Mysqli query with regex to Joomla php syntax

Question

// CODE1 (Mysql (MariaDB 10.8.3) query):

SELECT s.`p_p`, COUNT(REGEXP_SUBSTR(r.`xml`, '[0-9]+'))
 FROM `#__s` AS s, `#__p` AS x, `#__v` AS r
 WHERE REGEXP_SUBSTR(r.`xml`, '[0-9]+') = [user_id] AND s.`user_id` = r.`user` AND x.`id` = s.`p_p`
 GROUP BY REGEXP_SUBSTR(r.`xml`, '[0-9]+'), s.`p_p`
 ORDER BY FIELD(x.`id`, 44, 14, 11, 10, 18, 4)

// Note1: works fine

// CODE2 (phpMyAdmin (5.2.0) query convertion to PHP (7.4.30)):
$query = "SELECT s.`p_p`, COUNT(REGEXP_SUBSTR(r.`xml`, \'[0-9]+\'))
 FROM `#__s` AS s, `#__p` AS x, `#__v` AS r
 WHERE REGEXP_SUBSTR(r.`xml`, \'[0-9]+\') = [user_id] AND s.`user_id` = r.`user` AND x.`id` = s.`p_p`
 GROUP BY REGEXP_SUBSTR(r.`xml`, \'[0-9]+\'), s.`p_p`
 ORDER BY FIELD(x.`id`, 44, 14, 11, 10, 18, 4);"; 
 
// Note2: Having phpMyAdmin to convert CODE1 to php it gives CODE2. Only \ was added and made it not to work. Taking \ out, works just fine as it is exactly the same as CODE1 which also works.

// CODE3 (PHP (7.4.30) query in Joomla (3.10.11) syntax):
$query = $db
    ->getQuery(true)
    ->select($db->quoteName('s.p_p'), $db->quoteName('COUNT(REGEXP_SUBSTR(r.`xml`, \'[0-9]+\'))'))
    ->from($db->quoteName('#__s', 's'), $db->quoteName('#__p', 'x'), $db->quoteName('#__v', 'r'))
    ->where($db->quoteName('(REGEXP_SUBSTR(r.`xml`, \'[0-9]+\'))') .' = [user_id]' AND $db->quoteName('s.user_id') .' = '. $db->quoteName('r.user') AND $db->quoteName('x.id') .' = '. $db->quoteName('s.p_p'))
    ->group($db->quoteName('(REGEXP_SUBSTR(r.`xml`, \'[0-9]+\'))'), $db->quoteName('s.p_p'));
    
// Note3: In an effort to convert CODE1 to Joomla syntax, I can't make it work (even without the \). Using try/catch throws an error in line ->group. I also need to add the ORDER BY FIELD(x.`id`, 44, 14, 11, 10, 18, 4) but I don't know how.

Answer 1

quoteName() is something that you call when you are quote-wrapping identifiers. (REGEXP_SUBSTR(r.xml, \'[0-9]+\')) is not a table name or column name so it makes no sense to try to wrap it in quotes as an identifier.

In fact, I don't see any reason to use ANY of those quoteName() method calls in your script. It only bloats the syntax, and makes your code harder to "eyeball". There isn't any security/stability improvement for calling quoteName() either.

JFactory::getApplication()->enqueueMessage($query->dump(), 'notice'); can prove very helpful as a sanity check when queries are failing.

Ultimately, I'm saying, remove all of those quoteName() calls and those manually written backticks, then try your query again.

P.S. Since where()'s default glue is AND, I'd personally prefer to feed it an array of rules:

->where([
    "REGEXP_SUBSTR(r.xml, '[0-9]+') = [user_id]",
    "s.user_id = r.user",
    "x.id = s.p_p"
])

Err, wait. I don't like old-skool comma joins -- they also trick the developer eye. I recommend using explicit INNER JOINs with ON clauses for clarity. This means you will only have one rule in your WHERE clause (so no array is needed).