Propel 1.6 Greatest n per group

Question

I need to build propel query that can get the latest purchase of client

Please use these table/column names in your answer:

customer: id, name
purchase: id, customer_id, date

from this question here SQL join: selecting the last records in a one-to-many relationship

I need to build propel query based one of these queries:

SELECT c.*, p1.*
FROM customer c
JOIN purchase p1 ON (c.id = p1.customer_id)
LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND 
    (p1.date < p2.date OR p1.date = p2.date AND p1.id < p2.id))
WHERE p2.id IS NULL;

or

SELECT  c.*, p.*
FROM    customer c INNER JOIN
        (
            SELECT  customer_id,
                    MAX(date) MaxDate
            FROM    purchase
            GROUP BY customer_id
        ) MaxDates ON c.id = MaxDates.customer_id INNER JOIN
        purchase p ON   MaxDates.customer_id = p.customer_id
                    AND MaxDates.MaxDate = p.date

Yes. But nothing for now. Found this stackoverflow.com/questions/2111384/… but I can't translate it to propel Query — petkopara

Jordan Kasper Jordan Kasper · Accepted Answer · 2013-06-03T16:18:30

Well, given your question (finding latest purchase for a given client) then you just want to do a search on the purchase table ordering by date and limiting to one:

$lastPurchase = PurchaseQuery::create()
  ->filterByCustomerId($customerId)
  ->orderByDate(Criteria::DESC)
  ->limit(1)
  ->find();

The answer you link to, however, is finding all customers AND their latest purchase. In that case I would probably use a custom SQL query (although you technically can do this with Propel functions, it would be more difficult and would not get you very much since you won't be hydrating objects (at least, not easily).

UPDATE Here's some information on doing a manual query. Note that when doing so, the data returned will NOT be Propel objects, but you can do on-demand hydration if needed.

Example:

$sql = "SELECT c.*, p1.*
        FROM customer c
        JOIN purchase p1 ON (c.id = p1.customer_id)
        LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND 
            (p1.date < p2.date OR p1.date = p2.date AND p1.id < p2.id))
        WHERE p2.id IS NULL;";
$conn = Propel::getConnection(CustomerPeer::DATABASE_NAME);
$stmt = $conn->prepare($query);
if ($stmt->execute()) {
    while($result = $stmt->fetch()) {
        $someField = $result['...'];
    }
}

Propel 1.6 Greatest n per group

1 Answers