CakePHP 3 paginating a result from union query

Question

I have two tables. The first one has the main products data, another one has the secondary descriptions of these products, linked by products_id foreign key on the second table.

I've created the SQL and everything looks good. But when I convert to ORM of CakePHP 3, with the same result SQL code, it shows a big problem: Every page shows the secondary descriptions from the second data table.

At the first page pagination index, who has the primary product into, works as expected. At first, shows the main products, and after that description shows the secondary descriptions that are inside the secondary table data. But when I click to see the next pages, these secondary products are there, when I expected anything from this table data.

I need to show these secondary descriptions only when the main products are on that page and when it isn't there, it doesn't shows any record from that table. Like in SQL script result.

On this SQL script you can see what I need:

    (SELECT 
        Products.id, 
        (null) AS product_titles_id,
        (Products.title) AS title_,
        Products.product_groups_id, 
        Products.product_types_id, 
        Products.code, 
        Products.title, 
        Products.ean, 
        Products.ncm, 
        Products.obs, 
        Products.minimum, 
        Products.maximum,
        ProductTypes.id,
        ProductTypes.code,
        ProductTypes.title,
        ProductTypes.calc_cost,
        ProductGroups.id,
        ProductGroups.code,
        ProductGroups.title
    FROM `products` Products
       LEFT JOIN `product_groups` ProductGroups ON Products.product_groups_id = ProductGroups.id
       LEFT JOIN `product_types` ProductTypes ON Products.product_types_id = ProductTypes.id)

    UNION

    (SELECT 
        Products.id,
        ProductTitles.id AS product_titles_id, 
        (ProductTitles.title) AS title_,
        Products.product_groups_id, 
        Products.product_types_id, 
        -- ProductTitles.products_id, 
        ProductTitles.code, 
        ProductTitles.title, 
        Products.ean, 
        Products.ncm, 
        ProductTitles.obs,
        Products.minimum, 
        Products.maximum,
        ProductTypes.id,
        ProductTypes.code,
        ProductTypes.title,
        ProductTypes.calc_cost,
        ProductGroups.id,
        ProductGroups.code,
        ProductGroups.title
    FROM `product_titles` ProductTitles
       LEFT JOIN `products` Products ON products.id = ProductTitles.products_id
       LEFT JOIN `product_groups` ProductGroups ON Products.product_groups_id = ProductGroups.id
       LEFT JOIN `product_types` ProductTypes ON Products.product_types_id = ProductTypes.id)
    ORDER BY title_

This a result from that SQL script:

    id  product_titles_id   title_
    0080    NULL              SUPORTE DE PRESSAO
    7545    NULL              BA RET.
    3177    NULL              CORT RDX
    3177    17                EMEN RDX (secondary description)
    3177    18                PART RDX (secondary description)
    6623    NULL              LATAO CALCO CALIB.
    8079    NULL              TRANSM DE PRESSAO
    4242    NULL              GY 6/6 P.U CORAL

This is what I did on CakePHP ORM script:

    //Main table of products
    $qry_products = $this->Products->findByParametersId('85')
            ->select(['Products.id', 'product_titles_id' => 'null', 
                      'title_' => 'Products.title', 
                      'Products.product_groups_id', 
                      'Products.product_types_id', 
                      'Products.code', 'Products.title', 
                      'Products.ean', 'Products.ncm', 
                      'Products.obs', 'Products.minimum', 
                      'Products.maximum', 'ProductTypes.id', 
                      'ProductTypes.code', 'ProductTypes.title', 
                      'ProductTypes.calc_cost', 'ProductGroups.id', 
                      'ProductGroups.code', 'ProductGroups.title', 
                      'ProductTitles' => '0'
                     ])
            ->where($where)
            ->join([
              'ProductGroups' => ['table' => 'product_groups',
                                  'type'       => 'LEFT',
                                  'conditions' => 'Products.product_groups_id = ProductGroups.id'
                                 ],
              'ProductTypes'  => ['table'      => 'product_types',
                                  'type'       => 'LEFT',
                                  'conditions' => 'Products.product_types_id = ProductTypes.id'
                                 ]
                   ])
             ->limit(20);

    //Secondary table of products' descriptions:
    $qry_productTitles = $this->ProductTitles->findByParametersId('85')
             ->select(['Products.id', 
                       'product_titles_id' =>                  'ProductTitles.id', 
                       'title_' => 'ProductTitles.title', 
                       'product_groups_id' => 'Products.product_groups_id', 
                       'product_types_id' => 'Products.product_types_id', 
                       'ProductTitles.code', 'ProductTitles.title',
                       'ean' => 'Products.ean', 
                       'ncm' => 'Products.ncm', 
                       'obs' => 'Products.obs', 
                       'minimum' => 'Products.minimum', 
                       'maximum' => 'Products.maximum',
                       'ProductTypes.id', 'ProductTypes.code', 
                       'ProductTypes.title',                            
                       'ProductTypes.calc_cost', 
                       'ProductGroups.id', 'ProductGroups.code', 
                       'ProductGroups.title', 
                       'ProductTitles' => '1'
                      ])
             ->join([
      'Products' => ['table'      => 'products',
                     'type'       => 'LEFT',
                     'conditions' => 'ProductTitles.products_id = Products.id'
                    ],
      'ProductGroups' => ['table'      => 'product_groups',
                          'type'       => 'LEFT',
                          'conditions' => 'Products.product_groups_id = ProductGroups.id'
                         ],
      'ProductTypes' => ['table'      => 'product_types',
                         'type'       => 'LEFT',
                         'conditions' => 'Products.product_types_id = ProductTypes.id'
                        ]
    ]);

    //Union these two tables and put some where conditions and ordanation:
    $products = $qry_products->union($qry_productTitles)
                             ->where($where)
                             ->epilog('ORDER BY title_');
                             //->order(['title_ ASC']);

This is a SQL result generated from CakePHP ORM:

    'sql' => '
    (SELECT Products.id AS `Products__id`, 
     null AS `product_titles_id`, Products.title AS `title_`, 
     Products.product_groups_id AS `Products__product_groups_id`, 
     Products.product_types_id AS `Products__product_types_id`, 
     Products.code AS `Products__code`, 
     Products.title AS `Products__title`, 
     Products.ean AS `Products__ean`, 
     Products.ncm AS `Products__ncm`, 
     Products.obs AS `Products__obs`, 
     Products.minimum AS `Products__minimum`, 
     Products.maximum AS `Products__maximum`, 
     ProductTypes.id AS `ProductTypes__id`, 
     ProductTypes.code AS `ProductTypes__code`, 
     ProductTypes.title AS `ProductTypes__title`,        
     ProductTypes.calc_cost AS `ProductTypes__calc_cost`, 
     ProductGroups.id AS `ProductGroups__id`, 
     ProductGroups.code AS `ProductGroups__code`, 
     ProductGroups.title AS `ProductGroups__title`, 
     0 AS `ProductTitles` 
    FROM products Products 
    LEFT JOIN product_groups ProductGroups ON Products.product_groups_id = ProductGroups.id 
    LEFT JOIN product_types ProductTypes ON Products.product_types_id = ProductTypes.id 
    ORDER BY title_ LIMIT 20)

    UNION 

    (SELECT Products.id AS `Products__id`, 
     ProductTitles.id AS `product_titles_id`, 
     ProductTitles.title AS `title_`,  
     Products.product_groups_id AS `product_groups_id`, 
     Products.product_types_id AS `product_types_id`, 
     ProductTitles.code AS `ProductTitles__code`, 
     ProductTitles.title AS `ProductTitles__title`, 
     Products.ean AS `ean`, Products.ncm AS `ncm`, 
     Products.obs AS `obs`, Products.minimum AS `minimum`, 
     Products.maximum AS `maximum`, 
     ProductTypes.id AS `ProductTypes__id`, 
     ProductTypes.code AS `ProductTypes__code`, 
     ProductTypes.title AS `ProductTypes__title`, 
     ProductTypes.calc_cost AS `ProductTypes__calc_cost`, 
     ProductGroups.id AS `ProductGroups__id`, 
     ProductGroups.code AS `ProductGroups__code`, 
     ProductGroups.title AS `ProductGroups__title`, 
     1 AS `ProductTitles` 
    FROM product_titles ProductTitles 
    LEFT JOIN products Products ON ProductTitles.products_id = Products.id 
    LEFT JOIN product_groups ProductGroups ON Products.product_groups_id = ProductGroups.id 
    LEFT JOIN product_types ProductTypes ON Products.product_types_id = ProductTypes.id) 

    ORDER BY title_',

