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
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 ).
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
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:
sales_person_id, CONCAT(e.first_name, ' ', e.last_name) AS
c LEFT JOIN
sl ON c.customer_id=sl.customer_id LEFT JOIN
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.