Complex MySQL query through relational tables

Question

I am generating a query that returns a set of profiles that meet all the conditions posted below

I am receiving the data like so in Ruby, then making a dynamic MySQL query based on the contents -

[{ attribute_id: 58, parent_profile_name: 'Douglas-Connelly' },
{ attribute_id: 26, parent_profile_name: 'Brekke LLC' },
{ attribute_id: 35, val: 'Asa' },
{ attribute_id: 38, val: 'Stanton' }]

These are the current contents of the database

profile_attribute_values 
profile_id attribute_id parent_profile_id val
6 58 2
6 26 5
6 35 nil 'Asa'
6 38 nil 'Stanton'
profile
id name
2 Douglas-Connelly 
5 Brekke LLC
6 nil

I need to return all profiles that meet all the conditions - profiles that have a relation to profile_attribute_values, where the attribute_id is x, and the val is y, AND where the attribute_id is x, and the parent_profile name = y

WHAT I CURRENTLY HAVE

SELECT * FROM
(SELECT P2. * 
FROM profile_attribute_values PAV
INNER JOIN profiles P1 ON P1.id = PAV.parent_profile_id
INNER JOIN profiles P2 ON P2.id = PAV.profile_id
WHERE (PAV.ne_attribute_id = '58' AND P1.`name` = 'Douglas-Connelly')
) A,
(SELECT P1. * 
FROM profiles P1
INNER JOIN profile_attribute_values PAV ON P1.id = PAV.profile_id
INNER JOIN profile_attribute_values PAV2 ON P1.id = PAV2.profile_id
WHERE (PAV.ne_attribute_id = '35' AND PAV.val = 'ASA')
AND (PAV2.ne_attribute_id = '38' AND PAV2.val = 'Stanton')
) B
WHERE A.id = B.id

This will return

profile
id name
6 nil

which is exactly what I want, though the tricky part is the second parent_profile condition where I need attribute_id 26, and parent_profile_name: 'Brekke LLC'

I know this wont work, but I need this to do something like this

SELECT * FROM
(SELECT P2. * 
FROM profile_attribute_values PAV
INNER JOIN profiles P1 ON P1.id = PAV.parent_profile_id
INNER JOIN profiles P2 ON P2.id = PAV.profile_id
WHERE (PAV.ne_attribute_id = '58' AND P1.`name` = 'Douglas-Connelly')
AND (PAV.ne_attribute_id = '26' AND P1.`name` = 'Brekke LLC')
) A,
.....

I am generating the SQL statement dynamically, so I really need it to be as clean as possible. I generally use ruby active record for everything, so I am a little babied when it comes to SQL statements. Thanks!

• Rick James: Condolences. EAV is messy and inefficient.
• Rick James: I hope you have PRIMARY KEY(profile_id, ne_attribute_id).

Answer 1

Looks like straightforward addition of INNER JOIN for every condition will do

SELECT id 
FROM profile p 
INNER JOIN profile_attribute_values pav1 ON p.id = pav1.profile_id
 AND pav1.attribute_id = '58' 
INNER JOIN profiles p1 ON p1.id = pav1.parent_profile_id 
 AND p1.`name` = 'Douglas-Connelly'
INNER JOIN profile_attribute_values pav2 ON p.id = pav2.profile_id
 AND pav2.attribute_id = '26' 
INNER JOIN profiles p2 ON p2.id = pav2.parent_profile_id 
 AND p2.`name` = 'Brekke LLC'
INNER JOIN profile_attribute_values pav3 ON p.id = pav3.profile_id
 AND pav3.ne_attribute_id = '35' AND pav3.val = 'ASA'
INNER JOIN profile_attribute_values pav4 ON p.id = pav4.profile_id
 AND pav4.ne_attribute_id = '38' AND pav4.val = 'Stanton'

• Matt Ramirez: Perfect, exactly what I was looking for. Works like a charm.

Answer 2

@Serg's answer is effective and fast for a small set of attributes to compare, but it is limited to 30, i.e. hitting the maximum 61 tables that can be referenced in a query (MySQL 5.x).

The following is another equivalent but arguably more costly plan, however this one is not limited in the number of attribute tests (you would build dynamically the must table of UNION ALL rows, and leave the rest of the query as is):

SELECT *
FROM `profiles`
WHERE id IN (
 SELECT p.id
 FROM (
 SELECT 58 AS attribute_id, 'Douglas-Connelly' AS parent_profile_name, NULL AS val
 UNION ALL SELECT 26, 'Brekke LLC', NULL
 UNION ALL SELECT 35, NULL, 'Asa'
 UNION ALL SELECT 38, NULL, 'Stanton'
 ) must
 CROSS JOIN `profiles` p
 LEFT JOIN `profiles` pp ON (pp.name = must.parent_profile_name)
 LEFT JOIN profile_attribute_values a ON 
 (a.profile_id = p.id AND
 a.attribute_id = must.attribute_id AND
 (a.parent_profile_id = pp.id OR must.val IS NOT NULL AND a.val = must.val))
 GROUP BY p.id
 HAVING SUM(CASE WHEN a.attribute_id IS NULL THEN 1 ELSE 0 END) = 0
 )

• Serg: ... (SELECT pid FROM (SELECT 1 AS condNbr, pav.profile_id AS pid FROM profile_attribute_values AS pav WHERE <condition 1> UNION ALL SELECT 2 AS condNbr, pav.profile_id AS pid FROM profile_attribute_values AS pav WHERE <condition 2> ...) GROUP BY pid HAVING COUNT(condNbr) = <number of conditions>) AS filteredPids ... May it peform better?
• Ezequiel Tolnay: @Serg not really, because that causes a lookup for a row that could simply be a constant, and for each row it is using up some of the 61 tables MySQL can handle in a single query.
• Matt Ramirez: Also a great solution, though I can definitely get away with the 30 limit, thanks!