Bug using relation and pagination in a FindAll from ActiveRecord

Hi,

I found a bug that when i use "->with(…relations…)" and "findAll($criteria)" with limit and offset on criteria, it ignore my relation and dont make the query with relations.

My method:

My relations:

Generated query:

I use Yii since 1.1 version and never have problems with it and all my customers websites i use it. But ow i have this problem, i think that it is a bug, because is too basic to have anything wrong with code and i think that im not doing it wrong.

Can anyone check it?

By documentation - http://www.yiiframew…ia#alias-detail

the default table alias for the current model is "t" so instead of "options.real_product_id" you should use "t.real_product_id".

You dont understand the problem.

I dont want the field from main table. I want from the relation (called options). You see the relation on the query?

If you comment offset/limit form criteria, it works, but without the offset/limit.

Maybe I don’t understand your problem… but I just read the error message you posted… and that error is very clear… it says:

So… did you try what I suggested above?

This is caused because i want the field from relation, it is clear that the main table and the other table has the same column name. Both tables has the same field name (real_product_id) or in one big database i can have only one column with the same name? So simple to understand.

Can anyone help me with this bug?

This is obviously a bug in your own code.

So I am going to move this to Yii 1.1 General Discussion.

I think you need to be more explicit in your ‘actionTeste’:


public function actionTeste()

	{

		$criteria = new CDbCriteria();

$criteria->with = array('sites', 'options');


		$criteria->condition = 'options.real_product_id = :real_product_id';

		$criteria->params    = array(':real_product_id' => 12345);

        $criteria->offset    = 0;

        $criteria->limit     = 10;


		$list = Product::model()->findAll($criteria);

	}

Why this is a bug in my code?

If i use relations and use limit/offset in the same criteria, the relations are not created in query and the field that i want doesnt exists(because the table with alias options doesnt exists - it come from relation) and the bug is in my code?

Where is the LEFT JOIN in the query? Im using WITH WITH WITH, so WHERE IS THE RELATION IN THE GENERATED QUERY?????????????????

Hi there, I think that you can’t use with(relation) and findAll($criteria) like you did, I believe this is the bug.

Where have you read in the documentation that you can mix both?

IMHO, what fellow members suggest is a (the?) correct way to achieve what you want.

Just try it.

Instead of ‘options’ you should probably use ‘ProductOption’ or ‘productOption’ (depending on your database):


public function actionTeste()

        {

                $criteria = new CDbCriteria();

$criteria->with = array('ProductOption');


                $criteria->condition = 'ProductOption.real_product_id = :real_product_id';

                $criteria->params    = array(':real_product_id' => 12345);

        $criteria->offset    = 0;

        $criteria->limit     = 10;


                $list = Product::model()->findAll($criteria);

        } 						 						

And the amount of CAPS and smileys really doesn’t make me want to help you out more - quite the opposite. :P

@bennouna : You are the first that understand the problem!!!! Thanks man!!!!! :)

My question is about mix it, when i mix relation with limit/offset, it doesnt generate the left join on query, and when i remove limit/offset from criteria, it create the query with the left join.

So, i think that is a bug, no?

What the problem in using JOIN with LIMIT ?

It’s not a bug, but so-called ‘BY DESIGN’.

You have to set ‘together’ to true in order to filter by a HAS_MANY relation when you set LIMIT and OFFSET.

http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-performance

It’s a little complicated, but there’s a rule.

  1. ‘with’ => eager loading

  2. ‘with’ + ‘LIMIT/OFFSET’ => lazy loading

  3. ‘with’ + ‘LIMIT/OFFSET’ + ‘together’ => eager loading

The eager loading executes one single lengthy query, joining related tables.

The lazy loading executes one main query for the main table, and then executes N queries for the related table per each retrieved main row.

In order to filter by the relation, you need to do the eager loading.

However, when you want a correct pagination, you need to do the lazy loading.

In your case, you can filter by the relation by specifying ‘together’ to true, but, unfortunately, you will not get the correct count/offset of rows.

@softark

WOW Man!!! It works :)

Very helpfull :)

The solution was:




$criteria = new CDbCriteria();

$criteria->condition = 'options.real_product_id = :real_product_id';

$criteria->params    = array(':real_product_id' => 12345);

$criteria->offset    = 0;

$criteria->limit     = 10;

        

$criteria->with = array(

     'options' => array('together'=>true),

);


$list = Product::model()->with('sites')->findAll($criteria);



Many thanks man, problem solved!!

Really?

You should check the actual count of rows that get retrieved.

When one of your Product has 2 or more Options, then you’ll get less than 10 rows.

And so what if it does have less than 10 rows?

The only thing which matters here is that it will always return a maximum of 10 rows.

Well, I’m thinking about the scenario when there are more than 10 rows and some of the main row has 2 or more related rows.

Eagerly loaded joined data




Row Product Option

1   1       A

2   1       B

3   2       C

4   3       D

5   4       E

6   4       F

7   4       G

8   5       H

9   6       I

10  7       J

---------------- limit

11  7       K

12  8       L

...



Retrieved AR objects




Row Product Options

1   1       A, B

2   2       C

3   3       D

4   4       E, F, G

5   5       H

6   6       I

7   7       J (missing K)



As you see, there are 3 possible problems.

  1. count of rows may be less than expected

  2. offset other than 0 may not be correct

  3. AR object may not have all the related objects