JDatabase JOIN on multiple values

Question

What is the JDatabase syntax for a JOIN statement that joins two tables on a variable that is either equal to x or is null? Eg it joins on multiple values.

I have found some examples that show me you can do this in normal SQL but I don't know how that translates into JDatabase syntax.

My query currently works when the variable (a.animal) equals b.id, but I need it to also join when the variable (a.animal) is empty.

$query1 = $db->getQuery(true)
    ->select ($db->quoteName(array('a.date','a.reminder','a.type','a.animal','a.mob')))
    ->select ($db->quoteName('b.nameandid') .'AS'. $db->quote('animalname'))    
    ->from ($db->quoteName('app_reminders', 'a'))    
    ->join('INNER', $db->quoteName('app_animal', 'b') . 'ON (' .$db->quoteName('a.animal'). 'LIKE' .$db->quoteName('b.id').')')
    ->where ($db->quoteName('a.date') . '>=' . 'NOW()', 'AND')    
    ->where ($db->quoteName('a.user_id') . 'LIKE' . $db->quote($userid));

Answer 1

In your ON statement you should be able to add multiple conditions combined by AND and OR. So something like this should do the trick:

->join('INNER', $db->quoteName('app_animal', 'b') . 'ON (' .$db->quoteName('a.animal'). 'LIKE' .$db->quoteName('b.id').' OR ' . $db->quoteName('a.animal') . ' IS NULL)')

Answer 2

I have a few concerns and refinements, but I'll skip straight to the code first and explain after.

You want to generate a query that renders like this:

SELECT a.date, a.reminder, a.type, a.animal, a.mob, b.nameandid AS 'animalname'
FROM app_reminders a
LEFT JOIN app_animal b ON a.animal = b.id
WHERE a.date >= NOW() AND a.user_id = 3

Notice that my JOIN and the JOIN in your linked StackOverflow page's question are using LEFT JOIN. The OP on that question is wanting only rows with values in all joined tables. The answer uses JOIN (regular join aka INNER JOIN) this ignores rows that have null for the joining value.

You want something different. You want the rows from the first table regardless of if the second table contains a qualifying value to join on the row. For this, you want LEFT JOIN. Here is an SQLFiddle Demo if you want to play around and compare the results of the joins. (If this isn't the action you are seeking, you should be able to use this link to express what isn't quite right.) With LEFT JOIN you won't need to add the extra condition to check IS NULL.

As asides...

1. You are using the LIKE operator but without % or _ wildcards, so there is no reason to write LIKE. Just simplify to =.
2. While date and type are mysql KEYWORDS, they are not RESERVED KEYWORDS so they don't NEED to be wrapped in backticks. This is also true of all of your other columns and the tablenames. You are welcome to use qn()/quoteName() on all of these entities, but it will only add bloat to your query syntax and code.
3. So that your query is more human-readable, include a single space on both side of your comparison operators. (e.g. 'LIKE' is better as ' LIKE ')
4. As a security precaution, cast your $userid variable as an integer (int) when applying it to your query. Integer values do not need to be quoted.
5. As a matter of personal preference with the intention to improve readability/code-comprehension, I try to avoid using duplicate method calls as a concatenation technique for a single clause. My suggested Joomla snippet will only use one select() and one where() call. (When joining multiple tables, the presence of multiple join() calls is logical/intuitive.)

Joomla Syntax (no quoting methods called):

$query = $db->getQuery(true)
            ->select("a.date, a.reminder, a.type, a.animal, a.mob, b.nameandid AS 'animalname'")
            ->from("app_reminders a")
            ->joinLeft("app_animal b ON a.animal = b.id")
            ->where("a.date >= NOW() AND a.user_id = " . (int)$userid);

p.s. If you actually want to use LIKE with wildcard character(s), this post will show you the secure way -- sourced from the Joomla documentation.