Build a query with JOIN, but filter and sort that joined table first

Question

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.

Answer 1

If all you are wanting to do is add the WHERE clause that is no different than any other DB Query for Joomla. You will need to group things by your user and query the MIN price. The example below should work for you, I included an SQL Fiddle link below as well.

$db    = $this->getDbo();
$query = $db->getQuery(true);
$query->select('p.name, c.make, MIN(c.price)');
$query->from('#__persons AS p');
$query->join('LEFT', '#__cars AS c ON c.personid = p.id');
$query->where("c.make = 'BMW'");
$query->group('p.id');

I edited your query to include the MIN(c.price). Notice I have grouped the statement by p.id instead of ordering by c.price ASC.

You can see the results of this query at this SQLFiddle