1
\$\begingroup\$

I am trying to evaluate if someone has read and seen all the questions of a book.

I am using PHP to average everything out after I get the data. I know MySQL can avg, but I can't seem to wrap my head around how I can average the box number of the questions (similar to spaced repetition learning) if they haven't seen every question.

How can this function be better? I feel there might be a more elegant way of doing it.

SELECT COUNT(s.id),
       (SELECT COUNT(u.id) 
          FROM user_sections u 
         WHERE u.chpt_id = ? && u.user_id = ?
       ), 
       (SELECT COUNT(q.id) 
          FROM question q 
         WHERE q.chapter_id = ? && (q.module = 1 || q.module = ?)
       ),
       (SELECT COUNT(v.id) 
          FROM user_questions v 
         WHERE v.chpt_id = ? && u.user_id = ?
       ), 
       (SELECT AVG(v.box) 
          FROM user_questions v 
         WHERE v.chapter_id = ? && u.user_id=?
       ),
       (SELECT AVG(m.$mkts_col) 
          FROM mkts m 
         WHERE m.chapter_id = ? && (m.module = 1 || m.module = ?)
       )
  FROM sections s 
 WHERE s.chapter_id = ? && (s.module = 1 || s.module = ?)
\$\endgroup\$
0

1 Answer 1

1
\$\begingroup\$

What you have written is essentially six independent queries. Let's drop the pretense that these are correlated subqueries. Let's also reorder the output columns. We can also simplify col = A || col = B as col in (A, B).

SELECT (SELECT COUNT(s.id)
          FROM sections s
         WHERE s.chapter_id = ? AND s.module IN (1, ?)
       ),
       (SELECT COUNT(q.id)
          FROM question q
         WHERE q.chapter_id = ? AND q.module IN (1, ?)
       ),
       (SELECT AVG(m.$mkts_col)
          FROM mkts m
         WHERE m.chapter_id = ? AND m.module IN (1, ?)
       ),
       (SELECT COUNT(u.id)
          FROM user_sections u
         WHERE u.chpt_id = ? AND u.user_id = ?
       ),
       (SELECT COUNT(v.id)
          FROM user_questions v
         WHERE v.chpt_id = ? AND u.user_id = ?
       ),
       (SELECT AVG(v.box)
          FROM user_questions v
         WHERE v.chapter_id = ? AND u.user_id = ?
       );

That's about all you can do to rewrite this particular query. Depending on how your application works, it might make sense to obtain all of these statistics globally, instead of for particular user + chapter + module combinations. In that case, you would write two queries, each with JOIN and GROUP BY clauses. One query be joined by chapter and module conditions, and the other would be joined by chapter and user.

After rearranging the columns, it becomes more obvious that you used both user_questions.chpt_id and user_questions.chapter_id. One of them is probably an error.

\$\endgroup\$
1
  • \$\begingroup\$ Never use "AVG()" function, its perfome as crap, :) sorry to say it. \$\endgroup\$ Commented Sep 19, 2013 at 8:41

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.