0

I have written a SQL query which works fine and output provides all the data that I need:

Please see this screenshot:

result

This is the SQL code:

SELECT 
    P.name, C.project_id, C.choice_id, C.time_stamp, P.id, 
    P.description, T.type, P.activated
FROM
    (SELECT P.*
     FROM aqoru_pt_project P
     JOIN
         (SELECT id, MAX(creation_date) AS latestUpdate
          FROM aqoru_pt_project
          GROUP BY id) t ON t.id = P.id AND t.latestUpdate = P.creation_date) P
JOIN
    (SELECT C.*
     FROM aqoru_pt_project_choice C
     JOIN
         (SELECT project_id, MAX(time_stamp) AS latestDate
          FROM aqoru_pt_project_choice
          GROUP BY project_id) t ON t.project_id = C.project_id AND t.latestDate = C.time_stamp) C ON P.id = C.project_id
LEFT JOIN 
    aqoru_community_groups_members CGM ON P.group_id = CGM.groupid
INNER JOIN
    aqoru_pt_project_type T ON P.type_id = T.id
WHERE
    P.user_id = 569 OR CGM.memberid = 569
    AND CGM.permissions = 1
    AND P.deactivated != 1
GROUP BY 
    P.id
ORDER BY 
    P.id;

Now I need to push it into php format I was trying without great success as I am struggling mainly with the part where I try to put JOIN in SELECT on the very beginning of the code.

Definitely the part after ON P.id = C.project_id works fine. The problem occurs when I try to add max value from other table.

This is the code I wrote, but it does not work:

public function getProjects($userID)
    {
        $db = JFactory::getDbo();
        $query = $db->getQuery(true);

        $columns = array('P.id', 'P.name', 'P.description', 'T.type', 'P.activated');
        $query
            ->select(array('P.id', 'P.name', 'P.description', 'T.type', 'P.activated', 'max(C.time_stamp) as max'))
            ->from($db->quoteName(
                ->SELECT ('P.*')
                ->FROM ($db->quoteName('aqoru_pt_project', 'P')
                ->JOIN($db->quoteName(
                    ->SELECT ('id', 'MAX(creation_date) AS latestUpdate')
                    ->FROM ($db->quoteName('aqoru_pt_project')
                    ->group($db->quoteName('id')) 't' .  ' ON t.id = P.id AND t.latestUpdate = P.creation_date') P
                ->JOIN(
                  ->SELECT ('C.*')
                  ->FROM ($db->quoteName('aqoru_pt_project_choice', 'C')
                    ->JOIN($db->quoteName(
                    ->SELECT ('project_id', 'MAX(time_stamp) AS latestDate')
                    ->FROM ($db->quoteName('aqoru_pt_project_choice'))
                    ->group($db->quoteName('id')) 't' .  ' ON t.project_id = P.project_id AND t.latestUpdate = P.time_stamp') 'C' . 'ON P.id = C.project_id'

            ))
            ->leftjoin($db->quoteName('#__community_groups_members', 'CGM') . ' ON P.group_id = CGM.groupid')
            ->innerjoin($db->quoteName('#__pt_project_type', 'T') . ' ON P.type_id = T.id')
            ->where(
                $db->quoteName('P.user_id') . ' = ' . $db->quote($userID),
            )
            ->orwhere(array(
                $db->quoteName('CGM.memberid') . ' = ' . $db->quote($userID),
                $db->quoteName('CGM.permissions') . ' = 1'
            ))
            ->andwhere($db->quoteName('P.deactivated') . ' != 1')
            ->group($db->quoteName('P.id'))
            ->order('P.id DESC');
        /*
         * Note: for some reason the queries return duplicates on the production site not on my local site
         * So we must group by projectID to avoid duplicates. I am not aware what is causing this duplication
         */

        return $db->setQuery($query)->loadObjectList();
    }

Problem is definitely when trying to push join to get my MAX in the very beginning of the query, but I have no idea how to proceed with it.

3
  • You have a PHP syntax error on line with ->SELECT ('P.*').
    – Sharky
    Jul 19 at 16:23
  • Joomla's query building helper methods do no respect code tabbing to create subqueries. As I said on Stack Overflow, you need to create rendered $subquerys in advance. joomla.stackexchange.com/search?q=%24subquery Did you read the pages that I linked before? @Marcin
    – mickmackusa
    Jul 19 at 20:40
  • I am going to close this for now because I think we have enough content to demonstrate how to write nested queries. If you still need support implementing the technique after reading the linked pages, please edit your question with your best attempt and we will try to help once again. Closing the page is not a punishment imposed on an asker -- it is just a form of good housekeeping.
    – mickmackusa
    Jul 20 at 11:25

Browse other questions tagged or ask your own question.