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 JOINjt5il_users
ASju
ON 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.
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP