Eloquent Many to many relationship

Question

I am using Eloquent ORM and I have a Category model that connects to a Note model by Many-to-Many relationship. I also have a User model that connects to the Note model by many to many relationship and to the Category model by one to many relationship.
The database structure is the following:
notes
id, title, content, created_at....

categories
id, title, description

users
id, first_name, last_name...

note_user
user_id, note_id, permission_id

category_note
category_id, note_id

note_permissions
permission_id, permissions

What I am trying to get:

All categories that contain notes that their IDs exist with a specific user ID in user_note table, and check the permission_id of that row.
Get all notes for a specific category that their IDs exist in user_note table with a certain user ID, and check permission_id of that row.

I wrote a method to check what permission a certain user has to a note (in Note model):

public function can($uid, $perm)
{
    $users = $this->users()->get();
    foreach ($users as $user) {
        if ($user->id == $uid) { // this user is shared
            $permId = $user->pivot->permission_id;
            $permissions = NotePerm::find($permId)->permissions;
            $perm = 'note.' . $perm;
            if (array_key_exists($perm, $permissions) && $permissions[ $perm ])
                return true;
        }
    }
    return false;
}

This is my idea to fetching all categories that contain notes that a certain user can see (meaning there is a row in user_note with his user ID and a note ID that belongs to this category) - this method is placed in Category model:

public function scopeWithNotesCan($query, $uid, $perm)
{
    return $query->whereHas('notes', function ($query) use ($uid, $perm) {
        $query->can($uid, $perm);
    });
}

And this is the call:

$categories->WithNotesCan($this->user->id, 'view');

Now, I am getting an error:

Call to undefined method Illuminate\Database\Query\Builder::can()

How can I check each note with a custom method before fetching all notes?

AdamGold AdamGold · Accepted Answer · 2015-11-01T12:23:15

Accomplished it by using multiple whereHas. In the Category model, added the following scope:

public function scopeForUser($query, $uid, $perm = 'view')
{
    return $query->whereHas('notes', function ($query) use ($uid, $perm) {
        $query->forUser($uid, $perm)->orWhere('private', '=', false);
    });
}

in the Note model, added the following scope:

public function scopeForUser($query, $uid, $perm = 'view')
{
    $permissions = NotePerm::findWhere($perm);
    return $query->whereHas('users', function ($query) use ($uid, $permissions) {
        $query->where('note_user.user_id', $uid)->whereIn('note_user.permission_id', $permissions)->orWhere('private', '=', false);
    });
}

The findWhere NotePerm method gets an array of all permissions the contain a certain permission string:

public function scopeFindWhere($query, $perm = 'view')
{
    $permissions = self::all();
    $perm = 'note.' . $perm;
    return $permissions->filter(function ($item) use ($perm) {
        return array_key_exists($perm, $item->permissions) && $item->permissions[ $perm ];
    })->lists('id')->toArray();
}

So to get all categories that are shared with a specific user ($user is the owner of the categories, not the specific user we are searching for):

$user->categories()->forUser($this->user->id, $perm);

To get all notes from a specific category that are shared with a specific user:

$user->categories()->forUser($this->user->id, $perm)->notes()->forUser($this->user->id, $perm);

Eloquent Many to many relationship

1 Answers