0

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
  • db_select has the curly braces around it '{'... eg: '{field_data_field_field_route_matrix}'.. ?? – Pravin Ajaaz Nov 18 '14 at 14:51
  • Hmm... didn't notice that, but removing them has no effect. – mediaashley Nov 18 '14 at 14:54
  • Just to make sure, you removed them from the left join in your subquery as well? – Paul Nov 18 '14 at 15:10
  • did you install the Devel module and do dpq($query) to see what output drupal is generating for your db_select ? – tenken Nov 18 '14 at 15:56
  • Yes. It is as if the subquery wasn't there at all. – mediaashley Nov 18 '14 at 15:56
0

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.

  • Thanks @tenken. I added the subquery SQL with addExpression, and it appears to have worked wonderfully. – mediaashley Nov 19 '14 at 10:51

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.