CODEWITHSUNDEEP
phalcon
chunchiu Chan
chunchiu Chan

Reputation: 199

Nested Query in Phalcon

I have three tables and the ER diagram is shown as followed enter image description here

The field "f" in table "dNodes" contains floating point number. My first work is to collect those records in "dNodes" having "f" value 3, so the phql for this task would be SELECT * FROM dNodes WHERE dNodes.f = 3

I then want to collect records from "neighbours" where both "aInd" and "bInd" match the result from my first work. In terms of sql, I can use either

SELECT * FROM neighbours AS n WHERE n.aInd IN (SELECT a.nodeInd FROM dNodes AS a WHERE a.f = 3) AND n.bInd IN (SELECT b.nodeInd FROM dNodes AS b WHERE b.f = 3)

or

SELECT n.* FROM neighbours AS n INNER JOIN (SELECT aa.nodeInd FROM dNodes AS aa WHERE aa.f = 3) AS a ON n.aInd = a.nodeInd INNER JOIN (SELECT bb.nodeInd FROM dNodes AS bb WHERE bb.f = 3) AS b ON n.bInd = b.nodeInd;

They both give me the same and correct result in MySQL. To collect such data through Phalcon RESTful web service, I use the following code:

$app->get('/neighbours', function() use ($app){
$phql = "SELECT n.* FROM neighbours AS n INNER JOIN (SELECT aa.nodeInd FROM dNodes AS aa WHERE aa.f = 3) AS a ON n.aInd = a.nodeInd INNER JOIN (SELECT bb.nodeInd FROM dNodes AS bb WHERE bb.f = 3) AS b ON n.bInd = b.nodeInd;";
$neis = $app->modelsManager->executeQuery($phql);

$data = array();
foreach($neis as $nei){
    $data[] = array(
        "aInd" => $nei->aInd,
        "bInd" => $nei->bInd,
        "distance" => $nei->distance
    );
}

echo json_encode($data);
});

but finally got an error

Fatal error: Uncaught exception 'Phalcon\Mvc\Model\Exception' with message 'Syntax error, unexpected token (, near to 'SELECT aa.nodeInd FROM dNodes AS aa WHERE aa.f = 3) AS a ON n.aInd = a.nodeInd INNER JOIN (SELECT bb.nodeInd FROM dNodes AS bb WHERE bb.f = 3) AS b ON n.bInd = b.nodeInd;', when parsing: SELECT n.* FROM neighbours AS n INNER JOIN (SELECT aa.nodeInd FROM dNodes AS aa WHERE aa.f = 3) AS a ON n.aInd = a.nodeInd INNER JOIN (SELECT bb.nodeInd FROM dNodes AS bb WHERE bb.f = 3) AS b ON n.bInd = b.nodeInd; (214)' in D:\Program Files\wamp\www\FYP\api\api_neighbours.php on line 10

Upvotes: 1

Views: 981

Answers (1)

yergo
yergo

Reputation: 4980

As far as I know, subqueries are not supported in PhQL. You can gather connection iterface from your DI() and try to put a raw query into it:

//$connection = $app->getDI()->get('db');
$connection = \Phalcon\DI::getDefault()->get('db');
$neis = $connection->query($phql); // standard-SQL query

Upvotes: 2

Related Questions