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
JFactory::getApplication()->enqueueMessage($query->dump(), 'info');to see your rendered sql (not in production code).