Laravel - Retrieving specific column from a releted query

Question

I have 4 tables:

conversations
- id (pk)
- userId1 (fk)
- userId2 (fk)

users
- id (pk)
- name
- surname
.
.
.
- roleId (fk)
- userStatusId (fk)

roles
- id (pk)
- type (fk)

user_status
- id (pk)
- description (fk)

this are my models:

    class Conversation extends Eloquent {

        public function user1(){
            return $this->hasOne('User', 'id', 'userId1');
        }

        public function user2(){
            return $this->hasOne('User', 'id', 'userId2');
        }

    }

    class User extends Eloquent {

        public function role(){
            return $this->hasOne('Role', 'id', 'roleId');
        }
        public function userStatus(){
            return $this->hasOne('UserStatus', 'id', 'userStatusId');
        }


        // public function conversation1(){
        //  return $this->belongsToMany('Conversation', 'id', 'userId1');
        // }
        // public function conversation2(){
        //  return $this->belongsToMany('Conversation', 'id', 'userId2');
        // }


    }

class UserStatus extends Eloquent {

    public $timestamps = false;
    protected $table = 'user_status';

    public function user(){
        return $this->belongsToMany('User', 'id', 'userStatusId');
    }

}

class Role extends Eloquent {

    public $timestamps = false;

    public function user(){
        return $this->belongsToMany('User', 'id', 'roleId');
    }

}

Now what I want to do is, for example, take all the conversations where the "userId1" (on conversations) is of a "user" who have the status "description" equal to "registered".

That's what I do:

Route::get('/', function(){
    $conversation = Conversation::with(array('user1.userStatus' => function ($query){
        $query->where('description', '=', 'registered');
    }))->get();
    foreach ($conversation as $conv) {
        echo '<br \>';
        echo $conv;
    }
});

I expect to receive all the conversations record where the status of the userId1 is "registered" and nothing else... Instead what I receive are all the conversations records and, for each one, the user records and the records of the userStatus table (of this last I receive just the one who match the where clause and the ones who are not have a null value).

I know my english is terrible but I hope someone could understand and help me. Thanks!

score 0 · Answer 1 · answered Aug 11 '14 at 00:29

0

You may try this:

$conversations = Conversation::whereHas('user1.userStatus', function ($query){
    $query->where('description', '=', 'registered');
})->get();

This will return only the Conversation models whose related user1.userStatus is registered.

answered Aug 11 '14 at 00:29

The Alpha

143,660
29
287
307

score 0 · Accepted Answer · answered Aug 11 '14 at 06:13

All your relations are wrong. You need to read http://laravel.com/docs/eloquent#relationships and in your case it's:

class Conversation extends Eloquent {

    public function user1(){
        return $this->belongsTo('User', 'userId1');
    }

    public function user2(){
        return $this->belongsTo('User', 'userId2');
    }

}

class User extends Eloquent {

    public function role(){
        return $this->belongsTo('Role', 'roleId');
    }
    public function userStatus(){
        return $this->belongsTo('UserStatus', 'userStatusId');
    }
}

class UserStatus extends Eloquent {

    public $timestamps = false;
    protected $table = 'user_status';

    public function user(){
        return $this->hasMany('User', 'userStatusId');
    }

}

class Role extends Eloquent {

    public $timestamps = false;

    public function user(){
        return $this->hasMany('User', 'roleId');
    }

}

Now, to retrieve only those conversations you want this:

Conversation::whereHas('user1', function ($q) {
  $q->whereHas('userStatus', function ($q) {
    $q->where('description', 'registered');
  });
})->get();

or using this PR https://github.com/laravel/framework/pull/4954

Conversation::whereHas('user1.userStatus', function ($q) {
  $q->where('description', 'registered');
})->get();

Also, to make it more verbose, you can wrap that code in a scope:

// User model
public function scopeRegistered($query)
{
  $q->whereHas('userStatus', function ($q) {
    $q->where('description', 'registered');
  });
}

then:

Conversation::whereHas('user1', function ($q) {
  $q->registered();
})->get();

Couse I have not 15+ reputation i can't vote up your reply, but I want to thank you anyway. I thought I understood the meaning of belongsTo and hasAny, but it was not. Thx! — ciccioassenza, Aug 11 '14 at 10:49
No problem, just mark it as the answer if it solves the issue. — Jarek Tkaczyk, Aug 11 '14 at 10:50

Laravel - Retrieving specific column from a releted query

2 Answers2