CODEWITHSUNDEEP
javascriptsql
ampedo
ampedo

Reputation: 253

Query join two table but another table need to be in another inside array

Hello just a question from a newbie i just to query this two and i want the second table to be inside the array of the other table.

the table looks like this

post

|d : 1"|
|content: "test"|
|id : 2|
|content: "test2"|

post_images

|id : 1|
|post_id : 1|
|img_name : 1.jpg|
|id : 2|
|post_id : 1|
|img_name : 2.jpg|
|id : 3|
|post_id : 2|
|img_name : 3.jpg|
|id : 4|
|post_id : 2|
|img_name : 2.jpg|

My query looks like this

Select posts.*,posts_images.* 
from posts 
INNER JOIN posts_images on posts_images.post_id = posts.id

is it possible to get this data look like this

array
(
   id:1,
   content:test,
   imgs:array
   (
       1.jpg,
       2.jpg
   )
)

I hope you understand what I want to say. :) looking forward for your help thanks in advance

Upvotes: 0

Views: 42

Answers (1)

zebnat
zebnat

Reputation: 521

My domain is mysql but i have found this info.

Postgresql GROUP_CONCAT equivalent?

You could try:

SELECT posts.id, posts.content, array_agg(posts_images.img_name)
FROM posts_images 
INNER JOIN posts on posts_images.post_id = posts.id
GROUP BY posts_images.post_id

not sure if aliases work on array_agg but you could try

array_agg(img_name) AS imgs

Upvotes: 1

Related Questions