-2

I have three tables: 

Table USER 
id
name
email
Table CAMPUS
id
user_id (foreign key)
name 
Table POST
id
title
user_id (foreign key)

A User belongs to a Campus, and a Post belong to a User. I want to write a query to fetch posts inner join with user inner join with campus.

The result i get: 

[{ 
id
username
campus name
title
... 
}]

Everything is in a single object. Instead, I want nested objects like this:

[{
post_title:
post_id:
...
User: {id name... }
campus:{id name ...}

}]

This way, User and Campus are inherited in the post according to foreign keys. How can i achieve it with raw sql? Should I have to parse it later?

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Hassan Raza
  • 82
  • 6
  • I think you want a json object. like here https://stackoverflow.com/questions/41758870/how-to-convert-result-table-to-json-array-in-mysql – nbk Apr 30 '20 at 21:37

1 Answers1

1

You can use join and use json functions to generate the result that you expect.

Your question is not very accurate about the table structures, but this should look like:

select 
    json_object(
        'id',     p.id
        'title',  p.title
        'user',   json_object('id', u.id, 'name', u.name),
        'campus', json_object('id', c.id, 'name', c.name)
    ) post_object
from posts p
inner join users u on u.id = p.user_id
inner join campus c on c.user_id = u.id

This gives you a resultset with a single column and one record per post as a json object that contains nested objects that represent the user and the campus.

If you want a result with a single row and all rows stuffed in a json array, you can use json_arrayagg() on top of this:

select json_arrayagg(
    json_object(
        'id',     p.id
        'title',  p.title
        'user',   json_object('id', u.id, 'name', u.name),
        'campus', json_object('id', c.id, 'name', c.name)
    ) 
) all_posts_objects
from posts p
inner join users u on u.id = p.user_id
inner join campus c on c.user_id = u.id
GMB
  • 216,147
  • 25
  • 84
  • 135