Bug using relation and pagination in a FindAll from ActiveRecord

[prchakal (Paulo)]

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:

public function actionTeste()

{


	$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;





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


}

My relations:

public function relations()

{


	return array(


		'sites'          => array(self::MANY_MANY,  'Site',           'product_site(product_id, site_id)'),


		'options'        => array(self::HAS_MANY,   'ProductOption',  'product_id'),


	);


}

Generated query:

CDbCommand falhou ao executar o comando SQL: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘options.real_product_id’ in ‘where clause’. The SQL statement executed was: SELECT t.id AS t0_c0, t.category_main AS t0_c1, t.category_id AS t0_c2, t.producttitle AS t0_c3, t.detailed_descrip AS t0_c4, t.or_price AS t0_c5, t.or_price_alt AS t0_c6, t.peso_product AS t0_c7, t.fornecedor_id AS t0_c8, t.external_product_id AS t0_c9, t.audio AS t0_c10, t.cost_price AS t0_c11, t.real_product_id AS t0_c12, t.lote AS t0_c13, t.type_payment_ship AS t0_c14, t.meta_keywords AS t0_c15, t.meta_description AS t0_c16 FROM product t WHERE (options.real_product_id = :real_product_id) LIMIT 10

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?

[mdomba (Maurizio Domba Cerin)]

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

[prchakal (Paulo)]

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.

[mdomba (Maurizio Domba Cerin)]

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

Unknown column ‘options.real_product_id’ in ‘where clause’.

So… did you try what I suggested above?

[prchakal (Paulo)]

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.

[prchakal (Paulo)]

Can anyone help me with this bug?

[jacmoe (Jacob Moen)]

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);

	}

[prchakal (Paulo)]

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

[abennouna]

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.

[jacmoe (Jacob Moen)]

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.

[prchakal (Paulo)]

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

[softark]

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.

[prchakal (Paulo)]

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

[softark]

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.

[jacmoe (Jacob Moen)]

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.

[softark]

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