How do I change a MySQL query into a Drupal query?

Question

mysql_query("SELECT nid,title, type,created, v_api.ContentSum
  FROM `node` n
  LEFT JOIN ( SELECT content_id,
                     SUM(CASE WHEN value=1 THEN 1
                         WHEN value=0 THEN -1
                         ELSE 0
                     END) AS ContentSum
                FROM votingapi_vote
               WHERE value_type = 'option'
               GROUP BY content_id) v_api
    ON n.nid = v_api.content_id
where type='provider'
ORDER BY v_api.ContentSum DESC, created DESC LIMIT 0 , 22");

How do I change this query into a Drupal query?

Answer 1

Check out the drupal Database abstraction layer. Without thinking about what you want to achieve with your query I would write it like that:

db_query_range("SELECT nid, title, type, created, v_api.ContentSum
                FROM {node} n
                LEFT JOIN (SELECT content_id,
                                  SUM(CASE
                                       WHEN value=1 THEN 1
                                       WHEN value=0 THEN -1
                                       ELSE 0
                                      END) AS ContentSum
                           FROM {votingapi_vote}
                           WHERE value_type = 'option'
                           GROUP BY content_id) v_api
                ON n.nid = v_api.content_id
                WHERE type='provider'
                ORDER BY v_api.ContentSum DESC, created DESC", 0, 22)

Maybe one of the values (type,value_type,...) could be set via arguments, too.

EDIT:

here again with arguments:

db_query_range("SELECT nid, title, type, created, v_api.ContentSum
                FROM {node} n
                LEFT JOIN (SELECT content_id,
                                  SUM(CASE
                                       WHEN value=1 THEN 1
                                       WHEN value=0 THEN -1
                                       ELSE 0
                                      END) AS ContentSum
                           FROM {votingapi_vote}
                           WHERE value_type = %s
                           GROUP BY content_id) v_api
                ON n.nid = v_api.content_id
                WHERE type= %s
                ORDER BY v_api.ContentSum DESC, created DESC", 'option', 'provider', 0, 22)