2

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

4
  • 1
    Related posts I've made with subqueries: 1, 2, 3, 4, 5, 6, 7, 8. I recommend JFactory::getApplication()->enqueueMessage($query->dump(), 'info'); to see your rendered sql (not in production code).
    – mickmackusa
    Feb 13, 2022 at 3:26
  • Specific to JOINing subqueries, here is an answer showing how to make three separate subquery JOINs: How to SET a user-defined MySQL variable and increment it to produce ranking values with JDatabaseQuery?
    – mickmackusa
    Feb 13, 2022 at 3:32
  • 1
    Can you please provide a db-fiddle.com demo with a small set of sample data for us? I am not convinced that you actually need to JOIN on a subquery.
    – mickmackusa
    Feb 13, 2022 at 7:35
  • @mickmackusa First time using db-fiddle.com. Hope the link I added above is what you've requested.
    – phunsoft
    Feb 13, 2022 at 9:50

2 Answers 2

3

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
2
  • Thanks. This is almost what my first non-subquery looked like. Just didn't think of counting on the unique id of the second table. From a performance point of view, which one is th better solution?
    – phunsoft
    Feb 13, 2022 at 10:01
  • I expect that avoiding the subquery will be more performant, but I'll make no guarantees. I'll leave it to you to test performance using your real project data and real table schema / column indexes. Makes sure to turn off query caching in PHPMyAdmin when you benchmark test. Testing performance of queries in mysql
    – mickmackusa
    Feb 13, 2022 at 10:16
4
  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'));
    
1
  • Thanks. There were still two errors in your answer. Firtsly, in the subquery, it has to say group($db->quotename('list_id'))instead of ...('b.list_id')). Secondly, in the query I had to name the count column, i.e. coalesce(b.member_cnt, 0) as member_cnt.
    – phunsoft
    Feb 13, 2022 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.