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.

Struggling to access PHQL JOIN query result

I've got a simple pair of models like this:

class Users extends Model
{
    public $id;
    public $name;
    public $role_id;
}

class Roles extends Model
{
    public $id;
    public $role_name;
}

The role id in the Users model relates to the id in the Roles model.

I want to do a simple query with a join, like this:

        $query = $this->modelsManager->createBuilder()
            ->columns(["u.*", "r.role_name"])
            ->from(["r" => "Roles"])
            ->join("Users", "u.role_id = r.id", "u")
            ->where("u.name = :name:")
            ->getQuery()
            ->getSingleResult(["name" => $username]);

This is generating the correct SQL code (I've turned on SQL logging) and it's also generating the correct result. But I can't figure out how to extract the role name from the result. Various Phalcon documentation and examples I've read imply that it's something like

        foreach ($query as $user) {
            error_log ("user name is " . $user->name);
            error_log ("role name is " . $user->r->role_name);
        }

but that fails with an error that r isn't a property of Users (and neither is role name, if I leave out the r->).

I'm sure this is pretty simple, but it's got me beat. Any help appreciated!



3.0k
edited Jun '17
 $query = $this->modelsManager->createBuilder()
            ->columns(["u.*", "r.role_name as roleName"])
            ->from(["r" => "Roles"])
            ->join("Users", "u.role_id = r.id", "u")
            ->where("u.name = :name:")
            ->getQuery()
            ->getSingleResult(["name" => $username]);

and

error_log ("role name is " . $user->roleName);


5.5k
Accepted
answer

You need to have a belongsTo relation in you Users model to Roles then use the alias to retrieve data.
So if your alias is UserRole for example :

// in the Users initialize
$this->belongsTo('role_id', 'Roles', 'id', array('alias' => 'UserRole'));

// then be able to
$user = Users::findFirst();
$user->UserRole->role_name

```php

$query = $this->modelsManager->createBuilder() ->columns(["u.*", "r.role_name as roleName"]) ```

and

```php

error_log ("role name is " . $user->roleName);

```

This still logs as "Access to undefined property Users::roleName".

You need to have a belongsTo relation in you Users model to Roles then use the alias to retrieve data.
So if your alias is UserRole for example :

``` // in the Users initialize $this->belongsTo('role_id', 'Roles', 'id', array('alias' => 'UserRole'));

// then be able to $user = Users::findFirst(); $user->UserRole->role_name ```

Thanks for the reminder. I did actually have a belongsTo() relation in the original model, but I'd left it off here to simplify the example. But I didn't have the alias - and that was the missing piece of the puzzle. Once I had that in place, everything worked. Thanks so much!