Drupal Technical

[Drupal] Using SQL Joins in Drupal

Abhai.sasidharan | | 2 min read

Often joins are the most required for odd jobs around the site for mining a specific data or field. The efficient way to do this would be to join multiple tables having a common field and arriving at the field. This is also the reason why joins are the most confusing to apply on more than 2 tables.

Consider we have three tables "webform_submissions", "field_data_field_child" and "taxonomy_term_hierarchy" where field_data_field_child is an extra field in an entity "group". The goal is to get the last submitted date of a particular user who belongs in a particular "parent" group whose child is in "field_data_field_child". The table "taxonomy_term_hierarchy" has the parent of the child in the table "field_data_field_child". The easy way to do this would be to load all the submissions of that user and check for the parent of the child in "taxonomy_term_hierarchy" and use that parent to check for that last submission in the loaded submissions. This can cause problems, once the number of submissions is a very large number.

Using joins we can mine the data from the tables and arrive at an exact value with very little overhead as shown below. What we do here is find common in "webform_submissions" and "field_data_field_child" (node_id or entity_id in this case) and which has common with "taxonomy_term_hierarchy" for current user. 


SELECT MAX(ws.submitted) AS submitted FROM webform_submissions ws
  INNER JOIN field_data_field_child fdr ON fdr.entity_id=ws.nid
  INNER JOIN taxonomy_term_hierarchy th ON th.tid=fdr.field_child_tid AND th.parent=$parent
  WHERE  (ws.uid = $user->uid)

The same can be achieved using the db_select function. 

 
  $query = db_select('webform_submissions', 'ws');
  $query->join('field_data_field_child', 'fdr', "fdr.entity_id=ws.nid");
  $query->join('taxonomy_term_hierarchy', 'th', "th.tid=fdr.field_child_tid AND th.parent=$parent")
  $query->fields('ws', array('submitted'))
    ->condition('ws.uid', $user->uid);
  $query->addExpression('MAX(submitted)');

The above codes can be extrapolated to accommodate 4 or 5 tables for join. You can view this page for a more visual idea of what join is.

Please let us know if we can provide with any further assistance.

 

 

Related Topics