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

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



37.4k
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 .



4.7k
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 https://docs.phalcon.io/en/master/api/Phalcon_Mvc_Model_Query_Builder.html



125.8k

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



1.9k
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