Relational Active Record

Hi,

I have 3 tables.

Product

Product_Description

Product_Price

Each Product can have MANY Description and Price

What I want to do is get a list of all products in a table.

But the thing is I want the following columns.

Product.id

Product_Description.name

Product_Price.price

Also I want to be able to sort by all these columns.

Can I do this with Relational Active Records?

I was thinking I could do the one lines like:

Product::model()->with(‘product_description’,‘product_price’)->findAll();

I have tried putting the table names in the where conditions and scopes also to sort but it doesn’t work.

I am trying to learn the best method or way to achieve this.

Please let me know if you have an idea.

You should use the relation name, http://www.yiiframework.com/doc/guide/database.arr

Product::model()->with(‘relationName’,‘productPrice’)->findAll();

Yes that is what I tried, for example I did:




Tbl_Product::model()->with('product_description','product_price')->findAll(array(

    'condition' => $where,

    'order' => $sort,

    'offset' => $start,

    'limit' => $limit,

    'params' => $params,

));



product_description being a relation name and product_price also.

but how can I use Tbl_Product but order by product_description "name" column or product_price "price" column?

I tried product_description.name in the order portion but it says the column can’t be found.

Check if the second example will help you out

http://www.yiiframework.com/doc/api/CActiveRecord#with-detail

/Tommy

So from what I can understand from that example is:

If I have a table with a Product ID column, Product Description NAME column and Product Price PRICE column and I want to order by one of the columns depending on which one it is and in which model it is I have to pass the order by to the right relation.

So if I want to get the product with its description and price I would do:


Product::model()->with(array(

	'product_description'=>array(

    	'order'=>$order_pd

     ),

     'product_price'=>array(

     	'order'=>$order_pp

     )

))->findAll(array(

	'order'=>$order_p

));

Sounds about right to you?

Oh I noticed something wierd, don’t know if it is a bug or not.

But if I do the following:


foreach (Tbl_Product::model()->with(array('product_description','product_price'))->findAll(array(

    'condition' => $where,

    'order' => 'id ASC',

    'params' => $params,

)) as $row) {					

    // item node

    $responce->rows[$i]['id'] = 'product-'.$row->id;

    $responce->rows[$i]['cell'] = array(

        $row->id,

        $row->product_description->name,

        $row->product_type,

        $row->active,

        $row->product_price->price,

    );

    

    ++$i;

}

It crashes this error:

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘id’ in order clause is ambiguous

But if I add:


foreach (Tbl_Product::model()->with(array('product_description','product_price'))->findAll(array(

    'condition' => $where,

    'order' => 'id ASC',

    'limit' => $limit,

    'params' => $params,

)) as $row) {					

    // item node

    $responce->rows[$i]['id'] = 'product-'.$row->id;

    $responce->rows[$i]['cell'] = array(

        $row->id,

        $row->product_description->name,

        $row->product_type,

        $row->active,

        $row->product_price->price,

    );

    

    ++$i;

}


echo json_encode($responce);

}	

It works. The only thing I have added is a limit of 10.

Is this normal?

Kheang Hok Chin,

Sorry for not being able to help you out right now. I deliberately just answered “Check if the second example will help you out …” because AR had a number of changes/additions since Yii 1.0, and I have to test every thought myself before knowing if it will work. I’ll rest my case until my next db design adventure. ;)

I suggest you enable logging and examine the generated SQL, in order to get a better picture of what’s going on. (My first guess why “limit” makes the error go away is that multiple SQL statements was generated).

/Tommy

Thanks Tommy for your help!

Thanks!

Ok so for exceptions I was able to figure out that I had to read the log files to see the SQL it creates.

And indeed in the SQL itself, it has only 1 table.

But now I need to find out why when I remove the limit, it actually works.

How do I echo the SQL being sent when it is successful, so I can compare both?

For instance, have a look at this thread.

(I used this Google search "site:yiiframework.com sql logging")

/Tommy

Thanks Tommy that helped!

This is what i’m getting, which is pretty wierd:

First command:

(i removed the columns because it made smaller queries to show here so ignore that.


Tbl_Product::model()->with(array('product_description','product_price'))->findAll(array(

    'condition' => $where,

    'order' => 'product_description.name ASC',

    'limit' => 10,

    'params' => $params,

)


SELECT 

*

FROM 

`product` `t`  

ORDER BY 

product_description.name ASC 

LIMIT 10

2nd command without the limit:


Tbl_Product::model()->with(array('product_description','product_price'))->findAll(array(

    'condition' => $where,

    'order' => 'product_description.name ASC',

    'params' => $params,

)


SELECT 

*

FROM 

`product` `t` 

LEFT OUTER JOIN 

`product_description` `product_description` 

ON 

(`product_description`.`id_product`=`t`.`id`) 

LEFT OUTER JOIN 

`product_price` `product_price` 

ON 

(`product_price`.`id_product`=`t`.`id`) 

WHERE 

(language_code="en") 

ORDER BY 

product_description.name ASC

Wierd huh? I wonder if anyone have had this same problem.

I also found this interesting read: http://www.yiiframework.com/doc/guide/database.arr

Although my Product model has relations defined like this:


/**

 * Specifies table relations

 */

public function relations()

{

    return array(

        /**

          * A product has many descriptions (1 per language)

          * Our relation links that description to our product with the current language

          **/

        'product_description' => array(self::HAS_MANY, 'Tbl_Product_Description', 'id_product', 'condition' => 'language_code="'.Yii::app()->language.'"'),

        

        // A product has many prices (1 per price type)

        'product_price' => array(self::HAS_MANY, 'Tbl_Product_Price', 'id_product'),

    );

}	

Ok I kind of get why it is not working.

It’s really wierd, because I understand by looking at the queries itself why it wouldn’t work.

But by doing it this way, it should work, but it doesn’t:


Tbl_Product::model()->findAll(array(

    'with' => array(

        'product_description'=>array(

            'on'=>'language_code="'.Yii::app()->language.'"',

        ),

        'product_price'=>array(

            'on'=>'id_price_type='.Yii::app()->getGlobalState('default_price_type'),

        ),			

    ),

    'condition' => $where,

    'order' => $sidx." ". $sord,

    'limit' => 10,

    'params' => $params,

))

Basically, what I am doing in the above is getting all listings of Product, with their Product Description and Product Price, but for the Product Description and Product Price, I am adding an ON clause so that it returns only 1 row for the product itself.

So using LIMIT and OFFSET should work in that case. Because the query would look like:


SELECT 

product.id,

product_description.name,

product_price.price 

FROM 

`product` `t` 

LEFT OUTER JOIN 

`product_description` `product_description` 

ON 

(`product_description`.`id_product`=`t`.`id`) AND (product_description.language_code="en") 

LEFT OUTER JOIN 

`product_price` `product_price` 

ON 

(`product_price`.`id_product`=`t`.`id`) AND (product_price.id_price_type="12") 

ORDER BY 

product_description.name ASC

LIMIT 10

But right now it still gives off an error and doesn’t do the joining as soon as I put a LIMIT.

This is fixed in latest trunk.