Trying to add a join to a model query?

I've got this model that query's my volunteer table, I'd like to relate it to my job_positions table

    public static function search($query)
    {
        
        return empty($query) ? static::query()
            : static::where('related_experience', 'like', '%'.$query.'%')
                ->orWhere('firstname', 'like', '%'.$query.'%')
                ->orWhere('lastname', 'like', '%'.$query.'%');
    }

I can't figure out how to search a query with a join rather than just a single table... something like this

        $applicants = \DB::table('volunteerapplicants as v')
        ->join('joblistings as j', 'v.joblisting_id', '=', 'j.id')
        ->get();

for reference here is my livewire controller


    public function render()
    {
        
        return view('livewire.volunteer-applicants', [
            'applicants' => \App\VolunteerApplicants::search('%'.$this->search.'%')
                ->orderBy($this->sortField, $this->sortAsc ? 'asc' : 'desc')
                ->paginate($this->perPage),
        ]);
    }

squiaios

5 years ago

Level 1

Be careful, you wrap your query twice with "%"

You can check this part of laravel documentation : https://laravel.com/docs/7.x/eloquent-relationships#querying-relationship-existence

jlrdw

5 years ago

Level 75

Have you tried something like

    public static function search($query, $search = "")
    {
        
        Yourquery.....
         where('related_experience', 'like', '%'.$search.'%')
                ->orWhere('firstname', 'like', '%'.$search.'%')
                ->orWhere('lastname', 'like', '%'.$search.'%')->get();

    // And just return the data
    }

Is static even working.

Return something simpler and see if it works, like one known thing.

I use a scope like:

    public function scopegetPets($query, $petsearch = '')
    {
        $petsearch = $petsearch . "%";
        $query->where('petname', 'like', $petsearch);
        if (ChkAuth::userRole('admin') === false) {  // ignore, custom rbac used
            $userid = Auth::user()->id;
            $query->where('ownerid', '=', $userid);
        }
        $results = $query->orderBy('petname', 'asc')->paginate(5);
        return $results;
    }

If no criteria entered for petsearch, all is displayed. C entered, pets name starts with C, etc

Aron-Spiess

5 years ago

Best Answer

Level 1

yes my model / controller return what I expect, but I'd also like the title of my job listing.

I figured it out, just needed to add my join in the livewire controller

        return view('livewire.volunteer-applicants', [
            'applicants' => \App\VolunteerApplicants::search($this->search)
            ->join('joblistings', 'volunteerapplicants.joblisting_id', '=', 'joblistings.id')
                ->orderBy($this->sortField, $this->sortAsc ? 'asc' : 'desc')
                ->paginate($this->perPage),
        ]);

Please or to participate in this conversation.