UNION queries don't support PagerDefault

Created on 5 February 2013, over 11 years ago
Updated 28 February 2024, 4 months ago

The following fails:

  $query1 = db_select('a_blog', 'cb')
          ->fields('cb', array('title', 'changed_date'));
 
  $query2 = db_select('a_gallery', 'cg')
            ->fields('cg', array('title', 'changed_date'));
    
  $query1->union($query2, 'UNION ALL');
  $query1 = $query1->extend('PagerDefault');                  
  $results = $query1->execute();

The error is:

PDOException: SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns: SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {a_blog} cb UNION ALL SELECT cg.title AS title, cg.changed_date AS changed_date FROM {a_gallery} cg) subquery; Array ( ) in PagerDefault->execute() (line 74 of /Users/aangelantoni/Sites/base/includes/pager.inc).

OrderBy with UNIONS doesn't work (see http://drupal.org/node/1145076) and LIMITs with UNIONS don't work, either (as of 7.15).

šŸ› Bug report
Status

Active

Version

7.0 āš°ļø

Component
DatabaseĀ  ā†’

Last updated about 10 hours ago

  • Maintained by
  • šŸ‡³šŸ‡±Netherlands @daffie
Created by

šŸ‡ŗšŸ‡øUnited States aangel

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

  • šŸ‡·šŸ‡ŗRussia pvasili
    ā€¦
      $query1->union($query2, 'UNION ALL');
      $query1 = $query1->extend('PagerDefault');                  
      $results = $query1->execute();
    ā€¦
    

    we can try to do

    ā€¦
      $query1->union($query2, 'UNION ALL');
      $query_pagination = db_select($query1,'q')->fields('q');
      $query_pagination = $query_pagination->extend('PagerDefault');
      $results = $query_pagination->execute();      
    ā€¦
    
  • First commit to issue fork.
  • šŸ‡®šŸ‡³India himanshu_jhaloya Mandsaur

    Try this

    $query1 = db_select('a_blog', 'cb')
    ->fields('cb', array('title', 'changed_date'));

    $query2 = db_select('a_gallery', 'cg')
    ->fields('cg', array('title', 'changed_date'));

    // Ensure both queries have the same number of columns and name them consistently.
    $query1->addField('cb', 'title', 'title');
    $query1->addField('cb', 'changed_date', 'changed_date');

    $query2->addField('cg', 'title', 'title');
    $query2->addField('cg', 'changed_date', 'changed_date');

    // Combine the queries using UNION ALL.
    $query = $query1->union($query2, 'UNION ALL');

    // Extend the combined query with PagerDefault.
    $query = $query->extend('PagerDefault');

    // Execute the modified query.
    $results = $query->execute();

Production build 0.69.0 2024