Delete query and left join with JDatabaseQuery doesn't work

Question

I'm trying to convert a delete query with left join to use JDatabaseQuery, but I am encountering an issue in the query. The query that I'm trying to convert and which works is the following :

$db->setQuery("DELETE u FROM #__mycomponent_users AS u
               LEFT JOIN #__users AS ju ON u.userid=ju.id
               WHERE ju.username IS NULL"); 

I have converted it to JDatabaseQuery, with the following code:

$db->getQuery(true)
   ->delete($db->quoteName('#__mycomponent_users', 'u'))
   ->leftJoin($db->quoteName('#__users', 'ju') . ' ON u.userid=ju.id')
   ->where('ju.username IS NULL');
$db->setQuery($query);

In this, it returns the following error :

Syntax error near 'AS u LEFT JOIN jt5il_users AS ju ON u.userid=ju.id WHERE ju.username IS NUL'

So how can I make this query work by using JDatabaseQuery?

Answer 1

It seems that if you want to delete rows from one or more tables in a DELETE query containing a JOIN, you will need to specify the table(s) to delete between DELETE and FROM.

https://stackoverflow.com/q/2763206/2943403

Joomla's delete() method does not provide the necessary output - as you have discovered.

If you wish to patch the output from the query methods, then you can replace your:

$db->setQuery($query);

with:

$db->setQuery(preg_replace('~DELETE \K~', 'u ', $query, 1));

The \K metacharacter in the pattern says "forget/release any previously matched characters". By using this technique, no characters are removed -- only u then a space are added.

Or because you only have one DELETE in your query, you can more simply adjust the sql string with:

$db->setQuery(str_replace('DELETE', 'DELETE u', $query));

This issue seems like reasonable grounds for an adjustment in core Joomla's delete() method so that a hack isn't required. However, maybe this is too uncommon of a fringe case to bother with.

If the string replacement hack is too dirty for your taste, there are a few ways to perform this kind of mysql operation. You might prefer to build a subquery to delete against. (An example of Joomla subquery implementation.)

See these other sql options:

• https://networking.ringofsaturn.com/SQL/SQLDeleteRowsNotInAnotherTable.php
• Delete sql rows where IDs do not have a match from another table