shez1983's avatar

relationship with a pivot table have a hard time querying

so originally i had a simple Company belongs to country, company has many referrals. then in my INdex, i was displaying one referral per company (based on some criteria)

Now what i want to do is company can belong to many countries. (Ie global/multi national).. so i have added company_country table .. and the referrals ideally should have a company_country_id field.

This is all fine in theory.. but I am having hard time trying to get the referral..

at the moment (pre my changes) i am doing this:

Company::with('country')
                ->withWhereHas('referral')
                ->when(!$showAll && !$this->searchGlobally && getStoredCountry(), function($q){
                    $q->where(function($q){
                        $q->whereCountryId(getStoredCountry()->id)
                            ->orWhereNull('country_id');
                    });
                })
                ->when(!$showAll && $this->type, function($q){
                    $q->whereHas('types', function($q){
                        $q->whereSlug($this->type);
                    });
                })
                ->oldest('name')
                ->get();

what i am trying to do is:

            $query = Company::query()
                ->withWhereHas('referral')
               ->when(!$showAll && $this->type, function($q){
                    $q->whereHas('types', function($q){
                        $q->whereSlug($this->type);
                    });
                });

                if ($this->searchGlobally) {
                    $query->with('randomReferral');
                } else if (!$showAll  && getStoredCountry()) {
                    $query->withWhereHas('countries', function($q){
                        $q->where('country_id', getStoredCountry()->id);
                    })->with('countries.referral');
                }
                $this->results = $query->latest('name')->get();

randomReferral and referrals are both a

For now i am doing a simpler thing, and jsut letting users create duplicate companies if country id is different etc so keeping same simpler schema.

0 likes
0 replies

Please or to participate in this conversation.