SQL syntax error in query with subquery using JDatabase

Question

I'm coding my first Joomla! 4 component. There are two tables for the component: pfx_list, and pfx_member_to_list. Table pfx_list hash primary key id, and table pfx_member_to_list has foreign key list_id pointing to table pfx_list.

I need a list of all rows of pfx_list joined with the number of matches in pfx_member_to_list, which can be none, one, or many.

In the first version, there was a simple LEFT JOIN, but this did (of course) not return rows with zero entries in the right table. But, this version was running fine (except for the missing rows), so I know the component code is basically ok.

I then changed the query to a LEFT JOIN on a subselect, so that rows with no matches in the right table will also be returned. This is the query, and when run through php_myadmin, it returns the desired result:

SELECT a.id, a.list_name, coalesce( member_cnt, 0 )
FROM      pfx_list as a
LEFT JOIN ( select list_id, count(*) as member_cnt 
            from pfx_member_to_list
            group by list_id
            ) b
        ON a.id = b.list_id
GROUP BY a.list_name
ORDER BY a.list_name;   

I'm stuck with translating this to a JDataBase query object. Here is the current code:

$db       = $this->getDbo();
$query    = $db->getQuery(true);
$subquery = $db->getQuery(true);

$subquery->select(['list_id', 'count(*) as member_cnt'])
         ->from($db->quotename('#__member_to_list', 'b'))
         ->group($db->quotename('b.list_id'));

$query->select(['a.id', 'a.list_name', 'a.nz_list_id', 'a.nz_list_key', 'coalesce(member_cnt, 0)'])
      ->from($db->quoteName('#__list', 'a'))
      ->join('LEFT', $subquery . ' ON ' . $db->quoteName('a.id') . ' = ' . $db->quoteName('b.list_id'))
      ->order($db->quoteName('a.list_name') . ' ASC')
      ->group($db->quotename('a.list_name'));

When the query is run in Joomla! via admin menu entry, I'm getting error:

An error has occurred.
500 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version 
for the right syntax to use near 'SELECT list_id,count(*) as member_cnt FROM 
`pfx_member_to_...' at line 4 You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near 'SELECT list_id,count(*) as 
member_cnt FROM ``pfx_member_to_...' at line 4 

I've tried removing quoteName(), and some more changes, but cannot seem to find out what is wrong.

Edit 1: Here is a working sample on db-fiddle.com

Answer 1

You can actual fetch the desired result without implementing a JOIN on a subquery.

Use a LEFT JOIN if you want the possibility of zero counts in the results. Use a regular JOIN (INNER JOIN) if you want to exclude zero counts from your result set.

If your name column is guaranteed to be unique, you can GROUP BY that column. If you might possibly have shared name values in the first table, then you'll need to GROUP BY something that will be unique -- like the id column for absolute stability -- as well as the name column that you want in your SELECT clause.

SQL: (Generalised DB-Fiddle)

SELECT a.id, a.list_name, COUNT(b.id) 
FROM pfx_list AS a
LEFT JOIN pfx_member_to_list AS b ON a.id = b.list_id
GROUP BY a.id, a.list_name
ORDER BY a.list_name

Joomla Query Builder Syntax:

$query->select("a.id, a.list_name, COUNT(b.id) AS cnt")
   ->from("#__list AS a"))
   ->join("LEFT", "#__member_to_list AS b ON a.id = b.list_id")
   ->group("a.id, a.list_name")
   ->order("a.list_name");

---

EDIT: With your newly supplied db-fiddle, I have adjust the SELECT and GROUP BY clauses to include your extra columns. No subquery is needed. (DB-Fiddle Demo)

SELECT a.id,
       a.list_name,
       a.nz_list_id,
       a.nz_list_key,
       COUNT(b.member_id) cnt
FROM lists AS a
LEFT JOIN member_to_list AS b ON a.id = b.list_id
GROUP BY a.id,
         a.list_name,
         a.nz_list_id,
         a.nz_list_key
ORDER BY a.list_name

Answer 2

1. to spot a sql syntax error it's useful to view the resultant sql eg.

    echo $query->__toString();
   

2. you've put the alias of the subquery 'b' as the alias of the table inside the subquery. But the main reason is that the subquery needs parentheses in the join clause.

    $subquery->select(['list_id', 'count(*) as member_cnt'])
         ->from($db->quotename('#__member_to_list'))
         ->group($db->quotename('b.list_id'));
   
    $query->select(['a.id', 'a.list_name', 'a.nz_list_id', 'a.nz_list_key', 'coalesce(member_cnt, 0)'])
      ->from($db->quoteName('#__list', 'a'))
      ->join('LEFT', '(' . $subquery . ') AS b ON ' . $db->quoteName('a.id') . ' = ' . $db->quoteName('b.list_id'))
      ->order($db->quoteName('a.list_name') . ' ASC')
      ->group($db->quotename('a.list_name'));