Nested MySQL selects issue within WordPress usermeta table-CodePudding

Having an issue with a complex MySql select statement and hoping for some pointers!

So I am using a number of plugins on top of WordPress with some interesting ways of storing data. The bits I'm concerned with are as following: There are some 'parent' accounts which have a number of child accounts. The parent to child relationship is stored in the usermeta table (user_id=user_id of child account, meta_value = parent_id, meta_key='parent'). Each of these child accounts can also complete a number of tasks. This is also stored in the usermeta table (user_id=user_id of child account, meta_value = complete_status, meta_key='task_id_'.task_id).

I'm trying to create a view where I get a list of each of these parent accounts, along with a few bits of information, then a few derived values from their children, including the average number of completed tasks by the children of each parent.

This is my MySQL statement, the part that is having the issue is the nested select:

SELECT 
wp_parent_account_info_table.obj_id, 
wp_parent_account_info_table.obj_type, 
wp_parent_account_info_table.id, 
wp_other_custom_table_info.created_at, 
wp_other_custom_table_info.product_id, 
(SELECT AVG(cc.rcount) 
    FROM (SELECT DISTINCT COUNT(*) as rcount 
        FROM wp_usermeta 
        WHERE meta_key LIKE 'task_id_%' 
        AND meta_value = 'complete' 
        AND wp_usermeta.user_id IN (SELECT DISTINCT user_id 
            FROM wp_usermeta 
            WHERE meta_key = 'parent' 
            AND meta_value = wp_parent_account_info_table.id
            ) AS sc
        ) AS cc
    ) AS a 
FROM wp_parent_account_info_table 
JOIN wp_other_custom_table_info 
ON `wp_parent_account_info_table`.`obj_id`=`wp_other_custom_table_info`.`id` 
INNER JOIN wp_another_custom_table_info 
ON `wp_another_custom_table_info`.`subscription_id`=`wp_parent_account_info_table`.`obj_id` WHERE `wp_parent_account_info_table`.`status` = 'enabled' 
AND `wp_parent_account_info_table`.`sub_accounts_available` <> '0' 
AND `wp_other_custom_table_info`.`status` = 'active' 
AND (`wp_another_custom_table_info`.`expires_at` > CONCAT(CURDATE(), ' 23:59:59') 
OR `wp_another_custom_table_info`.`expires_at` = '0000-00-00 00:00:00') 
LIMIT 0,30;

I've tried to make this readable, apologies for complexity. I didn't want to remove any parts incase they were relevant.

The statement works fine without the nested select. It also works (has no error) if I replace the most nested select with an array of IDs (so just putting: IN (1,2,3)). Is this something about me trying to get the parent ID from too far down?

This is the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS sc) AS cc) AS a FROM wp_parent_account_info_table JOIN wp_other_custom_table_info ON'

Any pointers would be much appreciated.

Edit:

Along with the answer below which resolved this error, I also didn't have access to the id variable in the furthest nest (a new error!), so I split them into an extra column. Here is my final code:

 SELECT 
    wp_parent_account_info_table.obj_id, 
    wp_parent_account_info_table.obj_type, 
    wp_parent_account_info_table.id, 
    wp_other_custom_table_info.created_at, 
    wp_other_custom_table_info.product_id, 
    (SELECT DISTINCT COUNT(*) 
    FROM wp_usermeta
    WHERE meta_key = 'parent' 
    AND meta_value = wp_parent_account_info_table.id) as c,
    (SELECT DISTINCT COUNT(*) as rcount 
    FROM wp_usermeta 
    WHERE meta_key LIKE 'task_id_%' 
    AND meta_value = 'complete' 
    AND wp_usermeta.user_id IN (SELECT DISTINCT user_id 
        FROM wp_usermeta 
        WHERE meta_key = 'parent' 
        AND meta_value = wp_parent_account_info_table.id
        ) 
    ) AS a,
    (SELECT a / c)
    FROM wp_parent_account_info_table 
    JOIN wp_other_custom_table_info 
    ON `wp_parent_account_info_table`.`obj_id`=`wp_other_custom_table_info`.`id` 
    INNER JOIN wp_another_custom_table_info 
    ON `wp_another_custom_table_info`.`subscription_id`=`wp_parent_account_info_table`.`obj_id` WHERE `wp_parent_account_info_table`.`status` = 'enabled' 
    AND `wp_parent_account_info_table`.`sub_accounts_available` <> '0' 
    AND `wp_other_custom_table_info`.`status` = 'active' 
    AND (`wp_another_custom_table_info`.`expires_at` > CONCAT(CURDATE(), ' 23:59:59') 
    OR `wp_another_custom_table_info`.`expires_at` = '0000-00-00 00:00:00') 
    LIMIT 0,30;

CodePudding user response：

The syntax error is related to your IN cluse ... the IN clause base on a subselect don't require a tablename alias then avoid the sc after the )

    SELECT 
    wp_parent_account_info_table.obj_id, 
    wp_parent_account_info_table.obj_type, 
    wp_parent_account_info_table.id, 
    wp_other_custom_table_info.created_at, 
    wp_other_custom_table_info.product_id, 
    (SELECT AVG(cc.rcount) 
        FROM (SELECT DISTINCT COUNT(*) as rcount 
            FROM wp_usermeta 
            WHERE meta_key LIKE 'task_id_%' 
            AND meta_value = 'complete' 
            AND wp_usermeta.user_id IN (SELECT DISTINCT user_id 
                FROM wp_usermeta 
                WHERE meta_key = 'parent' 
                AND meta_value = wp_parent_account_info_table.id
                ) 
            ) AS cc
        ) AS a 
    FROM wp_parent_account_info_table 
    JOIN wp_other_custom_table_info 
    ON `wp_parent_account_info_table`.`obj_id`=`wp_other_custom_table_info`.`id` 
    INNER JOIN wp_another_custom_table_info 
    ON `wp_another_custom_table_info`.`subscription_id`=`wp_parent_account_info_table`.`obj_id` WHERE `wp_parent_account_info_table`.`status` = 'enabled' 
    AND `wp_parent_account_info_table`.`sub_accounts_available` <> '0' 
    AND `wp_other_custom_table_info`.`status` = 'active' 
    AND (`wp_another_custom_table_info`.`expires_at` > CONCAT(CURDATE(), ' 23:59:59') 
    OR `wp_another_custom_table_info`.`expires_at` = '0000-00-00 00:00:00') 
    LIMIT 0,30;