The mais cause was identificated by paginate script. The code '$products = $this->paginate($products);' has these follow conditions: $page = (!empty($this->request->query['pagina']) ? $this->request->query['pagina'] : 1); $this->paginate = ['page' => $page, 'limit' => '10' ]; The problem is where it's put the line content 'LIMIT 10 OFFSET 10' on the first SELECT instead of on the end, after 'ORDER BY title_'. I don't know how to solve that problem yet. — Maksoud Rodrigues, Apr 18 '19 at 19:49

score 1 · Accepted Answer · answered Apr 23 '19 at 12:37

The problem was solved using the code below:

$products_union = "((SELECT 
                       Products.id AS `id`, 
                       Products.product_groups_id, 
                       Products.product_types_id, 
                       Products.code AS `code`, 
                       Products.title AS `title`, 
                       Products.ean AS `ean`, 
                       Products.ncm AS `ncm`, 
                       Products.obs AS `obs`, 
                       Products.minimum AS `minimum`, 
                       Products.maximum AS `maximum`,
                       ProductTypes.id AS `ProductTypes__id`,
                       ProductTypes.code AS `ProductTypes__code`,
                       ProductTypes.title AS `ProductTypes__title`,
                       ProductTypes.calc_cost AS `ProductTypes__calc_cost`,
                       ProductGroups.id AS `ProductGroups__id`,
                       ProductGroups.code AS `ProductGroups__code`,
                       ProductGroups.title AS `ProductGroups__title`
                     FROM `products` Products
                     LEFT JOIN `product_groups` ProductGroups 
                            ON Products.product_groups_id = ProductGroups.id
                     LEFT JOIN `product_types` ProductTypes 
                            ON Products.product_types_id = ProductTypes.id)

                     UNION ALL

                     (SELECT 
                        Products.id AS `id`, 
                        Products.product_groups_id, 
                        Products.product_types_id, 
                        ProductTitles.code AS `code`, 
                        ProductTitles.title AS `title`, 
                        Products.ean AS `ean`, 
                        Products.ncm AS `ncm`, 
                        ProductTitles.obs AS `obs`, 
                        Products.minimum AS `minimum`, 
                        Products.maximum AS `maximum`,
                        ProductTypes.id AS `ProductTypes__id`,
                        ProductTypes.code AS `ProductTypes__code`,
                        ProductTypes.title AS `ProductTypes__title`,
                        ProductTypes.calc_cost AS `ProductTypes__calc_cost`,
                        ProductGroups.id AS `ProductGroups__id`,
                        ProductGroups.code AS `ProductGroups__code`,
                        ProductGroups.title AS `ProductGroups__title`
                      FROM `product_titles` ProductTitles
                      LEFT JOIN `products` Products 
                            ON Products.id = ProductTitles.products_id
                      LEFT JOIN `product_groups` ProductGroups 
                            ON Products.product_groups_id = ProductGroups.id
                      LEFT JOIN `product_types` ProductTypes 
                            ON Products.product_types_id = ProductTypes.id
                        ))";

    $products = $this->Products->find('all')
                               ->from([$this->Products->alias() => $products_union])
                               ->contain(['ProductTypes', 'ProductGroups'])
                               ->where($where)
                               ->order(['Products.title']);

Now, the 'LIMIT' and 'OFFSET' are in the right place. I still using the 3.4 CakePHP version.

CakePHP 3 paginating a result from union query

1 Answers1