Getting related many-to-one objects with a single query

Question

I have three related tables in PostgreSQL:

• objects - top-level table, that has an ID and a name.
• object_events - a related table, that has a foreign key to objects
• object_aux_data - a table that uses many-to-one relationship with object_event

Some example data:

• objects:

  • id: 1, name: test_object

• object_events

  • id: 1, object_id: 1
  • id: 2, object_id: 1

• object_aux_data

  • object_event: 1, name: foo, value: foo_val
  • object_event: 1, name: bar, value: bar_val
  • object_event: 2, name: foo, value: foo_val2
  • object_event: 2, name: baz, value: baz_val

It is easy to get a list of object_events that are related to the test_object, but I'd like to also attach to the results some of the objec_aux_data. So the output results will look like this:

object.name	object_event.id	foo	bar
test_object	1	foo_val	bar_val
test_object	2	foo_val2

Note, that the foo has value for all object_event but bar only for the first one. baz is not taken into account in this case.

Is it possible to get this data with a single query, or should I have a separate query for each object_event?

Example schema:

create table objects(
  id int,
  name varchar
);

create table object_events(
  id int,
  object_id int
);

create table object_aux_data(
  object_event int,
  name varchar,
  value varchar
);

insert into objects values (1,'test_object');
insert into object_events values (1,1),(2,1);
insert into object_aux_data values (1,'foo','foo_val'),(1,'bar','bar_val'),(2,'foo','foo_val2'),(2,'baz','baz_val');

Answer 1

You can do this with a simple join (Result here)

with bar as (
  select oe.id, oad.value
  from object_events oe join object_aux_data oad on oad.object_event = oe.id 
  where oad.name = 'bar'
),
foo as (
  select oe.id,oad.value 
  from object_events oe join object_aux_data oad on oad.object_event = oe.id 
  where oad.name = 'foo')
select 
    o.name,oe.id,coalesce(foo.value,''),coalesce(bar.value,'')
from
    objects o join object_events oe on o.id = oe.object_id
    left join foo on foo.id = oe.id
    left join bar on bar.id = oe.id