Phalcon ExecuteQuery inner Join issues

Question

This is Native SQL Query, thats work in MySql:

SELECT a.pshdQty
FROM production_stock_header a
INNER JOIN (SELECT MAX(pshdId) AS pshdId
FROM production_stock_header
WHERE pshdAuditDelete = 'N'
) b
ON a.pshdId = b.pshdId

But, why in Phalcon this always errors?

$triggerSet = $this->modelsManager->executeQuery("
                    SELECT  pshdQty
                    FROM ProductionStockHeader  
                    INNER JOIN (SELECT MAX(pshdId) AS pshdId
                    FROM ProductionStockHeader
                    WHERE ProductionStockHeader.AuditDelete = 'N'
                    ) AS b
                    ON ProductionStockHeader.pshdId = b.pshdId
                ");

Phalcon ORM just don't support queries like this, just inner join ProductionStockHeader and where statement. — Juri, Jul 19 '16 at 13:44

Timothy · Accepted Answer · 2016-07-19T14:36:46.843

1

Phalcon ORM doesn't provide a way to write such join statements.
I'd suggest to just run a raw native SQL query on your db connection:

$triggerSet = $this->db->query("SELECT a.pshdQty
        FROM production_stock_header a
        INNER JOIN (SELECT MAX(pshdId) AS pshdId
            FROM production_stock_header
            WHERE pshdAuditDelete = 'N'
        ) b
        ON a.pshdId = b.pshdId")->fetch();

$this->db points to your database connection you registered in your services.

You can also drop the inner join all together:

$triggerSet = $this->modelsManager->executeQuery("
    SELECT pshdQty
    FROM ProductionStockHeader
    WHERE AuditDelete = 'N'
    ORDER BY pshdId DESC
    LIMIT 1
");

edited Jul 19 '16 at 14:36

answered Jul 19 '16 at 14:06

Timothy

2,004
3
23
29

How can i get the results? when i vardump it doesnt show the results, thx – code K.O. Jul 19 '16 at 14:21
@codeK.O., if you try the 2nd solution, won't that be easier? It should give you the same result – Timothy Jul 19 '16 at 14:23
im phalcon new comer, and before this im using codeigniter.. the 2nd solutions have a bug when the records data become bigger.. it cannot fetch till the end, so sometimes if im using ORDER BY DESC, it doesnt get the last results. – code K.O. Jul 19 '16 at 14:27
@codeK.O. What do you mean with `it doesnt get the last results`. I doubt MySQL would randomly drop your records. Do you mean the column `pshId` isn't ordering your data correctly? – Timothy Jul 19 '16 at 14:32
in short answer is : yes.. i dont know why.. but it happens in my last project. – code K.O. Jul 19 '16 at 14:40
maybe a stupid question but; your `pshId` column is of a `numeric` type, right? If it were a string, it would indeed cause problems when sorting. – Timothy Jul 19 '16 at 14:41
yes, it auto increment, so it must be int (numeric).. i don't know why it happens too.. first time i use it and the records still small it works fine, but while the records getting bigger it has bug.. – code K.O. Jul 19 '16 at 14:46
I still would just remade your statement for working with PHQL, it can be done, also use query builder :) Just join whole ProductionStockHeader and use where ? – Juri Jul 19 '16 at 16:09

Phalcon ExecuteQuery inner Join issues

1 Answers1