JDatabase : Subquery in join left, then where clause

[LouisSeb]

Summary: when using a subrequest for a join statement, the "where" clause after the join are not taken in account.

Two table:
- j_ls_installations (list of items)
- j_users_ls_installations (relation between item and users, based on ls_id )

The query must retrieves all the items ("installations") of a user, then for each item, count how many other users can access to it.

Here a working pure MySQL working code. It:

Code: Select all

    SELECT DISTINCT installations. * , users.ls_role, t.team
    FROM j_ls_installations AS installations
    LEFT JOIN j_users_ls_installations AS lsusers ON installations.ls_id = lsusers.ls_id
    LEFT JOIN (
        SELECT  `ls_id` , COUNT( * ) as team
        FROM  `j_users_ls_installations`
        GROUP BY  `ls_id`
    ) AS t ON installations.ls_id = t.ls_id
    WHERE lsusers.ls_user_id =269

Here the JDatabase version of it:

Code: Select all

        $query = $db->getQuery(true);
        $subQuery = $db->getQuery(true);
        
        $subQuery->select($db->quoteName('ls_id').', COUNT(*) as team')
            ->from($db->quoteName('#__users_ls_installations'))
            ->group($db->quote('ls_id'));

        // Create the base select statement.
        $query
            ->select(
                'DISTINCT installations.*,  lsusers.ls_role, t.team'
                )
            ->from($db->quoteName('#__ls_installations').' as installations')
            ->leftJoin($db->quoteName('#__users_ls_installations').' as lsusers on installations.ls_id=lsusers.ls_id')
            ->leftJoin( '('. $subQuery .') as t on installations.ls_id = t.ls_id')
            ->where($db->quoteName('lsusers.ls_user_id') . ' = ' . $oUser->id);

Here the generated code:

Code: Select all

  SELECT DISTINCT installations.*,  lsusers.ls_role, t.team 
  FROM `j_ls_installations` as installations 
  LEFT JOIN `j_users_ls_installations` as lsusers 
     on installations.ls_id=lsusers.ls_id 
  LEFT JOIN ( SELECT `ls_id`, COUNT(*) as team 
  FROM `jkg9e_users_ls_installations` 
  GROUP BY 'ls_id') as t 
  on installations.ls_id = t.ls_id

As you can see, the generate method don't have the last "where" statement, it just stop after joining the subrequest.


(let me know if you want me to share the component, to get the tables and everything and test it. )

[LouisSeb]

Ok, adding an alias in the subquery solved the problem:

Code: Select all

        $query = $db->getQuery(true);
        $subQuery = $db->getQuery(true);
        
        $subQuery->select($db->quoteName('ls_id').', COUNT(*) as team')
            ->from($db->quoteName('#__users_ls_installations'). ' as b')
            ->group($db->quote('ls_id'));

        // Create the base select statement.
        $query
            ->select(
                'DISTINCT installations.*,  lsusers.ls_role, t.team'
                )
            ->from($db->quoteName('#__ls_installations').' as installations')
            ->leftJoin($db->quoteName('#__users_ls_installations').' as lsusers on installations.ls_id=lsusers.ls_id')
            ->leftJoin( '('. $subQuery .') as t on installations.ls_id = t.ls_id')
            ->where($db->quoteName('lsusers.ls_user_id') . ' = ' . $oUser->id);

I just added 'as b':

Code: Select all

            ->from($db->quoteName('#__users_ls_installations'). ' as b')