How to add limit to relations?

I added limit option in controller:




$dataProvider=new CActiveDataProvider('Prediction', array(

			    'criteria'=>array(

			    'select'=>'pr.*',

			    'condition'=>$conditionStr,

			    'alias'=>'pr',

			    'order'=>'date DESC',

			    'with'=>array('author','option','post',' selection'=>array('order'=>'selection.uid DESC','limit'=>1)),

			    ),

			    'pagination'=>array(

			        'pageSize'=>5,

			    ),

			));



Also added limit option in relation:




'selection' => array(self::HAS_MANY, 'Selection', 'pid','limit'=>2),



But none or them work, it loads all the record, what mistake I’ve made? Any idea?

try limit here




$dataProvider=new CActiveDataProvider('Prediction', array(

                            'criteria'=>array(

                            'select'=>'pr.*',

                            'condition'=>$conditionStr,

                            'alias'=>'pr',

                            'order'=>'date DESC',

                            'limit'=>1

                            'with'=>array('author','option','post',' selection'=>array('order'=>'selection.uid DESC')),

                            ),

                            'pagination'=>array(

                                'pageSize'=>5,

                            ),

                        ));



I thought this will output 1 ‘Prediction’ not 1 ‘Prediction->selection’. But I was wrong, this give limits to nothing. I mean nothing change… :o

@adamwu,

You set all things properly like shorting by , limit …

I thnk your code had to work.

There might be problem with your "condition" or "with" functionality…

I tried to simplify the situation to check whether limit works at all:




$dataProvider=new CActiveDataProvider('Prediction', array(

			    'criteria'=>array(

					'select'=>'pr.*',

			        'condition'=>'status=1',

					'alias'=>'pr',

			        'order'=>'date DESC',

        			'limit'=>1,

			    ),

			    'pagination'=>array(

			        'pageSize'=>5,

			    ),

			));



The result is it doesn’t work. Where else can be wrong?

I just found out limit defined in relations works in ‘lazy mode’. But the limit add to ‘with’ will not work at all.

Hi adamwu,

Why not you guys use a mysql view here if you are binding your data with 2 or 3 models at a time.This is some advance concept to enhance your mysql queries.In this way you can easily do a pagination on View model.And performance wise it would be the best option here. ;)

But if you dont want to use any mysql view then Your code work.But it seems that you are missing some thing here. Which is Grouping your End result.;) So,i will suggest you to use a Group parameter also here.Which will make your code work as you want.:)

Let it try !!

Hi jayant,

I am in the middle of project, so I can not start again from mysql view. So I followed your second advice:




$dataProvider=new CActiveDataProvider('Prediction', array(

			    'criteria'=>array(

					'select'=>'pr.*',

			        'condition'=>$conditionStr,

					'alias'=>'pr',

			        'order'=>'date DESC',

					'with'=>array('author','option','post','selection'=>array('order'=>'selection.uid DESC','limit'=>2)),

        			'group'=>'pr.id'

 

			    ),



But it seems there still no good luck. Can you give me more information?

		    ),







Could you please share your condition Variable which you have set there




‘condition’=>$conditionStr,

$conditionStr Really doesn’t matter, because I already tried changing it to ‘status=1’ or even commeting it out:




$dataProvider=new CActiveDataProvider('Prediction', array(

			    'criteria'=>array(

					'select'=>'pr.*',

			        //'condition'=>'status=1',

					'alias'=>'pr',

			        'order'=>'date DESC',

					'with'=>array('author','option','post','selection'=>array('order'=>'selection.uid DESC','limit'=>2)),

        			'group'=>'pr.id'

			    ),

			    'pagination'=>array(

			        'pageSize'=>5,

			    ),

			));

        $this->render('index', array(

            'dataProvider' => $dataProvider, 

        ));



Its Quite interesting thing :unsure:

After using a group still its not working.Well i have to test this code at my end let me check it and get back here.

I am using 1.1.10 version, FYI. Looking forward to your test result.

Hey adamwu,

i come up with some the solution.

Its seems very interesting thing Which i got on some of the forum threads.

After reading these post i got that this is the problem of selecting a fields with joining condition as some the other user also got it.

Even, i haven’t use code like you before.But when i check my code i got that i haven’t used a select in criteria in my case with ‘with’ param.But In your case you have to do something different for retrieving a selected fields.

You can check below given threads for the solution i found.

Link1

Link 2

Might be this will solve your problem.And Make sure you select the fields correctly here.

I tried everything and found this:

So I think limit only works in lazy mode. What a strange thing! Unlimited recorded will be loaded!

Ahh ,that is a heights of bad luck now… :o

Now what can we do.But still i will suggest you to use a mysql view here. I can assure you it will not take too much time to create it and change your code according to that.

Little more information for the bad luck guys like me :( :

‘limit’ for relations only works in relations definition like:




'post' => array(self::HAS_MANY, 'Post', 'pid', 'limit'=>1),



And it even will lot work like this:


$post=$data->post(array('limit'=>1));

This will force me to get all raw data by basic querying and then use CArrayProvider. This will cut of f a lot of nice feature though.

@Qiang: 加油,加油啊!

Yeah,

You are right.Even i was also stuck last night like this kind of work.Since, Its was a big issue with Yii regarding a relational database.

That is why i have just created That thread

Also talk about this kind of general issues in Yii 1.1.X with Yii dev team members and they inform me that might be they will fix these issues in Yii 2.0 version.

So, now wat can we do here at this moment. :)

But again i will recommend you to use Mysql View :P