Suppose we have these 2 following tables:
reservations table: id, time, user_id
users table: id, name
When selecting reservations, how can I get the following output on pure MySQL without querying for the "user" manually on php for each reservation?
Because it gets really slow when looping through thousands of reservations.
{
id: 1,
time: "123456789",
user_id: 321,
user: {
id: 321,
name: John Doe
}
}
Using JOIN:
-- could be wrapped with JSON_ARRAYAGG if needed
SELECT JSON_OBJECT('id', r.id,
'time', r.time,
'user_id', r.user_id,
'user', JSON_OBJECT('id', u.id, 'name', u.name)
) AS result
FROM reservations r
JOIN users u
ON r.user_id = u.id
SELECT reservations.*, users.* ...
SELECT * I am not aware of such functionality for MySQL"DESCRIBE $tableName" and returns a string to fill JSON_OBJECT() parameters. Helps me avoid typing 50 column names in there :) Thank you!I don't see any advantage to a nested structure. Simply return the columns you want:
select r.*, u.name as user_name
from reservation r join
user u
on r.user_id = u.id
id column is the same due to the JOIN condition. It is redundant to return it twice.You can get the data you want with a join.
SELECT reservations.id, reservations.time, reservations.user_id, users.name FROM reservations LEFT JOIN users ON users.id = reservations.user_id
