phql problem with LEFT JOIN

hello. i have a problem with phql. i try $query = $this->modelsManager->createQuery("SELECT f.id, f.feedtype as type, f.description, usubscr.name as subscrusername, v.name as videoname, s.id as subscr, uvideo.name as videoauthor FROM Feed f LEFT JOIN Subscribes s ON (f.feedobjectid = s.followto and f.feedtype=2) LEFT JOIN Users usubscr ON (s.userid = f.userid) LEFT JOIN Video v ON (f.feedobjectid=v.id and f.feedtype=1) LEFT JOIN Users uvideo ON (v.userid = f.userid) WHERE f.userid = :userid: 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



82.7k

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;
});


1.2k

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));)



1.2k

PHQL "SELECT ... FROM Feed f LEFT JOIN ... LEFT JOIN Users usubscr ON (s.userid = f.userid) LEFT JOIN ... LEFT JOIN Users uvideo ON (v.userid = f.userid) WHERE ...

TO SQL (without the first join) "SELECT ... FROM Feed f LEFT JOIN Subscribes s ON (f.feedobjectid = s.followto and f.feedtype=2) LEFT JOIN ... LEFT JOIN Users uvideo ON (v.userid = f.user_id) WHERE ...



82.7k

how the model Feed is created?



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

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


1.2k

CREATE TABLE IF NOT EXISTS feed ( id int(11) NOT NULL AUTOINCREMENT, 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 AUTOINCREMENT=85 ;



1.2k

CREATE TABLE IF NOT EXISTS users ( id int(10) unsigned NOT NULL AUTOINCREMENT, 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 AUTOINCREMENT=57 ;



1.2k

CREATE TABLE IF NOT EXISTS subscribes ( id int(10) NOT NULL AUTOINCREMENT, 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 AUTOINCREMENT=113 ;