JDatabase : Subquery in join left, then where clause - Joomla! Forum - community, help and support

summary: when using subrequest join statement, "where" clause after join not taken in account.

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

the query must retrieves items ("installations") of user, each item, count how many other users can access it.

here working pure mysql working code. it:

code: select all

    select distinct installations. * , users.ls_role, t.team
    j_ls_installations installations
    left join j_users_ls_installations lsusers on installations.ls_id = lsusers.ls_id
    left join (
        select  `ls_id` , count( * ) team
        from  `j_users_ls_installations`
        group by  `ls_id`
    ) t on installations.ls_id = t.ls_id
    lsusers.ls_user_id =269


here jdatabase version of it:

code: select all

        $query = $db->getquery(true);
        $subquery = $db->getquery(true);
       
        $subquery->select($db->quotename('ls_id').', count(*) team')
            ->from($db->quotename('#__users_ls_installations'))
            ->group($db->quote('ls_id'));

        // create base select statement.
        $query
            ->select(
                'distinct installations.*,  lsusers.ls_role, t.team'
                )
            ->from($db->quotename('#__ls_installations').' installations')
            ->leftjoin($db->quotename('#__users_ls_installations').' lsusers on installations.ls_id=lsusers.ls_id')
            ->leftjoin( '('. $subquery .') t on installations.ls_id = t.ls_id')
            ->where($db->quotename('lsusers.ls_user_id') . ' = ' . $ouser->id);


here generated code:

code: select all

  select distinct installations.*,  lsusers.ls_role, t.team
  `j_ls_installations` installations
  left join `j_users_ls_installations` lsusers
     on installations.ls_id=lsusers.ls_id
  left join ( select `ls_id`, count(*) team
  `jkg9e_users_ls_installations`
  group 'ls_id') t
  on installations.ls_id = t.ls_id


as can see, generate method don't have last "where" statement, stop after joining subrequest.


(let me know if want me share component, tables , , test it. )

ok, adding alias in subquery solved problem:

code: select all

        $query = $db->getquery(true);
        $subquery = $db->getquery(true);
       
        $subquery->select($db->quotename('ls_id').', count(*) team')
            ->from($db->quotename('#__users_ls_installations'). ' b')
            ->group($db->quote('ls_id'));

        // create base select statement.
        $query
            ->select(
                'distinct installations.*,  lsusers.ls_role, t.team'
                )
            ->from($db->quotename('#__ls_installations').' installations')
            ->leftjoin($db->quotename('#__users_ls_installations').' lsusers on installations.ls_id=lsusers.ls_id')
            ->leftjoin( '('. $subquery .') t on installations.ls_id = t.ls_id')
            ->where($db->quotename('lsusers.ls_user_id') . ' = ' . $ouser->id);


i added 'as b':

code: select all

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