executeQuery

I would like the following query to give me the following returned format:

$results = $app->modelsManager->executeQuery('
SELECT
    Apps.id,
    Members.id
FROM
    Apps,
    Members
WHERE
    Apps.member_id=Members.id
');
[0] ['Apps']['id'] = 1

[0]['Members']['id'] = 2

[1] ['Apps']['id'] = 4

[1] ['Members']['id'] = 32

....

[25]['Apps']['id'] = 7

[25]['Members']['id'] = 14

-----------------------------------------

Right now I am getting back:

[0] ['id'] = 1

[1] ['id'] = 2



I understand why this is happening just curious if Phalcon has a way to include table names in the result set.

Thanks in advance.
edited Dec '14

UPDATE.

The following code does not work for me. I took this from the Phalcon documentation.

$phql = "SELECT Cars.*, Brands.* FROM Cars, Brands WHERE Brands.id = Cars.brands_id";

$rows = $manager->executeQuery($phql);

foreach ($rows as $row) {
    echo "Car: ", $row->cars->name, "\n";
    echo "Brand: ", $row->brands->name, "\n";
}

So in my example I tried:

foreach($results as $result) {
    echo $result->members->id;
}

I get an empty string. If I print_r ($result) I get the results but just not in an associative array as I am asking for.

UPDATE #2

I believe this has something to do with Phalcon\Mvc\Model\Resultset. My query is returning Phalcon\Mvc\Model\Resultset\Simple. I believe I need Phalcon\Mvc\Model\Resultset\Complex. Not sure how to force this though.

UPDATE #3

This user reported the same issue: http://forum.phalconphp.com/discussion/902/how-to-get-model-data-in-resultset-

Unfortunatley he was not able to solve the problem using executeQuery().

I think the problem is that both fields are called 'id' and therefore, the value of one is being overwritten with the value of the other.

Try the following:

$results = $app->modelsManager->executeQuery('
SELECT
    Apps.id AS apps_id,
    Members.id AS members_id
FROM
    Apps,
    Members
WHERE
    Apps.member_id=Members.id
');

With this, you should at least get two columns.

Yes that would work but I am looking for a solution that would create an associative array from the query that includes the models.

What, that includes both an Apps model and a Members model?

I don't think thats possible.

If you want all the properties to be together, it might be better to create a view which comprises both tables, and then create a model to access that view.

Gareth that seems like such overkill. Both CakePHP and CodeIgnitor provide this functionality with the returned data.

can you post a cake or ci example with the results, and then I might understand your problem a bit better.

In CakePHP if I execute a query as follows:

$results=$this->query('SELECT Member.id, Member.username, Article.id, Article.name FROM members Member, articles Article WHERE Member.id=Article.member_id');

When I print r the $results variable cake returns the result sets as follows:

[0]
    [Member]
        [id]=>55
        [username]=>"gkelly"
    [Article]
        [id]=>233
        [name]=>"Why PHP programmers are awesome"
[1]
    [Member]
        [id]=>76
        [username]=>"jramsey"
    [Article]
        [id]=>299
        [name]=>"How to use regular expressions"
[2]
    ...

Now if I were to run the same query in Phalcon I get back the following result set:

[0]
    [id]=>233
    [username]=>"gkelly"
    [name]=>"Why PHP programmers are awesome"
[1]
    [id]=>299
    [username]=>"jramsey"
    [name]=>"How to use regular expressions"
[2]
    ...

Notice how the ID gets overwritten with Phalcon? I understand that I can simply alias one of the fields to overcome this but in my particular application I do not want to do this. I would like the result set returned as Cake is doing. Does Phalcon have a way to accomplish this?

Phalcon is just returning what you would get in a normal PHP query, i.e., one id being overwritten with the other in an associative array. If you want some sort of structured relationship between records, you might be better off looking at this:

http://docs.phalconphp.com/en/latest/reference/models.html#relationships-between-models