CODEWITHSUNDEEP
databasedrupal-7limitunion
Kr1
Kr1

Reputation: 1303

Drupal db_select - How to combine UNION with LIMIT properly?

I'm trying to get content from database divided by category. I want strictly max 4 entries of type "people" and three 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 three occurrences of people or organization combined. So if there are three 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!

Upvotes: 0

Views: 2190

Answers (1)

AjayNimbolkar
AjayNimbolkar

Reputation: 36

Union statement in query its actually get record from first query and merge result record of first query to second query result record.

For example first case don't used limit in query of union its giving all record.

 $query1 = db_select('node', 'n')
   ->fields('n', array('nid','title','type'))
   ->condition('type', array('people'));

 $query2 = db_select('node', 'n')
  ->fields('n', array('nid','title','type'))
  ->condition('type', array('organization'));

 $query = Database::getConnection()
    ->select($query1->union($query2))
    ->fields(NULL, array('nid','title', 'type'))
    ->orderBy('nid');           
 $result = $query->execute()->fetchAll();

From above query it get records from first query append before records of second query

As per the you example If you wanted to get 4 records from people content type and 3 record from organizations content type
used following query to solve your issue

  $query1 = db_select('node', 'n')
   ->fields('n', array('nid','title','type'))
   ->condition('type', array('people'))
   ->range(0,4);
  $query2 = db_select('node', 'n')
  ->fields('n', array('nid','title','type'))
  ->condition('type', array('organization'));
  ->range(0,7);
 $query = Database::getConnection()
    ->select($query1->union($query2))
    ->fields(NULL, array('nid','title', 'type'))
    ->orderBy('nid');           
 $result = $query->execute()->fetchAll();

Upvotes: 1

Related Questions