CODEWITHSUNDEEP
mysqllaraveleloquenteloquent-relationship
Christos Lytras
Christos Lytras

Reputation: 37298

Query multiple table relationships using Laravel Eloquent Models

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 App\Models\Form::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.

Eloquent Models

User.php

class User extends Authenticatable implements MustVerifyEmail
{
    public function brands()
    {
        return $this
            ->belongsToMany(Brand::class, 'brand_groups')
            ->using(BrandGroups::class)
            ->as('member');
    }

    public function forms()
    {
        return $this->hasMany(Form::class, 'user_id');
    }
}

Brand.php

class Brand extends Model
{
    protected $table = 'brands';

    public function forms()
    {
        return $this->hasMany(Form::class);
    }

    public function members()
    {
        return $this
            ->belongsToMany(User::class, 'brand_groups')
            ->using(BrandGroups::class)
            ->as('member');
    }
}

Form.php

class Form extends Model
{
    protected $table = 'forms';

    public function owner()
    {
        return $this->belongsTo(User::class);
    }

    public function brand()
    {
        return $this->belongsTo(Brand::class);
    }
}

UPDATE

I manage to find a query to get all forms related to a user like this:

class User extends Authenticatable implements MustVerifyEmail
{
    ...

    public function allForms()
    {
        return Form::where(function ($q) {
            $q->whereExists(function ($q) {
                $q->from('brand_groups')
                    ->where('forms.brand_id', DB::raw('brand_groups.brand_id'))
                    ->where('brand_groups.user_id', $this->id);
            })->orWhere('owner_id', $this->id);
        });
    }
}

How this can be converted to a direct User model eloquent relationship?

Upvotes: 0

Views: 1475

Answers (1)

Majdiden
Majdiden

Reputation: 84

Have you tried to Eager Load the User model relationships?

Edit Firstly: the pivot table name should be the singular -snake_case- name of both tables and should be in alphabetical order (brand_user)

Next, try the following:

return User::where(‘id’, $this->id)->with([‘forms’,‘brands.forms’)->get();

This should return the Forms with direct ownership plus the user Brands and their associated Forms

Upvotes: 2

Related Questions