netcoder netcoder - 7 months ago 15
SQL Question

Many-to-many relationship SELECT issue

I have a very annoying problem on the application I've been contracted to work in.

The database structure, which I can't modify for compatibility reasons is a mess. It's basically a many-to-many-to-many-to-many-to-bang-your-head-on-the-desk relationship. It goes like this:

contact

---------------------------
| contact_id | name | ... |
---------------------------
| 1 | foo | |
---------------------------


metadefinition

-----------------------------
| metadefinition_id | name |
-----------------------------
| 1 | title |
-----------------------------
| 2 | job |
-----------------------------


contact_metadata

----------------------------
| contact_id | metadata_id |
----------------------------
| 1 | 1 |
----------------------------
| 1 | 2 |
----------------------------


metadata

-------------------------------------------
| metadata_id | metadefinition_id | value |
-------------------------------------------
| 1 | 1 | mrs |
-------------------------------------------
| 2 | 2 | coder |
-------------------------------------------


So, for a single contact, I want to search on all this and obtain something like this:

-----------------------------------------------------
| contact_id | name | metadata.title | metadata.job |
-----------------------------------------------------
| 1 | foo | mrs | coder |
-----------------------------------------------------


So now what I've tried so far. I can fetch the list of
metadefinition
in advance, that's not really an issue. So I build a query like this:

SELECT contact.*,m1.value AS `metadata.title` FROM contact
LEFT JOIN contact_metadata ON contact.contact_id = contact_metadata.contact_id
LEFT JOIN metadata m1 ON contact_metadata.metadata_id = m1.metadata_id AND m1.metadefinition_id = 1
GROUP BY contact_id


This works for a single
metadefinition
, I get something like this:

--------------------------------------
| contact_id | name | metadata.title |
--------------------------------------
| 1 | foo | mrs |
--------------------------------------


If I try with two, however:

SELECT contact.*,m1.value AS `metadata.title`,m2.value AS `metadata.job` FROM contact
LEFT JOIN contact_metadata ON contact.contact_id = contact_metadata.contact_id
LEFT JOIN metadata m1 ON contact_metadata.metadata_id = m1.metadata_id AND m1.metadefinition_id = 1
LEFT JOIN metadata m2 ON contact_metadata.metadata_id = m2.metadata_id AND m2.metadefinition_id = 2
GROUP BY contact_id


I get:

-----------------------------------------------------
| contact_id | name | metadata.title | metadata.job |
-----------------------------------------------------
| 1 | foo | mrs | NULL |
-----------------------------------------------------


If I remove the GROUP BY clause, of course, I get:

-----------------------------------------------------
| contact_id | name | metadata.title | metadata.job |
-----------------------------------------------------
| 1 | foo | mrs | NULL |
-----------------------------------------------------
| 1 | foo | NULL | coder |
-----------------------------------------------------


I'm open to anything as long as the query times are relatively acceptable (considering the structure, if it takes 10 seconds for 100000 records it's better than nothing)

Will it be temporary tables, stored procedures, I don't care, as long as I don't have to change the actual database structure.

Trust me, when it'll be the time to remodel, it'll be my pleasure to take this whole thing down.

Is there a solution to this, is it possible?

Thanks in advance.

Answer

I'd use a pair of correlated sub-queries. Something like

SELECT c.*
  ,( Select value from metadata m1 
       INNER JOIN contact_metadata cm 
       on cm.metadata_id = m1.metadata_id 
     WHERE m1.metadefinition_id = 1 
       AND cm.contact_id = c.contact_id )AS `metadata.title`
  ,( Select value from metadata m1 
       INNER JOIN contact_metadata cm 
       ON cm.metadata_id = m1.metadata_id 
     WHERE m1.metadefinition_id = 2 
       AND cm.contact_id = c.contact_id )AS `metadata.job`
FROM contact c
WHERE c.contact_id = 1
Comments