John Davidson

Message:

Quit struggling a while on this one.

In php I start with some users:

$uids = [1,2,3,4,5];

Then I create different possible week plannings:

$plannings = array

(

 1 => 

 array(

   'friday' => 'cook'

 ),

 2 => 

 array(

   'friday' => 'cook',

   'sunday' => 'play outside'

 ),

 3 => 

 array(

   'friday' => 'training',

   'sunday' => 'training'

 ),

 4 => 

 array(

   'friday' => 'meeting',

   'sunday' => 'brunch'

 ),

 5 => 

 array(

   'sunday' => 'rest'

 )

);

insert them in mysql:

foreach($plannings as $id => $details)

{

   INSERT INTO planning (id, data) VALUES ($id, json_encode($details));

}

after that, I assign randomly each user with different planning for the week

$week = [];

foreach($uids as $uid)

{

  $week [$uid] = rand(1,5) // which refers to the id of one of the plannings in the plannings array;

}

then I put that into another table saved as JSON

INSERT INTO week (data) VALUES (json_encode($week));

Now it comes, if I want to get the week planning into one array from the DB I can only come up with this dirty solution. for each user included in the week planning I make a new query to retrieve that specific planning

$week = SELECT data FROM week WHERE id = 1



$week = json_decode($week->data);

foreach($week as $uid => $planning_id)

{

 $planning = SELECT data FROM planning WHERE id = $planning_id

 $week[$uid] = json_decode($planning->data)

}

Now I am kind of silently hoping there could be a way doing this in one simple query using JOINS, JSON_EXTRACT, and other fancy methods in Mysql which I don't know of

---

My Answer:
Yes, you can achieve this using MySQL JOIN and JSON functions. Here's an example query that retrieves the week planning for all users in one go:

sql
SELECT u.id, w.data AS week_planning, p.data AS user_planning
FROM users u
JOIN week w ON u.id = JSON_KEYS(w.data)
JOIN planning p ON w.data->>'$.u.id' = p.id
WHERE u.id IN (1,2,3,4,5);


In this query:
- `users` is the table containing user information
- `week` is the table containing the week planning data
- `planning` is the table containing the individual planning data
- `JSON_KEYS(w.data)` extracts the keys (user ids) from the week planning JSON object
- `w.data->>'$.u.id'` extracts the planning id for each user from the week planning JSON object
- `WHERE u.id IN (1,2,3,4,5)` filters the results for the specified user ids

This query will return a result set with columns for user id, week planning, and user planning. You can then process this result set in your PHP code as needed.