current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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 ( 
        ((sample_link.owner_type = 'project' AND sample_link.owner_id = 1) AND
        (sample_link.owner_type = 'raw_material' AND sample_link.owner_id = 2) )
    )

(notice the change on the OR)

Any ideas on how I should go about this?

Cheers and thank you for your time!

PS: technically I am building the query dynamically from an ORM, but I should be able to do that just fine if I can get a working select statement to go from

share|improve this question
    
Schema as SQLFiddle, after fixing up missing owner_id with dummy info: sqlfiddle.com/#!15/5b309 –  Craig Ringer Jan 23 '14 at 2:08
    
This is a variant of the entity-attribute-value (EAV) pattern problem, btw. –  Craig Ringer Jan 23 '14 at 2:11

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
Ah yes, thank you, that's exactly what I needed. I am hoping that I can greatly increase the search for related sample data through a complex web of enteties by collating them into containers. I will be able to see how well it scales in the morning. Cheers! –  drkstr1 Jan 23 '14 at 5:37
    
@drkstr1 Cool. Otherwise, you might want to look into using hstore - or using a nonrelational DB - if you've got lots of tag-like, schemaless, somewhat freeform data. –  Craig Ringer Jan 23 '14 at 5:44
    
That worked beautifully. It went from taking about 15 -30 seconds to about 1-5. You sir, just helped me patch up the ass end of an 8 month project! Thanks a ton. –  drkstr1 Jan 24 '14 at 0:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.