Join with an order diferent to the group by field

Yii 1.1 General Discussion
1

I have three tables which defines a ternary relation.

  • tbl_product: product_id (primary key), name (varchar)

  • tbl_image: image_id (primary key), rate (int), path(varchar)

  • tbl_image_product: product_id, image_id

A product (tbl_product) can have multiple images(tbl_image), and an image(tbl_image) can below to several products(tbl_product). So the tbl_image_product table manages the ternary relation between them.

My problem comes when I want to retrieve the last six products (biggers product_id) with the image with low rate. The result of the query shows me the join with the small image_id.

The query I do is the next:

SELECT *

FROM tbl_product t

LEFT OUTER JOIN tbl_image_to_product tblImages_tblImages ON (t.product_id=tblImages_tblImages.product_id)

LEFT OUTER JOIN tbl_image tblImages ON (tblImages.image_id=tblImages_tblImages.image_id)

GROUP BY t.product_id

ORDER BY t.product_id DESC, tblImages.rate ASC

LIMIT 6

Unfortunately I don’t get the low rate for the join I do. I would be pleased if someone could help me with the correct query or the correct CDbCriteria.

Thanks in advance:

Kike