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. MVC

PHQL

so i'm trying to get this query to work : 


SELECT 
projects.title , 
projects.deadline, 
projects.status , 
CASE WHEN projects.client_type = 1 THEN (SELECT clients.firstname from clients where clients._= projects.client )
     WHEN projects.client_type = 2 THEN (SELECT concat(companies.firstname,' ( ',companies.fantasy,' )') from companies where companies._= projects.client )
         END AS 'client'
FROM projects

Result

title       deadline        status      client
------------------------------------------------------
SEO         2016-05-07      1           Jhon 123 ( COMPANY NAME  )
TEST        2016-05-07      1           Jhon 123 ( COMPANY NAME  )

The thing is the phql does not work , heres the code and the results :


public function IndexAction()
    {

      $qq = "
      SELECT
      Manager\Models\Projects.title ,
      Manager\Models\Projects.deadline,
      Manager\Models\Projects.status ,
      CASE WHEN Manager\Models\Projects.client_type = 1
           THEN (SELECT Manager\Models\Clients.firstname from Manager\Models\Clients where Manager\Models\Clients._= Manager\Models\Projects.client )
           WHEN Manager\Models\Projects.client_type = 2
           THEN (SELECT concat(Manager\Models\Companies.firstname,' ( ',Manager\Models\Companies.fantasy,' )') from Manager\Models\Companies where Manager\Models\Companies._= Manager\Models\Prohects.client )
         END AS 'client'
     FROM Manager\Models\Projects";
      $r = $this->modelsManager->executeQuery($qq);

      $this->view->disable();
      var_dump($r);
    }

Result

Syntax error, unexpected token IDENTIFIER(Manager\Models\Projects), near to '.client_type = 1 THEN (SELECT Manager\Models\Clients.firstname from Manager\Models\Clients where Manager\Models\Clients._= Manager\Models\Projects.client ) WHEN Manager\Models\Projects.client_type = 2 THEN (SELECT concat(Manager\Models\Companies.firstname,' ( ',Manager\Models\Companies.fantasy,' )') from Manager\Models\Companies where Manager\Models\Companies._= Manager\Models\Prohects.client ) END AS 'client' FROM Manager\Models\Projects', when parsing: SELECT Manager\Models\Projects.title , Manager\Models\Projects.deadline, Manager\Models\Projects.status , CASE WHEN Manager\Models\Projects.client_type = 1 THEN (SELECT Manager\Models\Clients.firstname from Manager\Models\Clients where Manager\Models\Clients._= Manager\Models\Projects.client ) WHEN Manager\Models\Projects.client_type = 2 THEN (SELECT concat(Manager\Models\Companies.firstname,' ( ',Manager\Models\Companies.fantasy,' )') from Manager\Models\Companies where Manager\Models\Companies._= Manager\Models\Prohects.client ) END AS 'client' FROM Manager\Models\Projects (663)


12.2k

You can always use raw SQL: https://docs.phalcon.io/en/latest/reference/phql.html#using-raw-sql

What you want to do is probably not supported by PHQL (in my case whenever I get "Syntax error, unexpected token IDENTIFIER" and SQL works correctly, I assume the fault is at PHQL level and I switch to Phalcon\Mvc\Model\Resultset\Simple).



145.0k
edited Apr '16

Change:

 CASE WHEN Manager\Models\Projects.client_type = 1
           THEN (SELECT Manager\Models\Clients.firstname from Manager\Models\Clients where Manager\Models\Clients._= Manager\Models\Projects.client )
           WHEN Manager\Models\Projects.client_type = 2
           THEN (SELECT concat(Manager\Models\Companies.firstname,' ( ',Manager\Models\Companies.fantasy,' )') from Manager\Models\Companies where Manager\Models\Companies._= Manager\Models\Prohects.client)

to:

 CASE Manager\Models\Projects.client_type WHEN 1
           THEN (SELECT Manager\Models\Clients.firstname from Manager\Models\Clients where Manager\Models\Clients._= Manager\Models\Projects.client )
           WHEN 2
           THEN (SELECT concat(Manager\Models\Companies.firstname,' ( ',Manager\Models\Companies.fantasy,' )') from Manager\Models\Companies where Manager\Models\Companies._= Manager\Models\Prohects.client)

Also use modelsManager where you can use aliases and use registerNamespaces so you dont have to use full namespaces.