I’m trying to get content from database divided by category. I want strictly max 4 entries of type “people” and 4 other entries of type “organization”.

I tried to do it like that:

$query = db_select('node', 'n') ->fields('n', array('title','type')) ->fields('i', array('field_image_fid')) ->fields('f', array('uri')) ->condition('n.title', '%'. db_like($keys) . '%', 'LIKE') ->condition('type', array('people')) ->range(0,4); $query->leftJoin('field_data_field_image', 'i', 'i.entity_id = n.nid'); $query->leftJoin('file_managed', 'f', 'f.fid = i.field_image_fid');  $query2 = db_select('node', 'n') ->fields('n', array('title','type')) ->fields('i', array('field_image_fid')) ->fields('f', array('uri')) ->condition('n.title', '%'. db_like($keys) . '%', 'LIKE') ->condition('type', array('organization')) ->range(0,4); $query2->leftJoin('field_data_field_image', 'i', 'i.entity_id = n.nid'); $query2->leftJoin('file_managed', 'f', 'f.fid = i.field_image_fid');  $query->union($query2, 'UNION'); $result = $query ->execute(); 

The problem is that this query is returning only the first 4 occurrences of people or organization combined. So if there are 4 people returned by the query, I will not be able to see any organization.

I also tried something like this:

    $query = db_query('          SELECT p.title,p.type              FROM node as p             WHERE p.type = :type              LIMIT 4'         , array(':type' => 'people',':type1' => 'organization'))->fetchAll();  $query2 = db_query('          SELECT o.title,o.type              FROM node as o             WHERE o.type = :type1              LIMIT 4'         , array(':type' => 'people',':type1' => 'organization'))->fetchAll();   $query->union($query2, 'UNION'); 

or like this:

$result = db_query('      SELECT title,type          FROM {node}          WHERE type = :type          LIMIT 4      UNION ALL     SELECT title,type          FROM {node}          WHERE type = :type1          LIMIT 4'     , array(':type' => 'people',':type1' => 'organization'))->fetchAll(); 

But these two approaches are only returning the 4 people and no organizations, I mean never..

Thank you if you can help!

——————–EDIT——————–

Thank you for your comment Clive. I hadn’t tried yet to do it directly in plain SQL.

The query is something like that

(SELECT title,type FROM node WHERE type = 'organization'  LIMIT 4)  UNION (SELECT  title,type FROM node WHERE type = 'people'  LIMIT 4)  

I didn’t know but the parentheses are necessary to act as I need. Like that I get 4 of each, then 8 maximum in total.

So the same in drupal using db_query is something like:

$result = db_query('  (SELECT title,type      FROM {node}      WHERE type = :type      LIMIT 4 ) UNION ALL (SELECT title,type      FROM {node}      WHERE type = :type1      LIMIT 4)' , array(':type' => 'people',':type1' => 'organization'))->fetchAll(); 

I don’t know if it’s possible to do the same with db_select

————–EDIT2—————————

Regarding to Molot comments, I changed my query to:

    $subquery_1 = db_select('node', 'n')     ->fields('n', array('title','type'))     ->fields('i', array('field_image_fid'))     ->fields('f', array('uri'))     ->condition('n.title', '%'. db_like($keys) . '%', 'LIKE')     ->condition('type', array('people'))     ->range(0,3);     $subquery_1->leftJoin('field_data_field_image', 'i', 'i.entity_id = n.nid');     $subquery_1->leftJoin('file_managed', 'f', 'f.fid = i.field_image_fid');      $subquery_2 = db_select('node', 'n')     ->fields('n', array('title','type'))     ->fields('i', array('field_image_fid'))     ->fields('f', array('uri'))     ->condition('n.title', '%'. db_like($keys) . '%', 'LIKE')     ->condition('type', array('organization'))     ->range(0,5);     $subquery_2->leftJoin('field_data_field_image', 'i', 'i.entity_id = n.nid');     $subquery_2->leftJoin('file_managed', 'f', 'f.fid = i.field_image_fid');      $query = $subquery_1->union($subquery_2);     $f->fb((string)$query);     $result = $query   ->execute(); 

Now the query return People and Organizations together and limited to 5. Here is the print of the query:

SELECT n.title AS title, n.type AS type, i.field_image_fid AS field_image_fid, f.uri AS uri FROM {node} n LEFT OUTER JOIN {field_data_field_image} i ON i.entity_id = n.nid LEFT OUTER JOIN {file_managed} f ON f.fid = i.field_image_fid WHERE (n.title LIKE :db_condition_placeholder_0 ESCAPE '\') AND (type IN (:db_condition_placeholder_1)) LIMIT 3 OFFSET 0 UNION SELECT n.title AS title, n.type AS type, i.field_image_fid AS field_image_fid, f.uri AS uri FROM {node} n LEFT OUTER JOIN {field_data_field_image} i ON i.entity_id = n.nid LEFT OUTER JOIN {file_managed} f ON f.fid = i.field_image_fid WHERE (n.title LIKE :db_condition_placeholder_2 ESCAPE '\') AND (type IN (:db_condition_placeholder_3)) LIMIT 5 OFFSET 0