Laravel 9 Backpack 5 addBaseClause from 2 tables

Question

I've been trying pretty much every possibility that came into my mind, but no luck.

I'm trying to show a list of companies created WHERE the parent user_id in the Companies table is backpack_user()->id OR company_id AND user_id are present in the CompaniesUsers table.

Basically the user_id in the Companies table is the user who created the particular company, and the user_id in CompaniesUsers table are users that have been invited to manage the Company, if it makes sense. All of these users should be able to see the Company in their list.

Now i know how to do this in MySQL and it works, but i have no idea how can i make it work using Backpack.

Many thanks

Users | id | name | |:---|:-----|

Companies | company_id | user_id (parent) | company_name | |:-----------|:-----------------|:-------------|

... and

Companies_Users | company_id | user_id | |:-----------|:--------|

UPDATE

Company.php - company model

public function users()
{
    return $this->belongsToMany(User::class, 'companies_users', 'company_id', 'user_id');
}

public function user()
{
    return $this->belongsTo(User::class, 'user_id', 'id');
}

public function companies_users() {
    return $this->belongsTo(CompanyUser::class);
}

User.php - user model

public function companies() {
    return $this->belongsToMany(\App\Models\Company::class, 'companies_users', 'company_id', 'user_id');
}

Current clause I have in my CompanyCrudController.php is

$this->crud->addClause('WHERE', 'company_id', backpack_user()->default_company);

That's just for Laravel to pick up a default company when the user logs in. This is the controller where i would like to show to company where the user is a creator (companies table, user_id column) or the user can manage the company (companies_user table, company_id column and user_id column). For the users table i'm still using Laravel's default, so the id would be just id. This is what I have atm, I had to rollback as I did not managed to make it work, or probably I did not understood correctly.

Answer 1

I think I know exactly what you need, since I use that for many of my projects using Backpack. First of all, make sure you set the right relationships for your models. Assuming your Crud Controller is CompaniesCrudController and its model Company, you would probably have two kind of relationships for users on your Company Model.

1- Company->creator() a belongsTo() relationship that establishes the owner/creator of the Company.

https://laravel.com/docs/10.x/eloquent-relationships#one-to-one-defining-the-inverse-of-the-relationship

2- Company->managers() a hasManyThrough() relationship that connects the Model with users who were invited as managers (through CompaniesUsers table).

https://laravel.com/docs/10.x/eloquent-relationships#has-many-through

Your relationship names probably differ, but based on the above, you can add a few clauses to help you achieve what you need. In the method setup() of your CompaniesCrudController add something like this:

use Illuminate\Database\Eloquent\Builder;

$this->crud->addClause('whereHas', 'creator', function (Builder $queryCreator) {
    $queryCreator->where('users.id', backpack_user()->id); // assuming users is the table for your users!
}));

$this->crud->addClause('orWhereHas', 'managers', function (Builder $queryManagers) {
    $queryManagers->where('users.id', backpack_user()->id);
});

Maybe there is a better/cleaner way to do it, but this is the way I normally do it. Hope that helps!

Here is the link to Backpack docs https://backpackforlaravel.com/docs/5.x/crud-api#custom-advanced-queries

Cheers!

Answer 2

My final code was, which works great for me. Thanks @maurohmartinez

CRUD::addBaseClause('where', 'company_id', backpack_user()->default_company);
CRUD::addBaseClause('orWhereHas', 'users', function (Builder $queryManagers) {
        $queryManagers->where('user_id', backpack_user()->id);
    });