5

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
1
  • It'd be great if you could include a version of the plain SQL that produces accurate results, it'll make it easier to debug
    – Clive
    Jun 5, 2013 at 16:31

2 Answers 2

2

Create both subqueries separately, with their limits. Then, use:

$query = $subquery_1->union($subquery_2);

That should do it.

4
  • Hello, I just tried your solution but it doesn't work. Only results from $subquery_1 are returned.. For the moment it's only working with db_query, which is a hassle.
    – 118218
    Jun 6, 2013 at 13:29
  • 1
    @118218 Oh. Then I guess it's a job for support request or bugreport to core. Looks like something got skewed up in optimization of query builder. Could you show us a query rendered by it? print (string)$query; And make 1st query limit 3, 2nd query limit 5 so we could see what goes where, please. For debug's sake.
    – Mołot
    Jun 6, 2013 at 13:32
  • Where can I post a bugreport to core please?
    – 118218
    Jun 7, 2013 at 3:58
1

looks like there is small bug in Drupal. but there is workaround:

$subquery_1 = db_select('node', 'n')
->range(0,4);

$subquery_2 = db_select('node', 'n')
->range(0,8);

$query = $subquery_1->union($subquery_2);

->range(0,4); is for the first query and ->range(0, 8) is for both queries.

Problem is that $subquery_1->union doesn't add () around 2nd query.

issue with this approach is that if 1st query returns less than 4 rows then 2nd will return more to make 8

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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