0

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?

1 Answer 1

2

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

Sign up to request clarification or add additional context in comments.

9 Comments

ERROR Illuminate\Database\Eloquent\RelationNotFoundException with message 'Call to undefined relationship [brand_groups] on model [App\Models\Form].' There is no direct relationship of the forms table and the brand_groups table; brand_groups table is the pivot table that connects users with brands.
Can you please post your models?
I have updated my question with the eloquent models and their relationships.
I edited my answer, try and keep it updated.
Thank you for taking time to look onto this problem. Without any modification, your edited eloquent query works, but it doesn't group the forms to a single collection, so I get all the forms that the user is an owner with <query>->forms and then I get a collection of all the brands that the user belongs to using the <query>->brands<each>->forms which is a great step (upvoted for that), but I'd like to have a query that I can get all the forms in one collection. I know it's posible; do you know how I would achieve the query to get all the forms in a single collection?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.