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. Beginners

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!



5.1k
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);


12.8k
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


1.3k
in reply to phil67000 

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

and 


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

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



1.3k
in reply to corentin-begne

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!