Best practice to select data from many postgres tables

Question

I have movie database with following tables:

Movies:

 mid |  title     
-----+---------
  9  | Jason X

Actors:

mid |   name 
----+------------
 9  | Kane Hodder
 9  | Lexa Doig

My question is what is best practice to get all data related to particular movie from all tables, On my opinion getting the data on one sql query is ideal, so I don't have to do multiple queries to db for each table.

When I tried to get everything in one query, I get duplicate data, for example I get title and mid on every row, while it's needed only one time:

 mid | title   | name 
---------------+------------
  9  | Jason X | Kane Hodder
  9  | Jason X | Lexa Doig

The webapp will convert Postgres output to a hash like:

movie => {mid => 9, title => 'Jason X', actors => ['Kane Hodger', 'Lexa doig']}

With current postgres output I get duplicate data for instance $movie{title} equals to ['Jason X', 'Jason X'], where needed only 'Jason X'

That is possible but you shouldn't want that. You should just handle it in your business logic. — Jakub Kania
– Jakub Kania, Commented Aug 14, 2017 at 0:13
@JakubKania So you would suggest getting all data and in one query (with duplicate data) and parse it in business logic? or get the data via multiple queries a query for each table and combine them in logic? I'm concerned more about the way to do that, looking for a good practice. — hythm
– hythm, Commented Aug 14, 2017 at 0:55

klin · Accepted Answer · 2017-08-14 08:19:33Z

0

You can use the JSONB type:

select to_jsonb(m) || jsonb_build_object('actors', jsonb_agg(a.name)) as movies
from movies m
join actors a using(mid)
group by mid;

                                 movies                                 
------------------------------------------------------------------------
 {"mid": 9, "title": "Jason X", "actors": ["Kane Hodder", "Lexa Doig"]}
(1 row)

See also:

A question of best practices is a bit risky on SO because it can be considered too broad or opinion based. It largely depends on the size of the data. In most simple cases where the number of returned rows is limited, the use of json is very convenient. You can also use another aggregate functions, e.g. string_agg ():

select m.*, string_agg(a.name, ',') as actors
from movies m
join actors a using(mid)
group by mid;

 mid |  title  |        actors         
-----+---------+-----------------------
   9 | Jason X | Kane Hodder,Lexa Doig
(1 row)

If the number of rows processed at one time by the application is larger (say, several hundred thousand), then more efficient solution may be the use of several queries and processing of data on client side.

edited Aug 14, 2017 at 8:19

answered Aug 14, 2017 at 0:18

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hythm Over a year ago

klin: Thanks for your help, I will try using JSON and aggregate functions and will report back how that worked for me.

hythm Over a year ago

Thanks klin, I dont see any benefit of using jsonb type for now (may be will need in future)... array_agg function does exactly what i need and programming language im using handles postgres array type well so used array_agg (actors)

Collectives™ on Stack Overflow

Best practice to select data from many postgres tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related