Query multiple table relationships using Laravel Eloquent Models

  eloquent, eloquent--relationship, laravel, mysql

I’m trying to query multiple tables using Laravel Eloquent Models with one to one, one to many and many to many relationships.

I have a forms table, a brands table a users table and a brand_groups pivot table.

Each form has one brand and one user:

forms
  ID
  user_id
  brand_id

Brands do not have any foreign keys:

brands
  ID

Users do not have any foreign keys:

users
  ID

And there is a pivot table to create a many to many relationship for creating brand groups that have many users like brand members:

brand_groups
  brand_id
  user_id

I’m trying to get all the forms that belong to a user either by a direct ownership (forms.user_id) or by brand membership, all the forms from all the brands that the user is a member through brand_groups many to many pivot table.

For example, we have 2 brands, 2 users and 1 user is a member of 1 brand:

brand(ID: 1)
brand(ID: 2)

user(ID: 1)
user(ID: 2)

brand_group(brand_id: 1, user_id: 1)

form(ID: 1, user_id: 1,    brand_id: null)
form(ID: 2, user_id: null, brand_id: 1)
form(ID: 3, user_id: 2,    brand_id: 1)
form(ID: 4, user_id: 1,    brand_id: 2)

Using Laravel Eloquent Models (not direct DB facade calls), I’d like to retrieve all the forms that belong to a user. For the user(ID:1) there are 3 forms:

form(ID:1) direct user ownership
form(ID:2) user is a member of brand(ID:1) group which is the brand of form(ID:2)
form(ID:3) user is a member of brand(ID:1) group which is the brand of form(ID:3)

I gave it a shot using Eloquent: Relationships – Has Many Through:

Has Many Through

The "has-many-through" relationship provides a convenient way to access distant relations via an intermediate relation.

I have tried it like this:

class User extends Model
{
    public function forms()
    {
        return Forms::hasManyThrough(
            Form::class,
            BrandGroups::class,
            'brand_id',
            'brand_id',
            'id',
            'form_id',
        )->where('id', $this->id);
    }
}

But I get errors like:

BadMethodCallException with message 'Call to undefined method AppModelsForm::brand_groups()'

EDIT

After some digging, I have managed to come up with the working MySQL code that will return all the forms for a user:

SELECT * FROM `forms`
WHERE EXISTS (
  SELECT `brand_id`, `user_id`
  FROM `brand_groups`
  WHERE `forms`.`brand_id` = `brand_groups`.`brand_id`
    AND `brand_groups`.`user_id` = 1
) OR `forms`.`user_id` = 1

Now I just need to convert that query to an eloquent model relation.

Source: Laravel

Leave a Reply