Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

struggling with PHQL

Hi,

I am trying to write my sql statement in PHQL style but i am getting some errors. I have two Models called 'Media & MediaCategory' both extends from Phalcon Models. My sql statement works fine using raw sql format as below:

$rawSQL = "SELECT m.*, mc.cid FROM MediaCategory mc JOIN media m ON m.id = mc.id WHERE m.id = 1";

    $result = $this->getModelsManager()->executeQuery($rawSQL);

But when i tring to convert it to PHQL format as follow i get this alias error "SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'm'"

    $result =$this->getModelsManager()->createBuilder()
        ->columns(array('m.*', 'mc.cid'))
        ->from(array('mc' =>'MediaCategory'))
        ->addFrom('media')
        ->join('media', 'm.id = mc.id', 'm')
        ->where('m.id = 1')
        ->getQuery()
        ->execute();

Could you please help me?

Thanks

edited Apr '15

i think you need to point what tables you need , like m.id , m.title ....

If you have same column names you need to specify .



3.0k
Accepted
answer

You should either use join or addFrom. Think that tables is being referenced twice resulting in the error. Try removing the addFrom('media'). See the examples of joins in the documentation http://docs.phalconphp.com/en/master/api/Phalcon_Mvc_Model_Query_Builder.html

I would personally set up a query log and see what the database is actually being sent.

edited May '15

Thank you all esp. @Shad Mickelberry for comments. Actually, I solved the problem by removing " ->addFrom('media')" as it was suggested by Shad Mickelberry.

By the way, I am using Phalcon Version 1.3.4 The solution and even the problem itself may differs in other versions