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

Answer 1

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.