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:
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).
(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.
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'),
);
}
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.