is it possible to create mysql query (Sort by column considering foreign columns)

Question

For example I have 2 tables:

product:
id: integer
price: integer

variation:
id: integer
product_id: integer
price integer

Some products have variations, some products don't have variations. I want to get all products, sorted by price. And if product has variation, this product must be sorted by first variation price, not its own price.

And is it possible to make such Propel Criteria or Query ?

Answer 1

SELECT
*
FROM
product p
LEFT JOIN variation v ON p.id = v.product_id
ORDER BY COALESCE(v.price, p.price)

COALESCE() returns the first of its parameters which isn't NULL. The LEFT JOIN returns everything from product table, if there's no match on variation table, there's NULL.