Laravel query and filtering

Question

I'm trying to work out the best approach to filtering for this query, I've tried a few different things but can't come up with a solid solution.

Essentially we have the general query which is fine does exactly what it needs to, the problem is I need to be able to filter against user input on an answers table.

$profiles = User::with('photos', 'answers')
        ->where('id', '!=', $user_id)
        ->where('gender', $gender)
        ->where('location', $location)
        ->where('deleted_at', null)
        ->whereNotExists(function($query) use ($user_id)
        {
            $query->select(DB::raw('user_id1, user_id2'))
                                    ->from('approves')
                                    ->whereRaw("users.id = approves.user_id2 AND approves.user_id1 = '$user_id'");
        })
        ->whereNotExists(function($query) use ($user_id)
        {
            $query->select(DB::raw('user_id1, user_id2'))
                    ->from('likes')
                    ->whereRaw("users.id = likes.user_id2 AND likes.user_id1 = '$user_id'");
        })
        ->take(15)
        ->get();

That uses a couple of user inputs to alter the query, now the user can also filter by a variety of other criteria against a user's profile answers which is where I'm stuck.

The answers table's layout is id, user_id, question_id, answer it had to be like this to allow expansion later on.

Does anyone have any idea how I could filter against this with various other inputs for example if a user was filtering by question_id '1' and answer 'awesome'. Notably there are multiple inputs not just one to compare against and they only need comparing if they've been entered.

Any thoughts or advice is greatly appreciated :)

Edit:

id | user_id | question_id | answer
1  | 2       | 1           | dad
2  | 2       | 2           | lion
3  | 2       | 3           | 5

Solution

For multiple possible question/answer pairs, I would define a query scope. Now this is not a particularly efficient query, and someone else may have a better answer, but this is how I would do it.

$profiles = User::with('photos', 'answers')
    ->where('id', '!=', $user_id)
    ->where('gender', $gender)
    ->where('location', $location)
    ->where('deleted_at', null)
    ->questionAnswer($questionAnswerArray)
    ->whereNotExists(function($query) use ($user_id)
    {
        $query->select(DB::raw('user_id1, user_id2'))
                                ->from('approves')
                                ->whereRaw("users.id = approves.user_id2 AND approves.user_id1 = '$user_id'");
    })
    ->whereNotExists(function($query) use ($user_id)
    {
        $query->select(DB::raw('user_id1, user_id2'))
                ->from('likes')
                ->whereRaw("users.id = likes.user_id2 AND likes.user_id1 = '$user_id'");
    })
    ->take(15)
    ->get();

Then inside the User model:

public function scopeQuestionAnswer($query, $qaArray)
{
    foreach($qaArray as $question => $answer)
    {
        $query->whereHas('answers', function($query) use ($question, $answer)
        {
            $query->whereQuestionId($question)
                  ->whereAnswer($answer);
        });
    }

    return $query;
}

This uses an array of parameters with where array($question => $answer) but should be easily modified to however you would prefer to pass them in.

I tested this usage and it works quite well, but again I can't speak to its efficiency. Not that this solution will work if you need to filter by all the correct question/answer pairs, you can use 'orWhereHas' in the scope function to filter for any of them.

Comments

[SteveTHitchman]

Hey @akrist that sounds about right, forgive me if it's a stupid question but how would the whereQuestionId and whereAnswer work? and would this need to be repeated for multiple parameters? so if there was question id 1, answer Awesome and question id 2, answer London would this still work? Just trying to make complete sense of it really, thanks for the input

[akrist]

Hi steee, I'm sorry I misinterpreted your question slightly, I didn't realize you'd want to be able to filter on multiple questions/answer simultaneously. Please see my updated answer.

[SteveTHitchman]

Hey @akrist that worked perfectly a minor alteration and it's spot on, thanks for the help like you say I may need to look into optimising it later on but it does exactly what I need it to do :)