We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Multiply Select from 3 tables

Hello, sorry for bad eng. How i make query from 3 tables and return custom array, example my old code:

function GetAllCourses() {
        global $DBH, $settings;
        $result = $DBH->query("SELECT l.id, l.section_id, s.title as 'section_title', s.created_time, s.closed_time, l.instructor_id, l.title, l.video_id, l.presentation_id, l.free_access, i.name 
                    FROM {$settings['db_prefix']}_lectures l, {$settings['db_prefix']}_sections s, {$settings['db_prefix']}_instructors i 
                    WHERE l.section_id = s.id AND l.instructor_id = i.id");
        $result->setFetchMode(PDO::FETCH_ASSOC);
        $object = array();

        $i=0;
        while($row = $result->fetch()) 
        {
            $i++;
            $object['allcount'] = $i;
            $object[$row['section_id']]['free'] = $row['free_access'];
            $object[$row['section_id']]['title'] = $row['section_title'];
            $object[$row['section_id']]['created_time'] = $row['created_time'];
            $object[$row['section_id']]['closed_time'] = $row['closed_time'];
            $object[$row['section_id']]['author'] = $row['name'];
            $object[$row['section_id']]['lectures'][$i] = array('id' => $row['id'], 'instructor' => $row['name'], 'title' => $row['title'], 'video_id' => $row['video_id'], 'presentation_id' => $row['presentation_id'], 'free_access' => $row['free_access']);
        }
        return $object;
    }

In NEW array key is a value $row['section_id'] from SQL result. How i can rewrite this on phalcon? Pls Help

You can use raw sql like you have above following this example: https://phalcon-php-framework-documentation.readthedocs.org/en/latest/reference/phql.html#using-raw-sql

Instead of returning the Resultset like in the example, you can set it into a variable like $list and do a loop to set the $object array like you want.