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.

Moving to PHQL

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é



67.7k

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



48.2k
edited Jan '16

I like to do joins in the following format. You can experient with the joins being implicitly added from the defined relationship but about 6-8 months ago I found it to be sometimes buggy in certain situations do to the aliases matching code. I'm planning to see what is currently working in Phalcon since there have been countless fixes to the 2.* code base. If you add the join condition explicitly then it will use that instead of the implicit condition (which was sometimes ignored from past experience). I suggest looking at the generated SQL until you are confident.

There is at least 3 ways to reference tables so just read the Model and Phql documentation over and over again.

$Type1 = 'Project\Models\Type1';
$Type2 = 'Project\Models\Type2';
$phql = <<<PHQL
SELECT *
FROM $Type1 Type1
JOIN $Type2 Type2 ON Type2.type1Id = Type1.id
WHERE
conditions...
PHQL;

$query = new PhQuery($phql, $di);
error_log($query->getSql()['sql']);
$query->setBindParams([
    '...' => $...
]);

$resultset = $query->execute();


48.2k

Secondly in the documents frequently uses: $this->modelsManager->createQuery I fail to see the difference

Through the DI any object could return the service. It is possible to have more than one DI (although I don't know why). So its simply returning the query object that is associated with the DB and models of that modelsManager. Probably this approach is used internally but I found it to be awkward looking and so I create a Phalcon Query object and I just pass in the DI as the second parameter.

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?



48.2k
Accepted
answer
edited Jan '16

Ok for something like that you will be wanting/needing to use normal SQL and then popping it into a model with either cloneResultMap or cloneResultMapHydrate. You can do it manually but this function sets states for you and is exactly what you want to do. Also you can manually trigger afterFetch on the model to make your code less brittle and to make moving to a pure PHQL easier in the future. PHQL is NICE and you will want to be using it as much as possible.

You can currenlty use subselects and EXISTS but not all SQL is supported (from every possible SQL dialect). I think that the smart guys who are interested in continuing to expand PHQL and the DB adapters are currently focused on PHP 7 support in Zephir and Phalcon.

In my project I overrode the static model ::find... methods and implimented a very specific new search that joins other tables in a very domain specific way with its own parameters. Then I translated that into PHQL to get the benefits over PDO like easily binding arrays instead of ?,?,? madness.

I first wrote it all in SQL and then some things were fixed and added to PHQL so I happily gutted a huge amount of custom work. I spent no more than 2.5 days to track down a tricky AST handling related bug in the EXISTS support and it was accepted into Phalcon. I think that if you are smart and you get some tips where to start then you could add this to Phalcon (Zephir code) in just several days of work. Phalcon just needs to understand that operator and how many paramters it takes, etc. Also you can extend the database adapters to add some simple things.



48.2k

Zephir code is super simple to read and just a bit more difficult to write. The only tricky part is setting up the build environment and then dealing with build times. It has a cached building now so its not too bad really on a level something like a really slow Webpack build time.



48.2k

@joyider I think that you should make an github issue about this to see what amount of effort is involved in this. It might just be as simple as adding the operator. If there is already an operator for QUERY operator QUERY then it should be easy.

edited Jan '16

@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é