Issues

ZF-7689: Paginator DBSelect Adapter

Issue Type: Bug Created: 2009-08-26T00:23:24.000+0000 Last Updated: 2009-11-02T18:01:20.000+0000 Status: Closed Fix version(s): Reporter: Andrei Iarus (poni1111) Assignee: Ramon Henrique Ornelas (ramon) Tags: - Zend_Paginator

Related issues: - ZF-7259

Attachments:

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

Posted by Andrei Iarus (poni1111) on 2009-08-26T06:52:53.000+0000

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.

Posted by Jurrien Stutterheim (norm2782) on 2009-08-26T08:11:16.000+0000

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

Posted by Jurrien Stutterheim (norm2782) on 2009-10-16T00:06:02.000+0000

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

Posted by Andrei Iarus (poni1111) on 2009-10-17T02:32:35.000+0000

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)

Posted by Ramon Henrique Ornelas (ramon) on 2009-11-02T06:58:36.000+0000

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.

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts