sfDoctrine: adding queries to pager

[ponticelli]

Which is the right way to add WHERE clauses to a sfDoctrinePager object?
I've tried in several ways but it seems that the parameters are not passed correctly.
Thank you for your help.

[ponticelli]

I've forgotten to mention that the problem is in the autogenerated query for the count.

Looking more at the code I see that in sfDoctrinePager the method setQuery accepts $query and $params and passes those values to the method parseQuery of Doctrine_Query which instead accepts the parameters $query and $clear ... $params are unused or I'm missin something?

[ponticelli]

The problem seems to be in the init() method of sfDoctrinePager ...
the call

Code: Select all

$count = $pForCount->count(sfDoctrine::getTable($this->getClass()));


Does not allow to pass any parameter to the query ...

[chtito]

The where clause is not necessary with the sfDoctrine pager.

Besides you can specify any query you'd like by using the setQuery method.

What do you mean by "autogenerated query for the count"? Autogenerated by the admin generator?

Feel free to explain exactly what you want to achieve and hopefully we'll figure out a solution.

[ponticelli]

I'll try to explain better.
I have a CRUD (made by myself using symfony) which uses the sfDoctrinePager class in the list section ... my lists can be filtered adding where conditions.

The first problem is that the method $pager->init() executes a count query for retrieving the total number of results. This query will have the same where conditions of the one used to retrieve the resultset but the count() method used doesn't pass any parameter.

The second problem is that I can't use the method setQuery of the sfDoctrinePager class to set my query and parameters because the parameters in this method are misused.

Now the question, how do you use the pager when you have to apply custom where clauses?

[chtito]

My answer above was totally stupid. ~ ~ Apologies.

You are totally right. There is a problem with the setting of the query, the parameters are not set indeed. I'll try to fix that asap. This class has to be cleaned up anyway. I'll do that tonight if i find some time for that.

Stay tuned. ~ ~

[ponticelli]

Thank you. I'll refresh my subversion repository every 5 minutes

[chtito]

I'm not sure i fully understood your problem. Now you set up the pager using getQuery like that:

Code: Select all

$p = new sfDoctrinePager('myClass', 10);
$p->getQuery()->addWhere('foo = ?', 'bar');
$p->init();


and the count will take into account your "where" condition.

Does that solve your problem?

[ponticelli]

No it does not ~ ~

The code in your example is exactly what I'm doing ... running it I receive the following error:

Code: Select all

"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1"


The problem is in the query executed in the init method of sfDoctrinePager:

Code: Select all

$count = $pForCount->count(sfDoctrine::getTable($this->getClass()));


This method expects as second parameter the parameters for the where clauses ... but they are not passed at all ... as a workaround I've added a public method getParams() in Query and altered the code above this way:

Code: Select all

$count = $pForCount->count(sfDoctrine::getTable($this->getClass()), $pForCount->getParams());


I hope this helps ...

p.s. No one is using sfDoctrinePager with custom where clauses.

[chtito]

You are right, ponticelli. It is actually a doctrine bug. Here is the ticket (and a fix) i submitted: http://doctrine.pengus.net/trac/ticket/218

Now your problem should be solved... ~ ~

[ponticelli]

Thank you very much.