I'm building a custom component in Joomla 3.x. I have two tables in my model:
table persons id name 1 Peter 2 Paul 3 Mary table cars id personid make price 1 1 BMW 10,000 2 1 Audi 8,000 3 1 BMW 6,000 4 2 BMW 21,000
Now, I'd like to build a query returning persons and their least expensive BMW:
Peter BMW 6,000 Paul BMW 21,000
Here's the MySQL statement I can come up with:
SELECT p.name, c.make, c.price FROM persons AS p LEFT JOIN cars AS c ON c.personid = p.id
Translated to Joomla:
$db = $this->getDbo(); $query = $db->getQuery(true); $query->select('p.name, c.make, c.price'); $query->from('#__persons AS p'); $query->join('LEFT', '#__cars AS c ON c.personid = p.id');
But obviously, I first - before
JOINing - need to filter out Peter's Audi (
WHERE c.make = 'BMW') and sort the remaining BMWs by their price (
ORDER BY c.price ASC).
But how? I'm stuck on this, and really need some hints.