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
juON u.userid=ju.id WHERE ju.username IS NUL’
So how can I make this query work by using JDatabaseQuery?
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.
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(preg_replace('~DELETE K~', 'u ', $query, 1));
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
2 Asked on September 14, 2020 by ruchika
1 Asked on August 18, 2020 by grant-g
1 Asked on August 17, 2020 by aymeric-keyewa
1 Asked on August 6, 2020 by firefighter
0 Asked on July 21, 2020 by thiago-guimares
Get help from others!
© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP