0

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!

2
  • Condolences. EAV is messy and inefficient.
    – Rick James
    May 4, 2016 at 18:57
  • I hope you have PRIMARY KEY(profile_id, ne_attribute_id).
    – Rick James
    May 4, 2016 at 18:58

2 Answers 2

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'
1
  • Perfect, exactly what I was looking for. Works like a charm. May 4, 2016 at 12:52
0

@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
  )
3
  • ... (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?
    – Serg
    May 4, 2016 at 8:18
  • @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. May 4, 2016 at 8:23
  • Also a great solution, though I can definitely get away with the 30 limit, thanks! May 4, 2016 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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