Moving to PHQL

Question

Moving to PHQL

André Karlsson Jan '16

Created Jan '16	Last Reply Jan '16	Replies 1	Views 855	Votes 0

André Karlsson 8.7k

Jan '16

Hi guys

I'm trying to move most of my code to PHQL from native SQL, but I have troubel decoding the Docs :)

Lets say I have this simple SQL with a few joins:


SELECT m.team_a_name, m.team_b_name, m.id,ta.name,tb.name, s.id,s.ip,s.con 
FROM matches m, servers s, teams ta, teams tb 
WHERE m.start=1 AND m.team_a_name=ta.name AND m.team_b_name=tb.name AND m.server_id=s.id;

Lets say I ant to run this outside of a controller or view. If I understand I use Query like this:


$phql= new Query("SELECT m.team_a_name, m.team_b_name, m.id,ta.name,tb.name, s.id,s.ip,s.con
FROM matches AS m, servers AS s, teams AS ta, teams AS tb
WHERE m.start=1 AND m.team_a_name=ta.name AND m.team_b_name=tb.name AND m.server_id=s.id");

m.server_id belongs to s.id.

First of I'm not sure if this works since I can't get it to work as i want to. How does PHQL handle the joins or do I explicitly need to use JOIN of any kind?

Secondly in the documents frequently uses:

$this->modelsManager->createQuery

I fail to see the difference

Regards André

Izo · Answer 1 · 2016-01-25T05:32:51-07:00

i am not expert of model manager but basiccaly you hve to register it as a service, like `this->di->setShared("modelmanager", function(){ return new ModelManager(); });

now the way i do my custom queries is:


$di = \Phalcon\Di::getDefault();

$this->DB = $di->get('db');

$sql = $this->DB->query("SELECT * FROM `_MY_TABLE_` WHERE `id` = ? AND activation_key = ?", [
  id, //param 1 basiccaly
  $key //param 2
]);

$res = $sql->fetch();

You can write join as much as u like in this query.

you have ather methods etc.. u can check the docs but this is how I do it. Hopefully someone who uses modelsManager can post more examples.

The other thing that i use is \Phalcon\Mvc\Model\Query\Builder you cna check the docs, if you need i will post examples of how I create my form with 100 fields search and so on...

André Karlsson · Answer 2 · 2016-01-25T06:38:36-07:00

Hmm

This seems way more complicated that using plain SQL. Are there any major Pros of using Phalcon SQL rather than plain SQL.

If you have more complex SQL like:

SELECT M.p1, pr1.playername, M.p2, pr2.playername FROM player pr1 
INNER JOIN player pr2 INNER JOIN
(
   SELECT plays1.player p1, plays2.player p2, plays1.team t1 FROM plays plays1 
   INNER JOIN plays plays2 
   ON (plays1.player < plays2.player AND plays1.team = plays2.team)
   GROUP BY plays1.player, plays2.player HAVING COUNT(*) = 
((SELECT COUNT(*) FROM plays plays3 WHERE plays3.player = plays1.player) + 
(SELECT COUNT(*) FROM plays plays4 WHERE plays4.player = plays2.player)) /2
) M ON pr1.playerID = M.p1 AND pr2.playerID = M.p2 
UNION ALL
SELECT M.pid, M.pname, N.pid2, N.pname2 FROM
(
(SELECT p.playerID pid, p.playerName pname, pl.team FROM player p
 LEFT JOIN plays pl ON p.playerId = pl.player WHERE pl.team IS NULL) M
 INNER JOIN
 (SELECT p.playerID pid2, p.playerName pname2, pl.team FROM player p
  LEFT JOIN plays pl ON p.playerId = pl.player WHERE pl.team IS NULL) N 
 ON (pid < pid2)
)

I currently fail to see how to implement a statement like that. And I don't see any documention on UNION at all?

André Karlsson · Answer 3 · 2016-01-25T13:29:11-07:00

@dschissler Thanks for your very informative posts :) You seem to know your PHQL. As a DBA raw sql comes easy and i'm just blind to that. But I can see that modifying the models can give you some good features.

I guess it comes down to plain understanding as usual :)

I'll keep in reading the docs and focus on the simplier queries for now. And thanks to you guys, as I have my initial PHQL from my first post, running just fine now :)

Regards André