1

This overly complex query seems to work when I use a hardcoded value for gid=node.nid (eg. gid=123), but when I use it like below, I get the error following:

Unknown column 'node.nid' in 'where clause'

Edit: Important! This query is used in an even bigger query which has the node.nid in the overall query. And I know that the value is available in the most upper queries.

I assume this is due to scoping, though I'm not sure how to pass this node.nid to the deeper queries. If there is any way to reduce this to a lot smaller simpler query, I'd be all ears.

    ( 
       # Sum up all the user session counts to create a total 
       SELECT Sum(user_session_count) 
        FROM   node AS n1, 
               ( 
               # Filter out only the highest value for session_count per user, since that is the amount of sessions for one user, course completed or not.
               SELECT Max(session_count) user_session_count, 
                      uid, 
                      nid 
                FROM   node AS n2, 
                       ( 
                       # Returns the count of results, grouped by lesson nid. This way, we know that like 1/3 lessons have been run eg. 3 times, but 2/3 only 2 times. Therefor we can say we detected 3 sessions, which not all completed, but have started
                       SELECT Count(qnr.result_id) session_count, 
                              qnr.uid, 
                              qnr.nid 
                        FROM   node AS n3, 
                               quiz_node_results qnr 
                        WHERE  nid IN ( 
                                      # Returns all the nid's from the course's quizes 
                                      SELECT n4.nid 
                                       FROM   `og_membership` AS ogm 
                                              JOIN `node` AS n4 
                                                ON ogm.etid = n4.nid 
                                       WHERE  gid = node.nid 
                                              AND field_name = 'og_group_ref' 
                                              AND entity_type = 'node' 
                                              AND n4.type = 'quiz') 
                               AND qnr.uid IN ( 
                                              # Return all uid's in the class 
                                              SELECT ogm.etid 
                                               FROM   `og_membership` AS ogm 
                                               WHERE  gid = 983 
                                                      AND entity_type = 'user' 
                                                      AND field_name = 
                                                          'og_user_node') 
                        GROUP  BY qnr.nid, 
                                  qnr.uid 
                        ORDER  BY qnr.uid, 
                                  session_count DESC) t 
                GROUP  BY t.uid) t2)

Edit 2: Updated above query with latest version, based on the comments. For completeness sake, including the surrounding SQL, the final query would look as follows. Please note that I do not have permissions to alter the super query, only the subquery courses_started.

SELECT node.title                                                   AS 
       node_title, 
       node.nid                                                     AS nid, 
       node.created                                                 AS 
       node_created, 
       ( 
       # Sum up all the user session counts to create a total 
       SELECT Sum(user_session_count) 
        FROM   node AS n1, 
               ( 
               # Filter out only the highest value for session_count per user, since that is the amount of sessions for one user, course completed or not.
               SELECT Max(session_count) user_session_count, 
                      uid, 
                      nid 
                FROM   node AS n2, 
                       ( 
                       # Returns the count of results, grouped by lesson nid. This way, we know that like 1/3 lessons have been run eg. 3 times, but 2/3 only 2 times. Therefor we can say we detected 3 sessions, which not all completed, but have started
                       SELECT Count(qnr.result_id) session_count, 
                              qnr.uid, 
                              qnr.nid 
                        FROM   node AS n3, 
                               quiz_node_results qnr 
                        WHERE  nid IN ( 
                                      # Returns all the nid's from the course's quizes 
                                      SELECT n4.nid 
                                       FROM   `og_membership` AS ogm 
                                              JOIN `node` AS n4 
                                                ON ogm.etid = n4.nid 
                                       WHERE  gid = node.nid 
                                              AND field_name = 'og_group_ref' 
                                              AND entity_type = 'node' 
                                              AND n4.type = 'quiz') 
                               AND qnr.uid IN ( 
                                              # Return all uid's in the class 
                                              SELECT ogm.etid 
                                               FROM   `og_membership` AS ogm 
                                               WHERE  gid = 983 
                                                      AND entity_type = 'user' 
                                                      AND field_name = 
                                                          'og_user_node') 
                        GROUP  BY qnr.nid, 
                                  qnr.uid 
                        ORDER  BY qnr.uid, 
                                  session_count DESC) t 
                GROUP  BY t.uid) t2)                                AS 
       courses_started, 
       (SELECT Count(opigno_statistics_user_course_pk) 
        FROM   `opigno_statistics_user_course` 
        WHERE  course_nid = node.nid 
               AND uid IN ( 
                          # Return all uid's in the class 
                          SELECT ogm.etid 
                           FROM   `og_membership` AS ogm 
                           WHERE  gid = 983 
                                  AND entity_type = 'user' 
                                  AND field_name = 'og_user_node')) AS 
       courses_completed 
FROM   node node 
       INNER JOIN field_data_opigno_class_courses AS 
                  field_data_opigno_class_courses 
               ON node.nid = 
                  field_data_opigno_class_courses.opigno_class_courses_target_id 
                  AND ( field_data_opigno_class_courses.entity_type = 'node' 
                        AND field_data_opigno_class_courses.deleted = '0' ) 
       INNER JOIN node opigno_class_courses_node 
               ON field_data_opigno_class_courses.entity_id = 
                  opigno_class_courses_node.nid 
WHERE  ( (( opigno_class_courses_node.nid = '983' )) 
         AND ((( node.status = '1' ))) ) 
ORDER  BY node_created DESC 
  • Start by giving an alias (say n1, n2, ...) to each instance of node in the question. – Lennart Apr 14 '17 at 14:05
  • 1
    To expand on Lennart's comment; here, the t2 subquery include the table node, and the t subquery inside it also refers to node, leaving the qnr.nid IN subquery unclear on which node table's value it should refer to. ALSO - the node, t query and the qnr, node query are both going to be CROSS JOIN queries, since there's nothing that tells the engine how to match the rows. This may be deliberate, but seems worth mentioning. – RDFozz Apr 14 '17 at 14:56
  • 1
    IN ( SELECT ... ) performs poorly; turn it into a JOIN. – Rick James Apr 14 '17 at 15:35
  • I've aliased every node instance to n1, n2, n3, n4. Still, I'm getting the Column not found: 1054 Unknown column 'node.nid' in 'where clause' error. – Ambidex Apr 18 '17 at 6:18
  • I've updated my original question, including the entire resulting (first excluded from question) query. – Ambidex Apr 18 '17 at 8:41

Your Answer

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

Browse other questions tagged or ask your own question.