1

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?

0

It seems that if you want to delete 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:

  • I have tried this code snippet : $query = $db->getQuery(true) ->delete($db->quoteName('#__mycomponent_users')) ->leftJoin($db->quoteName('#__users', 'ju') . ' ON userid = ju.id') ->where('ju.username IS NULL'); $db->setQuery($query); I still obtain the error : syntax error near 'LEFT JOIN jt5il_users AS ju ON userid = ju.id WHERE ju.username IS NULL' – user3533235 Jun 2 at 13:06
  • The dump give : DELETE FROM `jt5il_mycomponent_users` LEFT JOIN `jt5il_users` AS `ju` ON userid = ju.id WHERE ju.username IS NULL – user3533235 Jun 2 at 13:13
  • In Phpmyadmin it throws the same error, for you second code snippnet there are the same error, the dump give : DELETE FROM `jt5il_mycomponent_users` LEFT JOIN `jt5il_users` AS `ju` ON `jt5il_mycomponent_users`.`userid` = ju.id WHERE ju.username IS NULL Same issue in phpmyadmin – user3533235 Jun 2 at 13:16
  • Literally the table is nammed #__mycomponent_users – user3533235 Jun 2 at 13:24
  • I have done a screenshot of phpmyadmin : imgur.com/6A7P8zI – user3533235 Jun 2 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.