How to query users company id on event?

I have a company owner role for users. I want to display the companies users events/appointments to the company owner role. Events can have a user_id, and then another type of event has many users for the event.

How can I make this concept in eloquent:

if (Auth::user()->hasRole('company')) {
            $company = DB::select('select * from companies where user_id = ?', [Auth::id()]);
$events = Event::whereHas('users', function ($query) {
                $query->whereUserId(Auth::id())->where('company_id', '=', $company->id);
            })->orWhere('user_id', Auth::id())->where('company_id', '=', $company->id)->where('start_date', '>=', Carbon::now('America/Chicago'))->orderBy('start_date', 'asc')->get();
}

The table company_user holds the user_id and company_id user model

public function company()
{
  return $this->belongsTo('App\Company');
}

Thanks

MarianoMoreyra

5 years ago

Level 25

Hi @adgower

I'm not sure if I understand your question, but it seems to me that you are trying to have some sort of Multi-Tenancy in place?

If that's the case, there are some packages that will make that easy.

Or you can follow this tutorial (from Povilas Korop) to have a basic multi tenancy model: https://youtu.be/nCiNqboYFVQ

Basically, you have to create a Multitenantable Trait with something like this:

trait Multitenantable {
    public static function bootMultitenantable() {
            static::creating(function($model) {
                $company = Auth::user()->company;
                $model->company_id = $company->id;
            });

            static::addGlobalScope('belongs_to_company_id', function(Builder $builder) {
                $company = Auth::user()->company;

                 return $builder->where('company_id', $company->id);
            });
    }
}

And the use that Trait on any model that you want to automatically filter by the User's Company ID. This will also automatically assign the User's Company ID for any new instance of the Multitenantable Models

Adgower

5 years ago

Best Answer

Level 14

This works, but is there a more efficient way?

$company = Company::whereHas('user', function ($query) {
                $query->where('user_id', Auth::id());
            })->first();

            $course_events = Event::has('courses')->whereHas('users.companies', function ($query) use ($company) {
                $query->where('company_id', $company->id);
            });

            $events = Event::has('services')->whereHas('user.companies', function ($query) use ($company) {
                $query->where('company_id', $company->id);
            })->union($course_events)->where('start_date', '>=', Carbon::now('America/Chicago'))->orderBy('start_date', 'asc')->get();

Please or to participate in this conversation.