I have two tables that I have joined, on my controller I got
$criterias = new CDbCriteria();
$q = $_GET['q'];
$criterias->join='RIGHT JOIN ECCategory AS b ON b.ID = t.CategoryID';
$criterias->select=array( '"t".*',
'"b"."Name" AS "cName"',
'"b"."ID" AS "cID"',
);
$criterias->compare('t.Name', $q, true, 'AND');
$criterias->compare('b.Name', $q, true, 'OR');
$criterias->compare('t.Spec1', $q, true, 'OR');
$dataProvider=new CActiveDataProvider("ECProduct", array('criteria'=>$criterias));
on my ECProduct model I have
public $cName; // for the ECCategory Name
public $cID;
and
'category' => array(self::BELONGS_TO, 'ECCategory', 'CategoryID'), // CategoryID = FK
That’s how I joined them, now if I search anything I get the results I want only the last page always duplicates. Say my search hits 11 results, it will give me 2 pages. The 1st page with the 1st 10 the 2nd page will show the 11th and repeats 1-9 making each page have 10 results. So unless I get results in 10s 20s…60s etc I will get duplicates on the last page. I tried various joins like FULL, LEFT etc but all the same.
The closest solution I can think of is setting a totlaItemCount=>$count, that way it will give me a fixed amount. The only problem is how do I count the numbers of item returned from a search query before it’s displayed?
Does anyone know how I can solve this please? I’m joining tables often for this project and I get the same problem. It’s like each page must contain 10 items, what’s more is that it will say found 11 results and still displays 20 duplicating the last 9 results. Cannot find much on the same issue either
I’d try to simplify your example to the most basic version and then would add needed code, checking on each step if the problem appeared at this stage.
Also, check which sql request is executed and test this request results directly from mysql (using phpmyadmin or whatever you use).
From you post it’s not clear which methods do you use to request you results, I’d also debug these methods to see which way your code is processed and at which stage you get the problem.
I’m leaning towards the idea this has something to do with Yii and a possible bug. Maybe ClistView was never supposed to be used this way (searching and so on).
For some reason it insists on showing 10 results on each page, so if the final page has < 10 it will duplicate to complete 10.
Still can’t find a solution to this problem, I tried using CSqlDataProvider, tried using totalItemCount getting count from $count=Yii::app()->db->createCommand… and I have tried about 100 other things. Still yii pagination persists on showing 10 results per page even if it means duplicates.
I’m using the default settings of pagination as that’s enabled on CActiveDataProvider, I have read through those Api’s several times before. If I add pagination like so
Thanks for the reply Softark but I just fond out literally about an hour ago that it was a MSSQL problem and these guys here figured it out SOLVED Duplicates. I saw all my tables had duplicates even the ones I didn’t join.
It was nothing to do with joining tables it’s just the way ClistView shows MSSQL data. Since you guys are the developers can you please include the solution on the link on your docs.
Also connecting MSSQL has been a very tricky task for me and getting things to work with downloading extensions/ configuration took it’s toll. With MySQL it was a breeze, it will be nice it was the same with MSSQL
Mind you I’am an amateur here so perhaps it’s common I struggled.
Thanks for the links, I will be joining those discussions. I think making a database with Yii in mind should never be done with MSSQL. In my situation the database was made by a 3rd party so I had no choice.