Combine multiple queries (Laravel 5)

Question

I try to make a search engine for users. The search will be with multiple fields so as the user can be selecting whatever he want and get the result.

routes.php:

Route::get('search/{tag?}/{town?}/{education?}/{contract?}', 'DisplayJobs@getSearch');

DisplayJobs.php Controller

    public function getSearch($tag = null, $town = null, $education = null, $contract = null)
{
    //get already database values to send them to the form
    $tags = \App\Tag::lists('name', 'id');
    $contract = \App\Contract::lists('name', 'id');
    $towns = \App\Town::lists('name', 'id');
    $education = \App\Education::lists('name', 'id');

    $tagQueryBuilder = Tag::query();
    $townQueryBuilder = Town::query();
    $educationQueryBuilder = Education::query();
    $contractQueryBuilder = Contract::query();

    if(Input::has('tag'))
    {
        $tagQueryBuilder->TagOfUser(Input::get('tag'));
    }
    if(Input::has('town'))
    {
        $townQueryBuilder->TownOfUser(Input::get('town'));
    }
    if(Input::has('education'))
    {
        $educationQueryBuilder->EducationOfUser(Input::get('education'));
    }
    if(Input::has('contact'))
    {
        $contractQueryBuilder->ContactOfUser(Input::get('contact'));
    }


    return view('main.search_jobs', compact('tags', 'towns', 'contract', 'education'));

}

If I try with each single query it works perfectly but I want to combined result data from all the queries or a way to query all the data at once.

In each model I have my query scope like this (Tag.php) Model:

    public function jobs()
{
    return $this->belongsToMany('App\Job');
}

public function scopeTagOfUser($query, $tag)
{
    return $query->where('id', '=', $tag)->with('jobs');
}

Your question is not clear. Do you want the combined result data from all the queries or you want a way to query all the data at once? Also, none of the \App\Something::lists('name', 'id'); work, which makes me wonder, did you even run the code? — JSelser
– JSelser, Commented Oct 7, 2015 at 6:32
Thank you for reply. All of the code \App\Something::lists('name', 'id'); works perfectly, I tried it. I want only to combined result data from all the queries or a way to query all the data at once. — Panagiotis Koursaris
– Panagiotis Koursaris, Commented Oct 7, 2015 at 8:35

Panagiotis Koursaris · Accepted Answer · 2016-01-12 14:40:31Z

After a lot of hours I found a solution. I will post it below so if anyone has the same problem can see one solution.

First I have delete all of the scope queries in the models and all of the work completed to the controller like bellow:

    public function getSearch($tag = null, $town = null, $education = null, $contract = null)
{
    //get already database values to send them to the form
    $tags = \App\Tag::lists('name', 'id');
    $towns = \App\Town::lists('name', 'id');
    $contract = \App\Contract::lists('name', 'id');
    $education = \App\Education::lists('name', 'id');

    //get inputs from users
    $getTagFromUser = Input::get('tag');
    $getTownFromUser = Input::get('town');
    $getContractFromUser = Input::get('contract');
    $getEducationFromUser = Input::get('education');

    $tagQuery = DB::table('jobs')
        ->join('job_tag', 'jobs.id', '=', 'job_tag.job_id')
        ->join('tags', 'job_tag.tag_id', '=', 'tags.id')
        ->where('tags.id', '=', $getTagFromUser);

    $townQuery = DB::table('jobs')
        ->join('job_town', 'jobs.id', '=', 'job_town.job_id')
        ->join('towns', 'job_town.town_id', '=', 'towns.id')
        ->where('towns.id', '=', $getTownFromUser);

    $contractQuery = DB::table('jobs')
        ->join('job_contract', 'jobs.id', '=', 'job_contract.job_id')
        ->join('contracts', 'job_contract.contract_id', '=', 'contracts.id')
        ->where('contracts.id', '=', $getContractFromUser);

    $educationQuery = DB::table('jobs')
        ->join('job_education', 'jobs.id', '=', 'job_education.job_id')
        ->join('education', 'job_education.education_id', '=', 'education.id')
        ->where('education.id', '=', $getEducationFromUser);


    $finalQuery = $tagQuery->union($townQuery)->union($contractQuery)->union($educationQuery)->get();


    return view('main.search_jobs', compact('tags', 'towns', 'contract', 'education', 'finalQuery'));

}

Your answer is extremely helpful for a project I'm currently running and I was stuck -days ago- in the same problem you're describing.

Collectives™ on Stack Overflow

Combine multiple queries (Laravel 5)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related