Laravel Eloquent Relationship Query

Question

I created a messaging system for the project I'm working on, and it seems to have hit a little stumbling block.

I have the following table structures (for clarity, inappropriate columns are omitted)

messages ------------------------- from_id to_id spam users ------------------------- id group_id groups ------------------------- id access_level 

access level uses bitwise permissions

 001 read 010 write 100 delete 

therefore, for reading and writing, the access level will be "3"

users may have permissions revoked by the site administrator, so I'm trying to select messages that were sent to users who still have write permissions to the recipient.

my models are configured as such (again only the relevant parts are 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 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(); 

Receives messages sent to the current registered user, where the access level of the sender group still allows the user to write messages (theoretically), however, he simply returns all messages sent to the user.

I also tried to target the group (groups 6 and 7 are forbidden 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 messages to the user.

tl;dr

How, using Eloquent, would I select all messages sent to the user, where the groups of senders have write permissions?

edit

Of course ::with will not affect messages that get pulled out, as this is only 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.

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

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 "from" for a specific group_id, for example 2? I think that such a query should properly filter your results.