4

I have the following query (stripped down for brevity), the aim of which is to create the w8 value used to order the results:

SELECT elements.id, [ ... ],
(SELECT 
    COALESCE(craft_w8_a.weight, 0) + COALESCE(SUM(craft_w8_b.weight), 0) 
    FROM `craft_w8` `craft_w8_a`
    LEFT JOIN `craft_w8` `craft_w8_b` 
        ON craft_w8_b.elementId
        IN ( SELECT targetId FROM `craft_relations`
                WHERE fieldId IN (15, 16)
                  AND sourceId = elements.id)
    WHERE craft_w8_a.elementId = elements.id
) as w8
FROM `craft_elements` `elements`
[ ... ]
GROUP BY `elements`.`id`
ORDER BY `w8` DESC, `name` ASC LIMIT 100

The problem I'm having is that the second nested subquery (the one in the left join) is unable to find the column elements.id from the initial select.

From what I've found searching around SQL only passes values one level deep, and I've been unable to find a suitable work-around.

Is it possible to force SQL to pass a value deeper than one level? Or is there a way of modifying the query to not use another subquery, but still get the same result?

Sorry if I'm doing anything stupid or missed anything obvious, SQL is not my strong point!

  • Why the GROUP BY? Are there COUNT and/or SUM? – Rick James Feb 10 '17 at 5:34
  • 2
    You cannot "pass a value in". Can you turn both subqueries into JOINs? – Rick James Feb 10 '17 at 5:35
4

To my knowledge, this limitation pertains to SQL specifically in MySQL. All the other SQL products that I am aware of do not have it.

There does not appear to be a solution to this problem in general: you cannot force a column reference to be recognised deeper than one level in MySQL. However, there is an easy workaround in your specific case: replace the elements.id in the innermost query with craft_w8_a.elementId:

SELECT elements.id, [ ... ],
(SELECT 
    COALESCE(craft_w8_a.weight, 0) + COALESCE(SUM(craft_w8_b.weight), 0) 
    FROM `craft_w8` `craft_w8_a`
    LEFT JOIN `craft_w8` `craft_w8_b` 
        ON craft_w8_b.elementId
        IN ( SELECT targetId FROM `craft_relations`
                WHERE fieldId IN (15, 16)
                  AND sourceId = craft_w8_a.elementId)
    WHERE craft_w8_a.elementId = elements.id
) as w8
FROM `craft_elements` `elements`
[ ... ]
GROUP BY `elements`.`id`
ORDER BY `w8` DESC, `name` ASC LIMIT 100

This is an equivalent substitute because whatever craft_w8_a.elementId will be passed to the IN subquery is going to match elements.id based on the filter in the query that passes the value.

  • That works great, thanks! (Feels kind of obvious in hindsight) – Tam Feb 10 '17 at 9:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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