CODEWITHSUNDEEP
phpmysqlsqljoin
John Boga
John Boga

Reputation: 532

MySQL join table as nested array

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
       }
}

Upvotes: 0

Views: 365

Answers (3)

geoidesic
geoidesic

Reputation: 5053

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

Upvotes: -1

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

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

db<>fiddle demo

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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

Upvotes: 0

Related Questions