List column of one table with all records of another

Question

I have 2 tables, posts and posts_images, the table post owns the fields id, title, Category, and images, and the table posts_images possesses: post_id, img_url etc. I would like to relate the field images table posts with all table records posts_images which have the same post_id country id table posts

In short, table POSTS

id,
title,
category,
images => todos os registros da tabela `posts_images` com o post_id = id

how can I do this?

Answer 1

Assuming your data structure is something like:

CREATE TABLE tb_post
(
   id BIGINT PRIMARY KEY,
   title TEXT NOT NULL,
   category TEXT NOT NULL
 );

CREATE TABLE tb_post_image
(
  id BIGINT PRIMARY KEY,
  id_post BIGINT NOT NULL,
  url TEXT NOT NULL
);

ALTER TABLE tb_post_image ADD FOREIGN KEY (id_post) REFERENCES tb_post(id);

Containing data similar to this:

-- POST #1
INSERT INTO tb_post ( id, title, category ) VALUES ( 1, 'Garota de Ipanema', 'Musica' );

-- POST #2
INSERT INTO tb_post ( id, title, category ) VALUES ( 2, 'Noite na Taverna', 'Literatura' );
INSERT INTO tb_post_image ( id, id_post, url ) VALUES ( 1, 2, 'noite1.jpg');
INSERT INTO tb_post_image ( id, id_post, url ) VALUES ( 2, 2, 'noite2.jpg');

-- POST #3
INSERT INTO tb_post ( id, title, category ) VALUES ( 3, 'Nao Sei Quantas Almas Tenho', 'Conto' );
INSERT INTO tb_post_image ( id, id_post, url ) VALUES ( 3, 3, 'alma1.jpg');
INSERT INTO tb_post_image ( id, id_post, url ) VALUES ( 4, 3, 'alma2.jpg');

Your query can be made so:

SELECT
  p.id,
  p.title,
  img.id,
  img.url
FROM
  tb_post AS p
LEFT JOIN
  tb_post_image AS img ON ( img.id_post = p.id )
ORDER BY
  p.id,
  img.id;

Exit:

| id |                       title |     id |        url |
|----|-----------------------------|--------|------------|
|  1 |           Garota de Ipanema | (null) |     (null) |
|  2 |            Noite na Taverna |      1 | noite1.jpg |
|  2 |            Noite na Taverna |      2 | noite2.jpg |
|  3 | Nao Sei Quantas Almas Tenho |      3 |  alma1.jpg |
|  3 | Nao Sei Quantas Almas Tenho |      4 |  alma2.jpg |
|  3 | Nao Sei Quantas Almas Tenho |      5 |  alma3.jpg |

SQL Fiddle: http://sqlfiddle.com/#! 17/0b7d9/1

EDIT

If the intention is to group the image records into one array, you can make a query as:

SELECT
     p.title,
     p.category,
     array_agg(img.url) AS array
FROM
     tb_post AS p
LEFT JOIN
    tb_post_image AS img ON ( img.id_post = p.id )
WHERE
     p.id = 3
GROUP BY
     p.title,
     p.category;

Exit:

|                       title | category |                         array |
|-----------------------------|----------|-------------------------------|
| Nao Sei Quantas Almas Tenho |    Conto | alma1.jpg,alma2.jpg,alma3.jpg |

SQL Fiddle: http://sqlfiddle.com/#! 17/0b7d9/30