CODEWITHSUNDEEP
mysqlsqlwordpresswoocommerceorders
John Smith
John Smith

Reputation: 483

Unknown column 'O.order_id' in 'on clause' SQL query with WooCommerce

My query below is returning the following error message "Unknown column 'O.order_id' in 'on clause'". I am guessing that it is an issue with precedence? But I don't have enough background to figure out where that might be.

I've just recently added the 'where' clause so that I can filter on product categories.

SELECT
    MAX(
        CASE WHEN OIM.meta_key = 'enrolment_id' THEN(
        SELECT
            firstname
        FROM
            wp_sp_enrolments E
        WHERE
            OIM.meta_value = E.id
    )
    END
) AS 'firstname',
MAX(
    CASE WHEN OIM.meta_key = 'enrolment_id' THEN(
    SELECT
        surname
    FROM
        wp_sp_enrolments E
    WHERE
        OIM.meta_value = E.id
)
END
) AS 'surname',
O.order_item_name AS 'school',
MAX(
    CASE WHEN OIM.meta_key = 'class' THEN OIM.meta_value
END
) AS 'class',
MAX(
    CASE WHEN OIM.meta_key = 'fee-type' THEN OIM.meta_value
END
) AS 'fees',
MAX(
    CASE WHEN OIM.meta_key = 'enrolment_Id' THEN(
    SELECT
        UM.meta_value
    FROM
        wp_usermeta UM,
        wp_sp_enrolments E
    WHERE
        UM.meta_key = '_user_phone' AND UM.user_id = E.memberid AND E.id = OIM.meta_value
    LIMIT 1
)
END
) AS 'phone', MAX(
    CASE WHEN OIM.meta_key = 'enrolment_id' THEN(
    SELECT
        U.user_email
    FROM
        wp_users U,
        wp_sp_enrolments E
    WHERE
        U.id = E.memberid AND E.id = OIM.meta_value
    LIMIT 1
)
END
) AS 'email', P.post_status AS 'status'
FROM
    wp_woocommerce_order_items O,
    wp_terms T,
    wp_term_taxonomy TT,
    wp_term_relationships TR,
    wp_woocommerce_order_itemmeta OIM
INNER JOIN wp_posts P ON
    P.ID = O.order_id
WHERE
    T.name = 'workshops' AND T.term_id = TT.term_id AND TT.taxonomy = 'product_cat' AND TT.term_id = TR.term_taxonomy_id AND TR.object_id = OIM.meta_value AND OIM.meta_key = '_product_id' AND OIM.order_item_id = O.order_item_id
GROUP BY
    O.order_item_id

Upvotes: 2

Views: 728

Answers (2)

Jakumi
Jakumi

Reputation: 8374

the problem is, as you suspected, the precedence of the comma operator and the join operator.

on https://dev.mysql.com/doc/refman/8.0/en/join.html it is stated:

However, the precedence of the comma operator is less than that of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur.

meaning, you shouldn't mix, preferably.

so instead of inner join, just 

, wp_posts P

and in the WHERE

and P.id=O.order_id and P.id is not null

alternatively you can bundle the comma-separated tables in parentheses:

(wp_woocommerce_order_items O,
wp_terms T,
wp_term_taxonomy TT,
wp_term_relationships TR,
wp_woocommerce_order_itemmeta OIM  )
INNER JOIN ...

just another example why I have such a strong distaste for the comma operator.

Upvotes: 2

LoicTheAztec
LoicTheAztec

Reputation: 253849

Not sure, but try the following doing INNER JOINs instead:

SELECT
    MAX(
        CASE WHEN OIM.meta_key = 'enrolment_id' THEN(
        SELECT
            firstname
        FROM
            wp_sp_enrolments E
        WHERE
            OIM.meta_value = E.id
    )
    END
) AS 'firstname',
MAX(
    CASE WHEN OIM.meta_key = 'enrolment_id' THEN(
    SELECT
        surname
    FROM
        wp_sp_enrolments E
    WHERE
        OIM.meta_value = E.id
)
END
) AS 'surname',
O.order_item_name AS 'school',
MAX(
    CASE WHEN OIM.meta_key = 'class' THEN OIM.meta_value
END
) AS 'class',
MAX(
    CASE WHEN OIM.meta_key = 'fee-type' THEN OIM.meta_value
END
) AS 'fees',
MAX(
    CASE WHEN OIM.meta_key = 'enrolment_Id' THEN(
    SELECT
        UM.meta_value
    FROM
        wp_usermeta UM,
        wp_sp_enrolments E
    WHERE
        UM.meta_key = '_user_phone' AND UM.user_id = E.memberid AND E.id = OIM.meta_value
    LIMIT 1
)
END
) AS 'phone', MAX(
    CASE WHEN OIM.meta_key = 'enrolment_id' THEN(
    SELECT
        U.user_email
    FROM
        wp_users U,
        wp_sp_enrolments E
    WHERE
        U.id = E.memberid AND E.id = OIM.meta_value
    LIMIT 1
)
END
) AS 'email', P.post_status AS 'status'
FROM wp_woocommerce_order_items O
INNER JOIN wp_woocommerce_order_itemmeta OIM ON
    O.order_item_id = OIM.order_item_id 
INNER JOIN wp_term_relationships TR ON
    O.order_id = OIM.meta_value
INNER JOIN wp_term_taxonomy TT ON
    TR.term_taxonomy_id = TT.term_taxonomy_id
INNER JOIN wp_terms T ON
    T.term_id = TT.term_id 
INNER JOIN wp_posts P ON
    P.ID = O.order_id
WHERE T.name = 'workshops' 
    AND TT.taxonomy = 'product_cat' 
    AND OIM.meta_key = '_product_id'
GROUP BY
    O.order_item_id

Upvotes: 2

Related Questions