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

phql problem with LEFT JOIN

hello. i have a problem with phql. i try $query = $this->modelsManager->createQuery("SELECT f.id, f.feed_type as type, f.description, u_subscr.name as subscr_user_name, v.name as video_name, s.id as subscr, u_video.name as video_author FROM Feed f LEFT JOIN Subscribes s ON (f.feed_object_id = s.follow_to and f.feed_type=2) LEFT JOIN Users u_subscr ON (s.user_id = f.user_id) LEFT JOIN Video v ON (f.feed_object_id=v.id and f.feed_type=1) LEFT JOIN Users u_video ON (v.user_id = f.user_id) WHERE f.user_id = :user_id: GROUP BY f.id ORDER BY f.id DESC");

result: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'u_subscr.name' in 'field list' please help. in phpmyadmin it works



98.9k

Could you add an events manager to your connection to see what SQL is being sent to the database server?

$di->set('db', function() {

    $eventsManager = new \Phalcon\Events\Manager();    

    //Listen all the database events
    $eventsManager->attach('db', function($event, $connection) {
        if ($event->getType() == 'beforeQuery') {
            echo $connection->getSQLStatement();
        }        
    });

    $connection = new \Phalcon\Db\Adapter\Pdo\Mysql(array(
        "host" => "localhost",
        "username" => "root",
        "password" => "secret",
        "dbname" => "invo"
    ));

    //Assign the eventsManager to the db adapter instance
    $connection->setEventsManager($eventsManager);

    return $connection;
});


2.0k

when I try to register I get another error : Notice: Phalcon\Session\Adapter::set() [phalcon\session\adapter.set]: Cannot use a scalar value as an array in .. ($this->session->set('user', $user);)

ps: The first problem has been solved with using clean SQL but I want to do the working version of this query with phql.. (use Phalcon\Mvc\Model\Resultset\Simple as Resultset; $user=new Users(); $res=new Resultset(null, $user, $user->getReadConnection()->query($query2));)



2.0k

PHQL "SELECT ... FROM Feed f LEFT JOIN ... LEFT JOIN Users u_subscr ON (s.user_id = f.user_id) LEFT JOIN ... LEFT JOIN Users u_video ON (v.user_id = f.user_id) WHERE ...

TO SQL (without the first join) "SELECT ... FROM Feed f LEFT JOIN Subscribes s ON (f.feed_object_id = s.follow_to and f.feed_type=2) LEFT JOIN ... LEFT JOIN Users u_video ON (v.user_id = f.user_id) WHERE ...



98.9k

how the model Feed is created?



2.0k
edited Oct '14
class Feed extends \Phalcon\Mvc\Model {

    public function initalize() {
        $this->useDynamicUpdate(true);
    }
}


2.0k

CREATE TABLE IF NOT EXISTS feed ( id int(11) NOT NULL AUTO_INCREMENT, user_id int(11) NOT NULL, feed_action int(2) NOT NULL, feed_type int(2) NOT NULL, feed_object_id int(11) NOT NULL, description varchar(255) NOT NULL, cdate int(10) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=85 ;



2.0k

CREATE TABLE IF NOT EXISTS users ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, email varchar(80) NOT NULL, password varchar(128) NOT NULL, network varchar(50) DEFAULT NULL, identity varchar(150) DEFAULT NULL, city varchar(80) DEFAULT NULL, role varchar(12) DEFAULT 'Users', about varchar(400) DEFAULT NULL, speciality_id int(10) unsigned DEFAULT '1', birthday int(10) unsigned DEFAULT '0', cdate int(10) unsigned NOT NULL, PRIMARY KEY (id), KEY speciality_id (speciality_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=57 ;



2.0k

CREATE TABLE IF NOT EXISTS subscribes ( id int(10) NOT NULL AUTO_INCREMENT, user_id int(10) NOT NULL, follow_to int(10) NOT NULL, PRIMARY KEY (id), UNIQUE KEY user_id_2 (user_id,follow_to), KEY user_id (user_id), KEY follow_to (follow_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=113 ;

So I can not do a self join by Phalcon's ORM?