TransWikia.com

Delete query and left join with JDatabaseQuery doesn't work

Joomla Asked by user3533235 on January 6, 2022

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?

One 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:

Answered by mickmackusa on January 6, 2022

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP