Laravel Eloquent querying relationships

Question

I have created a messaging system for a project I am working on and appear to have hit a bit of a stumbling block.

I have the following table structures (irrelevent columns omitted for clarity)

messages
-------------------------
from_id    to_id    spam

users
-------------------------
id    group_id

groups
-------------------------
id    access_level

the access level uses bitwise permissions

001 read
010 write
100 delete

so to read and write, the access level would be '3'

users can have permissions revoked by site admin, so what I am trying to do is to select messages which have been sent to users where the sender still has write permissions.

my models are set up as such (again only relevant parts shown)

class User extends Eloquent {
    public function group() {
        return $this->belongsTo('Group');
    }
}

class Message extends Eloquent {
    public function to() {
        return $this->belongsTo('User', 'to_id');
    }
    public function from() {
        return $this->belongsTo('User', 'from_id');
    }
}

I have tried the following

Message::with(['from' => function($query)
{
    $query->with(['group' => function($_query)
    {
        $_query->where('access_level', '&', 2);
    }]);
}])
->where('to_id', Auth::user()->id)
->get();

This gets the messages sent to the currently logged in user where the senders group's access level still enables the user to write messages (theoretically) however it is simply returning all the messages sent to the user.

I have also tried specifically targeting the group (groups 6 and 7 are banned and unconfirmed accounts):

Message::with(['from' => function($query)
{
    $query->whereNotIn('group_id', [6,7])
}])
->where('to_id', Auth::user()->id)
->get();

which also returns all of the messages to the user.

tl;dr

How, using Eloquent, would I select all messages sent to a user where the senders group has write permissions?

edit

Of course, ::with will not affect the messages which get pulled, as it only does lazy loading of joined tables.

Answer 1

My solution was to do this:

Message::select(DB::raw("messages.*"))
    ->join("users AS from", "from.id", "=", "from_id")
    ->join("groups", "groups.id", "=", "from.group_id")
    ->whereRaw("groups.access_level & 2 = 2")
    ->where('to_id', '=', Auth::user()->id);

Which does exactly what I wanted it to.

As stated in my question ::with only affects the loading of the data.

Answer 2

How about this?

 Message::with(['from' => function($query){
              $query->where('group_id', '=', '2');
    }])
    ->where('to_id', Auth::user()->id)
    ->get();

Have you tried filtering the "from" for a certain group_id, like 2? I think a query like this should filter your results correctly.