[ItsYii]

Hi

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?


Thanks

[ItsYii]

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

Thanks

[yugene]

Hi ItsYii,

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.


Regards,
Yuga

[ItsYii]

Hi Yuga, thanks for the reply

Quote

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.

It's pretty simple as it is, I tried various joins and all the same, if I don't join the table then the results are fine (no duplicates)

Quote

Also, check which sql request is executed and test this request results directly from mysql (using phpmyadmin or whatever you use).

I'm using MSSQL and when I put the exact query Yii is executing I get accurate results (no duplicates)

Quote

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.

Not sure what methods you referring too, the only thing missing from what I posted is the get action ...

<form method="get" action="/mySite/index.php/site/search/">
<input type="search" name="q" id="aderForm"  size="28" value="<?=isset($_GET['q']) ? CHtml::encode($_GET['q']) : '' ; ?>" />
<input type="submit" value="" class="searchBtn" />
</form>

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.

Cheers

[ItsYii]

ItsYii, on 13 November 2012 - 04:36 AM, said:

Hi Yuga, thanks for the reply



It's pretty simple as it is, I tried various joins and all the same, if I don't join the table then the results are fine (no duplicates)



I'm using MSSQL and when I put the exact query Yii is executing I get accurate results (no duplicates)



Not sure what methods you referring too, the only thing missing from what I posted is the get action ...

<form method="get" action="/mySite/index.php/site/search/">
<input type="search" name="q" id="aderForm"  size="28" value="<?=isset($_GET['q']) ? CHtml::encode($_GET['q']) : '' ; ?>" />
<input type="submit" value="" class="searchBtn" />
</form>

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.

Cheers

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.

Need some help here

Cheers

[Ujjwal Prajapati]

Hello there,

I don't see u using any pagination parameter in CActiveDataProvider. Please take a look at the documentation to see how to use the pagination.

http://www.yiiframew...iveDataProvider
http://www.yiiframew...1.1/CPagination

Let me know if this helps.

Thanks.

[ItsYii]

Ujjwal Prajapati, on 16 November 2012 - 04:16 AM, said:

Hello there,

I don't see u using any pagination parameter in CActiveDataProvider. Please take a look at the documentation to see how to use the pagination.

http://www.yiiframew...iveDataProvider
http://www.yiiframew...1.1/CPagination

Let me know if this helps.

Thanks.

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

$dataProvider=new CActiveDataProvider("ECProduct", array('criteria'=>$criterias,  'pagination' => array('pageSize' => 10, ),
			
		));

makes no difference.



Thanks

[softark]

Hi ItsYii,

It might be caused by the way you are rendering the CListView.
I used the combination of CListView and CActiveDataProvider in many places, and it worked quite fine all the way.
Would you please post your view code?

[ItsYii]

softark, on 18 November 2012 - 03:58 AM, said:

Hi ItsYii,

It might be caused by the way you are rendering the CListView.
I used the combination of CListView and CActiveDataProvider in many places, and it worked quite fine all the way.
Would you please post your view code?

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.

Cheers

[softark]

I see. I have to apologize you.

I'm not a developer and can't do much about this issue because I don't use MSSQL for the moment.

BTW, I found some issue tickets and PR in github.
https://github.com/y.../yii/issues/166
https://github.com/y...yii/issues/1137
https://github.com/y...t/yii/pull/1318
It seems a long persisting issue, but I hope they are on the way to correct it.
Why don't you join the discussion there?

[ItsYii]

@Softark

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.

Cheers