Converting MySQL to db_select with subquery count

Question

I have an SQL query, with subquery, that returns as set of nodes ordered by referenced nodes... It works fine in a db_query, but I need it to be dynamic, and it feels like a better idea to use Drupal's abstraction layer.

I have got so far, but I am having issues with the subquery. It doesn't appear to having any effect...

I should say I used the Query Coder module to help do the conversion.

SQL:

SELECT n.nid , dest.field_flight_hero_destination_tid  hero_desintation_tid , (
SELECT COUNT( * )
FROM {field_data_field_field_route_matrix} routes
LEFT JOIN  {field_data_field_flight_destination} destination ON routes.field_field_route_matrix_target_id = destination.entity_id
WHERE routes.entity_id = n.nid
AND  field_flight_destination_tid
IN (:stopovers_tids)
)total
FROM node n
LEFT JOIN  field_data_field_flight_hero_destination dest ON n.nid = dest.entity_id
LEFT JOIN field_data_field_flight_price_matrix prices ON n.nid = prices.entity_id
LEFT JOIN field_data_field_flight_cabin_class class ON n.nid = class.entity_id
WHERE dest.field_flight_hero_destination_tid = :destination_tid AND class.field_flight_cabin_class_tid IN (:class_tid)
ORDER BY total DESC 

db_select:

// Subquery.
  $subquery  = db_select('{field_data_field_field_route_matrix}', 'routes');
  $subquery->addExpression('COUNT(*)', 'count');
  $subquery->leftJoin('{field_data_field_flight_destination}', 'destination', 'routes.field_field_route_matrix_target_id = destination.entity_id');
  $subquery->condition('routes.entity_id', 'n.nid');
  $subquery->condition('field_flight_destination_tid', $stopovers);

  // Main query sorted by subquery.
  $query = db_select('node', 'n');
  $query->fields('n', array('nid'));
  $query->fields('dest', array('field_flight_hero_destination_tid'));
  $query->addJoin('', $subquery, 'total');
  $query->leftJoin('field_data_field_flight_hero_destination', 'dest', 'n.nid = dest.entity_id');
  $query->leftJoin('field_data_field_flight_price_matrix', 'prices', 'n.nid = prices.entity_id');
  $query->leftJoin('field_data_field_flight_cabin_class', 'class', 'n.nid = class.entity_id');
  $query->condition('dest.field_flight_hero_destination_tid', $destination);
  if (!empty($cabin_class)) {
    $query->condition('class.field_flight_cabin_class_tid', array($cabin_class), 'IN');
  }
$query->orderBy('total.count', 'DESC');
$result = $query->execute();

Generated output:

[queryString] => SELECT n.nid AS nid, dest.field_flight_hero_destination_tid AS field_flight_hero_destination_tid
FROM 
node n
 JOIN (SELECT COUNT(*) AS count
FROM 
field_data_field_field_route_matrix routes
LEFT OUTER JOIN field_data_field_flight_destination destination ON routes.field_field_route_matrix_target_id = destination.entity_id
WHERE  (routes.entity_id = :db_condition_placeholder_1) AND (field_flight_destination_tid IN  (:db_condition_placeholder_2)) ) total
LEFT OUTER JOIN field_data_field_flight_hero_destination dest ON n.nid = dest.entity_id
LEFT OUTER JOIN field_data_field_flight_price_matrix prices ON n.nid = prices.entity_id
WHERE  (dest.field_flight_hero_destination_tid = :db_condition_placeholder_0) 
ORDER BY total.count DESC
)

Devel dpq:

SELECT n.nid AS nid, dest.field_flight_hero_destination_tid AS field_flight_hero_destination_tid
FROM 
{node} n
 JOIN (SELECT COUNT(*) AS count
FROM 
{field_data_field_field_route_matrix} routes
LEFT OUTER JOIN {field_data_field_flight_destination} destination ON routes.field_field_route_matrix_target_id = destination.entity_id
WHERE  (routes.entity_id = 'n.nid') AND (field_flight_destination_tid IN  ('31', '366')) ) total
LEFT OUTER JOIN {field_data_field_flight_hero_destination} dest ON n.nid = dest.entity_id
LEFT OUTER JOIN {field_data_field_flight_price_matrix} prices ON n.nid = prices.entity_id
WHERE  (dest.field_flight_hero_destination_tid = '86') 
ORDER BY total.count DESC

Answer 1

Your adding the subquery via a join. you want the subquery to be run as piece of the outermost Select statement, which Drupal 7 SelectQuery cannot do see:

https://stackoverflow.com/questions/20214014/how-do-i-place-a-sub-query-in-a-select-statement-in-drupal7

The easiest way to do this to use db_query (a string) or maybe hook_query_alter after adding a tag to the db_select query and adding in the subquery via a string later. If you need additional info passed in and out the hook_query_alter then use the metadata options.