After half a day of headaches trying to figure out a double INNER JOIN, I finally got it right:
SELECT product.id as product_id, count(purchase_vaucher.object_id) as purchased
FROM purchase_vaucher
INNER JOIN purchase on purchase.id = purchase_vaucher.object_id
INNER JOIN product on purchase.product_id = product.id
WHERE purchase.is_paid=1
GROUP BY product.id
ORDER BY purchased_amount desc;
What is this about: basically, three tables: product, purchase, purchase_vaucher (yeah I know it's "voucher", but I didn't invent the schema). Basically, purchase contains purchases made and a back-link to a product. purchase_vaucher contains info about vouchers and a back-link to a purchase. What I needed from this query: For all purchases who have is_paid=1, count all vouchers of all the purchases with the same product id and group them by the product id.
What I need is: the above query translated as a Propel Criteria API calls.
I am starting to work on this right away, but I will be happy if I could save some time.
Thanks.
Curiously enough, it turned out to be very easy:
$purchased_amount_column = 'purchased_amount';
$c = new Criteria();
self::addSelectColumns($c);
$c->addAsColumn($purchased_amount_column, 'COUNT(' . PurchaseVaucherPeer::OBJECT_ID . ')');
$c->addGroupByColumn(ProductPeer::ID);
$c->addDescendingOrderByColumn($purchased_amount_column);
$c->add(PurchasePeer::IS_PAID, 1, Criteria::EQUAL);
$c->addJoin(PurchasePeer::ID, PurchaseVaucherPeer::OBJECT_ID, Criteria::INNER_JOIN);
$c->addJoin(PurchasePeer::PRODUCT_ID, ProductPeer::ID, Criteria::INNER_JOIN);
Had a trouble with the pager, but it took me 5 mins to realize that the way to make it count the records of such non-trivial Criteria was simply this:
$pager->setPeerCountMethod('customCount');
...
// customCount:
return YourPeer::doCount($yourCriteria);
That was it really.