We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Get nested resultset with model and relationships

I have two models: Post and User, and I need to get result as this nested format:

[
    {
        "id": 1,
        "title": "post one title",
        "content": "hello",
        "user_id": 1,
        "user": {
            "id": 1,
            "nickname": "phalcon",
        }
    },
    {
        "id": 2,
        "title": "post two title",
        "content": "world",
        "user_id": 2,
        "user": {
            "id": 2,
            "nickname": "php",
        }
    }
]

How can I get this nested result? Many Thanks!

1


10.5k

You can use Phalcon PHQL

something like this:

$phql = "SELECT Post.*, User.* FROM Post JOIN User ON Post.user_id = User.id";
$rows = $manager->executeQuery($phql);


913
in reply to Aboozar Ghaffari

Thank you. But it doesn't meet my needs, your code get the result like:

[
    {
        "post": {
            "id": 1,
            "title": "title one",
            "user_id": 1
        },
        "user": {
            "id": 1,
            "nickname": "hello"
        }
    },
    {
        "post": {
            "id": 2,
            "title": "title two",
            "user_id": 2
        },
        "user": {
            "id": 2,
            "nickname": "world"
        }
    }
]

But what I need is the nested result, like:

{
        "post": {
            "id": 1,
            "title": "title one",
            "user_id": 1,
            "user": {
                  "id": 1,
                  "nickname": "hello"
            }
        },
    },
    {
        "post": {
            "id": 1,
            "title": "title one",
            "user_id": 1,
            "user": {
                  "id": 2,
                  "nickname": "world"
            }
        }
    }
]

Then what can I do to achieve this?

You can use Phalcon PHQL

something like this:

$phql = "SELECT Post.*, User.* FROM Post JOIN User ON Post.user_id = User.id";
$rows = $manager->executeQuery($phql);


913

Any more help?



6.9k

You won't be able to explicitly get that structure back from SQL, not without adding your own layer of abstraction to manipulate the dataset.



10.5k

As @Mechina said you should use your layer over the query result.

use foreach over the query result and make your dataset.

there is other normal way even by using RAW SQL.



913
in reply to Aboozar Ghaffari

Thank you, @tartanpro @Mechina

What is the RAW SQL way?

As @Mechina said you should use your layer over the query result.

use foreach over the query result and make your dataset.

there is other normal way even by using RAW SQL.