Yii Framework Forum: Joining Tables Shows Duplicate Data - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Joining Tables Shows Duplicate Data Table join is duplicating data on the last page Rate Topic: -----

#1 User is offline   ItsYii 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 117
  • Joined: 24-January 12

Posted 11 November 2012 - 02:21 AM

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
0

#2 User is offline   ItsYii 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 117
  • Joined: 24-January 12

Posted 13 November 2012 - 02:59 AM

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
0

#3 User is offline   yugene 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 488
  • Joined: 08-August 09
  • Location:World is Wide

Posted 13 November 2012 - 03:34 AM

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
0

#4 User is offline   ItsYii 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 117
  • Joined: 24-January 12

Posted 13 November 2012 - 04:36 AM

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
0

#5 User is offline   ItsYii 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 117
  • Joined: 24-January 12

Posted 14 November 2012 - 08:32 AM

View PostItsYii, 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
0

#6 User is offline   Ujjwal Prajapati 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 47
  • Joined: 24-April 12

Posted 16 November 2012 - 04:16 AM

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.
0

#7 User is offline   ItsYii 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 117
  • Joined: 24-January 12

Posted 17 November 2012 - 09:00 AM

View PostUjjwal 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
0

#8 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,005
  • Joined: 16-February 11
  • Location:Japan

Posted 18 November 2012 - 03:58 AM

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?
0

#9 User is offline   ItsYii 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 117
  • Joined: 24-January 12

Posted 18 November 2012 - 07:21 AM

View Postsoftark, 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
0

#10 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,005
  • Joined: 16-February 11
  • Location:Japan

Posted 18 November 2012 - 06:55 PM

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?
0

#11 User is offline   ItsYii 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 117
  • Joined: 24-January 12

Posted 19 November 2012 - 04:01 AM

@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
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users