postgres filtering select based on combination of joins

I am having trouble filtering a request that depends on a combination of child elements (one-to-many relationship)

Please consider the following example.

CREATE TABLE sample_container
(
  id serial NOT NULL,
  owner_id integer NOT NULL,
  owner_type character varying(255) NOT NULL,
  CONSTRAINT sample_container_pkey PRIMARY KEY (id)
)

CREATE TABLE sample_link
(
  id serial NOT NULL,
  owner_type character varying(255) NOT NULL,
  label character varying(255),
  owner_id integer NOT NULL,
  sample_container_id integer NOT NULL,
  CONSTRAINT sample_link_pkey PRIMARY KEY (id),
  CONSTRAINT sample_link_sample_container_id FOREIGN KEY (sample_container_id)
      REFERENCES sample_container (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

sample_container
id  owner_type
1   glue_batch
2   glue_batch
3   glue_batch

sample_link
id  owner_type      owner_id    sample_container_id
1   project         1           1
2   raw_material    1           1
3   project         1           2
4   raw_material    2           2
5   project         2           3
6   raw_material    2           3

What I need to do is select every sample_container which "owns" a sample_link where project and material match.

So for example, if I search for project=1 && raw_material=2, I would want it to return container 2, but not container 1 or 3.

So far I can all the containers with:

SELECT DISTINCT sample_container.* FROM sample_container
    INNER JOIN sample_link ON sample_container.id = sample_link.sample_container_id 
    WHERE sample_container.owner_type = 'glue_batch' AND ( 
        ((sample_link.owner_type = 'project' AND sample_link.owner_id = 1) OR
        (sample_link.owner_type = 'raw_material' AND sample_link.owner_id = 2) )
    )

Or no containers with:

```
SELECT DISTINCT sample_container.* FROM sample_container
INNER JOIN sample_link ON sample_container.id = sample_link.sample_container_id
WHERE sample_container.owner_type = 'glue_batch' AND

The schema is basically Entity-Attribute-Value (EAV), and the same query pattern applies: Use repeated joins with different filters to obtain the desired information.

SELECT *
FROM sample_container sc
INNER JOIN sample_link sl_proj
  ON (sc.id = sl_proj.sample_container_id
      AND sl_proj.owner_type = 'project'
      AND sl_proj.owner_id = 1)
INNER JOIN sample_link sl_rm
  ON (sc.id = sl_rm.sample_container_id
      AND sl_rm.owner_type = 'raw_material'
      AND sl_rm.owner_id = 2);

See: http://sqlfiddle.com/#!15/5b309/3

Like most EAV schemas, this will scale very poorly to large numbers of attributes, and requires dynamic SQL most of the time. Consider whether EAV is the right choice for you.

StackExchange Database Administrators Q#57337, answer score: 4