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?

mickmackusa · Answer

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

Delete query and left join with JDatabaseQuery doesn't work

One Answer

Add your own answers!

Ask a Question