Hello, I have a model that lists swimmers name and their competition-times in a CGridview.
I have a condition in the model that sorts the times so that only the best time that each swimmer have can be shown. The problem is that the pagination does not work when the condition takes place, everything else works fine.
See error below, this pops up when I click on a pagination-button:
Error 500: PHP Error
Preg_match():Compilation failed:unmatched parentheses at offset 22
…
This is my model(values are hard-coded at the moment)
public function search()
{
// Warning: Please modify the following code to remove attributes that
// should not be searched.
$criteria=new CDbCriteria;
$criteria->addCondition(
'EXISTS(
SELECT DISTINCT t.*
FROM Result
INNER JOIN Event evt ON evt.event_code = t.event_code
INNER JOIN Swimmer swm ON t.swimmer_id = swm.swimmer_id AND swm.class_id = 1 AND swm.is_deleted = 0 AND
DATEDIFF(year, swm.date_of_birth, t.result_date) BETWEEN 11 AND 99
AND YEAR(t.result_date) = 2012
AND t.event_code = 35 AND t.result_id IN
(SELECT TOP 1 bestRes.result_id
FROM Result AS bestRes JOIN
Event AS bestEvt ON (bestEvt.event_code = bestRes.event_code) JOIN
Swimmer AS bestSw ON (bestRes.swimmer_id = bestSw.swimmer_id) AND
bestSw.class_id = 1 AND bestSw.is_deleted = 0 AND
(datediff(year, bestSw.date_of_birth, bestRes.result_date) BETWEEN 11 AND 99)
AND YEAR(result_date) = 2012
AND bestRes.event_code = 35 AND t.swimmer_id = bestRes.swimmer_id
ORDER BY bestRes.result_time)
)'
);
return new CActiveDataProvider($this, array(
'criteria' => $criteria,
'sort'=>array(
'defaultOrder'=>'result_time ASC',
),
));
}
It has something to do with the last row in the SQL-statement "ORDER BY bestRes.result_time", when I remove that line the pagination works but the Gridview displays the wrong results.
Any ideas how I can fix this?
Maybe it’s a better way of handeling these kind of SQL-queries?
You might have to change the sql to remove the ORDER BY clause.
$criteria->addCondition(
'EXISTS(
SELECT DISTINCT t.*
FROM Result t
INNER JOIN Event evt ON evt.event_code = t.event_code
INNER JOIN (
SELECT bestRes.result_id, MIN(bestRes.result_time) as bestTime
FROM Result AS bestRes
JOIN Event AS bestEvt ON (bestEvt.event_code = bestRes.event_code)
JOIN Swimmer AS bestSw ON (bestRes.swimmer_id = bestSw.swimmer_id) AND
bestSw.class_id = 1 AND bestSw.is_deleted = 0 AND
(datediff(year, bestSw.date_of_birth, bestRes.result_date) BETWEEN 11 AND 99)
AND YEAR(result_date) = 2012
AND bestRes.event_code = 35
AND t.swimmer_id = bestRes.swimmer_id
) best ON best.result_id = t.result_id AND best.bestTime = t.result_time
INNER JOIN Swimmer swm ON t.swimmer_id = swm.swimmer_id AND swm.class_id = 1 AND swm.is_deleted = 0 AND
DATEDIFF(year, swm.date_of_birth, t.result_date) BETWEEN 11 AND 99
AND YEAR(t.result_date) = 2012
AND t.event_code = 35
)'
);
Or try to check to see what the actual query is that is being parsed.
CActiveDataProvider will construct its own ORDER BY clause according to its ‘sort’ property, and the ORDER BY clause will be inserted to the sql that has been created by ‘criteria’ property.
So when your criteria has generated an ORDER BY clause, the data provider will to try to replace it with the one created by ‘sort’ property, hence a preg_match error.
I don’t know if we should call it a bug or a so-called spec by design.
(There might be a same kind of problem with ‘OFFSET’ and ‘LIMIT’ clause against ‘pagination’ property … I’m not very sure)
It’s almost the same with CSqlDataProvider in this problem.
SELECT r.*
FROM Result r
inner join (
select swimmer_id, min(result_time) as FastestTime
from Result
group by swimmer_id
) rm on r.swimmer_id = rm.swimmer_id and r.result_time = rm.FastestTime
This one works in CSqlDataProvider to get the best times for each swimmer, but im not very good at SQL so I need to use $criteria to take care of the rest, like year-filter and age-filter. And that only works with CActiveDataProvider.
My new query does not work in CActiveDataProvider tho, it still show all rows, so I was wondering if it’s possible to “convert” the query to the “yii way” with select, group and join somehow?
Is this to advanced to have in the CActiveDataProvider?
Adding this to the criteria may work, haven’t tried it though
$criteria->join = 'inner join (
select swimmer_id, min(result_time) as FastestTime
from Result
group by swimmer_id
) rm ON t.swimmer_id = rm.swimmer_id and t.result_time = rm.FastestTime';
Thanks, it’s working, but I realized that I have another problem.
In the Result-table there is a column called event_code, that column specificies which event the result-time belongs to, 100 freestyle, 200 freestyle etc.
My SQL-statement only retrieves the best time of all events, not the best time of each event.
addcondition and condition does not work in this case since the "join" has already retrieved the rows.
Any ideas how I can include event_code = ‘a number’ in the join?
I can then include "param" and match it with my search-condition.
