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';
'"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
'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
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.
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.