Issues

ZF-7689: Paginator DBSelect Adapter

Description

When having a Select with an orWhere method called (and the searched fieldX is from a table left joined to the first one), it will fail (in the adapter's method count() ) with a message like (fieldsX not found).

The solution is to reset also the where part of the Select (in the getCountSelect() adapter's method ).

Comments

Forgot to add, that on the query the distinct part is added.

The resulted select query that counts the items would be like:

SELECT COUNT(1) AS ... FROM ( SELECT .... FROM... LEFT JOIN... WHERE... ) as t1 WHERE...

Mainly, there are to where parts, which is wrong.

Could you paste your original code and query that reproduce this problem? Thanks.

  • bump * Andrei, could you paste your original code and query that reproduce this problem?

Jurrien, sorry for late response. The resulted query (as string) that is passed to the paginator is:

SELECT DISTINCT c.customer_id, c.code, c.first_name, c.last_name, c.company, c.email, sl.sales_person_id, CONCAT(e.first_name, ' ', e.last_name) AS sales_person FROM core_customers AS c LEFT JOIN customers_vehicles_sales AS sl ON c.customer_id=sl.customer_id LEFT JOIN core_employees AS e ON sl.sales_person_id = e.employee_id WHERE (e.first_name LIKE '%phil%') OR (e.last_name LIKE '%phil%')

Please note the distinct part, and the OR-where part.

The query is generated with somethnig like:

$sleect = $db->select()->distinct() ->from( array('c' => 'core_customers',), array('c.customer_id', 'c.email') )

                    ->joinLeft( array('sl' => 'customers_vehicles_sales'), 'c.customer_id=sl.customer_id', 
                                array('sl.sales_person_id') ) 
                    ->joinLeft( array('e' => 'core_employees'), 'sl.sales_person_id = e.employee_id', 
                                array( 'sales_person' => new Zend_Db_Expr('CONCAT(e.first_name, \' \', e.last_name)') ) 
                                ) 
                    ->where('e.first_name LIKE ?', '%' . 'john' . '%') 
                    ->orWhere('e.last_name LIKE ?', '%' . 'john' . '%'); 

I hope there is no problem to get tables' structure from the query above.

PS: it seems that the problem was solved in version 1.9.4 (but did only a very quick test)

I had this problem in 1.8.4, but had SVN Trunk fix before the release of 1.9.0.

Versions greater than 1.8.4 have been resolved this problem.