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