Phalcon: specific column on joined PHQL

Question

The below PHQL generates a complex resultset like it should:

$phql = "SELECT User.*, ProductUser.* "
      . "FROM ProductUser "
      . "INNER JOIN User "
      . "WHERE ProductUser.product_id = 5";

Replacing ProductUser.* with an existing column like ProductUser.id causes an error:

MESSAGE: The index does not exist in the row
FILE: phalcon/mvc/model/row.zep
LINE: 67

This is version 2.0.6. Is this a bug or am I making a mistake somewhere? According to the documentation it should be fine.

score 0 · Accepted Answer · answered Sep 03 '15 at 08:18

0

It was my mistake (expecting the row to always be an object).
I hope this helps someone because looping complex resultsets is not in the documentation.

$result = $this->modelsManager->createQuery($phql)->execute();

if ($result instanceof \Phalcon\Mvc\Model\Resultset\Complex) {
    foreach ($result as $rows) {
        foreach ($rows as $row) {
            if (is_object($row)) {
                $modelData = $row->toArray());
            // this is what I needed
            } else {
                $id = $row;
            }
        }
    }
}

answered Sep 03 '15 at 08:18

galki

8,149
7
50
62

You probably have models for tables ProductUser and User, why not to use `queryBuilder` or internal Phalcons' model relation declarations? – yergo Sep 03 '15 at 08:25
@yergo Yes, queryBuilder will give the same result. I wish I could use model relations but I don't know how to get the "SELECT User.*, ProductUser.id" part in there. – galki Sep 03 '15 at 08:31

score 0 · Answer 2 · edited May 23 '17 at 10:34

0

First of all you're missing the ON clause in your query.

Anyways, it's easier and more error prone to use Phalcon's query builder for querying:

<?php

// modelManager is avaiable in the default DI
$queryBuilder = $this->modelsManager->createBuilder();
$queryBuilder->from(["product" => 'Path\To\ProductUser']);

// Inner join params are: the model class path, the condition for the 'ON' clause, and optionally an alias for the table
$queryBuilder->innerJoin('Path\To\User', "product.user_id = user.id", "user");

$queryBuilder->columns([
    "product.*",
    "user.*"
]);

$queryBuilder->where("product.id = 5");

// You can use this line to debug what was generated
// $generatedQuery = $queryBuilder->getPhql();
// var_dump($generatedQuery);

// Finish the query building
$query = $queryBuilder->getQuery();

// Execute it and get the results
$result = $query->execute();

// Now use var_dump to see how the result was fetched
var_dump($result);
exit;

edited May 23 '17 at 10:34

Community

1
1

answered Sep 04 '15 at 12:48

cvsguimaraes

12,910
9
49
73

Thanks for the answer. The error was coming from phalcon/mvc/model/row. By the way you don't always need an ON clause in PHQL but yes I ended up using it to get the correct result. – galki Sep 04 '15 at 12:52
@galki When you don't need to use the `ON`clause?! When there FK's set in the table schema or something? – cvsguimaraes Sep 04 '15 at 12:55
yup just take a look at the link in my question. I commented out all my model relations and it still gave me a broken result :/ – galki Sep 04 '15 at 12:58
@galki that's odd, anyways, why aren't you using the build-in methods for getting related records?! When you set a relationship you can provide an alias then simply use something like `$product->user`. Not sure what is your context but it might be easier and faster since the `user` property will be fetched only when you need it. – cvsguimaraes Sep 04 '15 at 13:01
this is a tricky case with a dynamically generated ManyToMany query with models A B and C and in addition to getting relation C I need to get B.id. – galki Sep 05 '15 at 03:28

Phalcon: specific column on joined PHQL

2 Answers2