select data from several tables in one query - how to control JOIN?

Question

I'm building on my question here: Build a query with JOIN, but filter and sort that joined table first Now the goal has become more complicated and I can't solve it.

Here's the problem:
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     color
1   1         BMW      10,000    red
2   1         Audi     8,000     blue
3   1         BMW      6,000     white
4   2         BMW      21,000    silver
5   2         Renault  9,500     black
6   3         Seat     4,200     green

Now, I'd like to build a query returning persons, their car, its price and its color.
Here's the tricky part: If they have several cars, the query has to select the BMW with the lowest price:

Peter   BMW    6,000    white
Paul    BMW    21,000   silver
Mary    Seat   4,200    green

I tried it with...

SELECT p.name, c.make, MIN(c.price), c.color
FROM persons AS p 
LEFT JOIN cars AS c ON c.personid = p.id
GROUP BY p.id

but the results are wrong:

Peter    BMW    6,000    red      (color change!)
Paul     BMW    9,500    silver   (price drop!)
Mary     Seat   4,200    green    (well...)

see the Fiddle: http://sqlfiddle.com/#!9/d93f41/2/0

How can I control which car gets linked to the person who has more than one car?

Answer 1

Joomla doesn't handle subqueries very well since there is no real $query->subQuery method. In this instance it is easiest to format the query as a standard MySQL query and let Joomla process the results. The query below will return the proper result for Peter and Paul having BMWs, with lowest price and correct colors.

$db       = JFactory::getDbo();
$query    = $db->getQuery(true);
$sql = "SELECT p.name, c.make, c.price, c.color FROM #__persons AS p LEFT JOIN #__cars AS c ON c.personid = p.id WHERE c.make = 'BMW' AND c.price = (SELECT MIN(price) FROM #__cars WHERE make = 'BMW' AND personid = p.id)";
$db->setQuery($sql);
$rows = $db->loadObjectList();

Updated SQLFiddle

Answer 2

Terry Carter's solution does not provide the desired functionality / result set AND it is conveying the false notion that Joomla "doesn't handle subqueries well". While it is true that there is no method called subQuery(), Joomla does provide a clean syntax for nesting queries inside of other queries.

The most glaring issue with Terry's query is that it disregards all persons that do not have a BMW for sale. This is not the desired logic described in the question. The expected result set should include a row for Mary which contains the lowest-priced non-BMW item.

Furthermore, my solution implements an INNER JOIN instead of a LEFT JOIN so that any names that exist in persons which do not have a row in cars will be excluded from the result set. (If the opposite logic is desired, then of course use a LEFT JOIN.) In other words, a LEFT JOIN will potentially generate rows with NULL values in make, price, and color.

Raw SQL: (DB-Fiddle.com Demo)

SELECT name, make, price, color
FROM persons p
INNER JOIN cars ON p.id = personid
WHERE price = IFNULL(
                  (SELECT MIN(price) FROM cars WHERE personid = p.id AND make = 'BMW'),
                  (SELECT MIN(price) FROM cars WHERE personid = p.id)
              )

Things to notice:

• I did not add table aliases in the SELECT clause because there is no ambiguity to contend with. In other words, because none of the nominated columns exist in both table, MySQL knows exactly what is intended and so no alarm bells go off.
• The same logic is applied to the first WHERE clause -- I could have written c.price, but MySQL already knows which table to draw it from.
• Because cars is never used to identify any columns in the query, I have elected to omit the alias c.
• If you DO decide to declare the table alias c and add c. to each of its columns in the query, take special care to NOT write c.make = 'BMW' in the first subquery because that will alter the logic and damage the results -- make in this context needs to refer to the make value within the subquery.
• IFNULL() is a wise choice for the query because effectively the second subquery is only executed if the first subquery returns no qualifying rows. This condition block ensures that you get the lowest BMW value unless there is no BMW for the respective person in which case, you fallback to the lowest priced "anything" for that person.

Now to use Joomla's query building methods to create the query... As with the great majority of my posts in this community, I'll package my snippet with a query dump and some diagnostic niceties to help researchers to understand and debug their own projects. Please note, that you should never display generated queries nor mysql errors to the public because this can lead to naughty people refining malicious attacks on your system.

try {
    $db = JFactory::getDbo();

    $bmw_subquery = $db->getQuery(true)
                       ->select("MIN(price)")
                       ->from("cars")
                       ->where("personid = p.id")
                       ->where("make = " . $db->q("BMW"));

    $all_subquery = $db->getQuery(true)
                       ->select("MIN(price)")
                       ->from("cars")
                       ->where("personid = p.id");

    $query = $db->getQuery(true)
                ->select("name, make, price, color")
                ->from("persons p")
                ->innerJoin("cars ON p.id = personid")
                ->where("price = IFNULL(($bmw_subquery), ($all_subquery))");

    $db->setQuery($query);
    JFactory::getApplication()->enqueueMessage($query->dump(), 'info');
    if (!$result = $db->loadAssocList()) {
        echo "<p>No Rows Found</p>";
    } else {
        echo "<pre>";
        var_export($result);
        echo "</pre>";
    }
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');  // don't show $e->getMessage() to public
}

This will serve up the rendered query described above and the desired result set:

array (
  0 => 
  array (
    'name' => 'Peter',
    'make' => 'BMW',
    'price' => '6000',
    'color' => 'white',
  ),
  1 => 
  array (
    'name' => 'Paul',
    'make' => 'BMW',
    'price' => '21000',
    'color' => 'silver',
  ),
  2 => 
  array (
    'name' => 'Mary',
    'make' => 'Seat',
    'price' => '4200',
    'color' => 'green',
  ),
)

Some parting words:

1. I would have expected the tables in the question to have names Joomla's random prefix characters. Using these prefixes is best practice and adjusting the Joomla query builder syntax only means prepending #__ like #__cars and #__persons which would associate with tables that could be named abcde_cars and abcde_persons.

2. If anyone is hell-bent on writing terse code, the $bmw_subquery subquery can be generated by cloning the $all_subquery subquery then chaining the additional where clause condition.

   $all_subquery = $db->getQuery(true)
                      ->select("MIN(price)")
                      ->from("cars")
                      ->where("personid = p.id");
   
   $bmw_subquery = (clone($all_subquery))->where("make = " . $db->q("BMW"));
   //              ^--------------------^-- parentheses are essential
   
   $query = $db->getQuery(true)
               ->select("name, make, price, color")
               ->from("persons p")
               ->innerJoin("cars ON p.id = personid")
               ->where("price = IFNULL(($bmw_subquery), ($all_subquery))");
   

   *The parentheses are required so that you "clone then chain" instead of "chaining to the original then cloning". If you remove the parentheses, you will find that both subqueries will be identical and both will include AND make = 'BMW'.

Answer 3

Try something like this:

SELECT p.name, c.make, MIN(c.price), c.color
FROM persons AS p 
LEFT JOIN cars AS c ON c.personid = p.id
GROUP BY p.id
HAVING min(c.price)