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

        <?php
        //QueryFilter.php
        namespace App\Http\Filters\V1;

        use Illuminate\Database\Eloquent\Builder;
        use Illuminate\Http\Request;
        
        abstract class QueryFilter {
            protected $builder;
            protected $request;
            protected $sortable = [];
        
            public function __construct(Request $request)
            {
                $this->request = $request;
            }
        
            public function apply(Builder $builder) {
                $this->builder = $builder;
        
                foreach($this->request->all() as $key => $value) {
                    if (method_exists($this, $key)) {
                        $this->$key($value);
                    }
                }
        
                return $builder;
            }
        
            protected function filter($arr) {
                foreach($arr as $key => $value) {
                    if (method_exists($this, $key)) {
                        $this->$key($value);
                    }
                }
        
                return $this->builder;
            }
        
            protected function sort($value) {
                $sortAttributes = explode(',', $value);
        
                foreach($sortAttributes as $sortAttribute) {
                    $direction = 'asc';
        
                    if (strpos($sortAttribute, '-') === 0) {
                        $direction = 'desc';
                        $sortAttribute = substr($sortAttribute, 1);
                    }
        
                    if (!in_array($sortAttribute, $this->sortable) && !array_key_exists($sortAttribute, $this->sortable)) {
                        continue;
                    }
        
                    $columnName = $this->sortable[$sortAttribute] ?? null;
        
                    if ($columnName === null) {
                        $columnName = $sortAttribute;
                    }
        
                    $this->builder->orderBy($columnName, $direction);
                }
            }
        }

        
    //TicketFilter.php
    namespace App\Http\Filters\V1;
    
    class TicketFilter extends QueryFilter {
        protected $sortable = [
            'title',
            'status',
            'createdAt' => 'created_at',
            'updatedAt' => 'updated_at'
        ];
    
        public function createdAt($value) {
            $dates = explode(',', $value);
    
            if (count($dates) > 1) {
                return $this->builder->whereBetween('created_at', $dates);
            }
    
            return $this->builder->whereDate('created_at', $value);
        }
    
        public function include($value) {
            return $this->builder->with($value);
        }
    
        public function status($value) {
            return $this->builder->whereIn('status', explode(',', $value));
        }
    
        public function title($value) {
            $likeStr = str_replace('*', '%', $value);
            return $this->builder->where('title', 'like', $likeStr);
        }
    
        public function updatedAt($value) {
            $dates = explode(',', $value);
    
            if (count($dates) > 1) {
                return $this->builder->whereBetween('updated_at', $dates);
            }
    
            return $this->builder->whereDate('updated_at', $value);
        }
    
        
    }

//model
  public function scopeFilter(Builder $builder, QueryFilter $filters) {
        return $filters->apply($builder);
    }
//controller
  public function index(TicketFilter $filters)
    {
        return TicketResource::collection(Ticket::filter($filters)->paginate());
    }

Answer 2

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.