Laravel 查询和过滤

Laravel query and filtering

提问by SteveTHitchman

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.

答案表的布局是id，user_id，question_id，答案必须是这样才能以后扩展。

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.

有谁知道我如何使用其他各种输入进行过滤，例如，如果用户通过 question_id '1' 过滤并回答 'awesome'。值得注意的是，有多个输入，而不仅仅是一个要比较的输入，它们只需要在输入后进行比较。

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

采纳答案by akrist

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:

然后在 User 模型中：

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.

这使用了一个带有 where array($question => $answer) 的参数数组，但应该很容易修改为但是你更喜欢传递它们。

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.

我测试了这种用法，效果很好，但我又不能说它的效率。并不是说如果您需要按所有正确的问题/答案对进行过滤，此解决方案将起作用，您可以在范围函数中使用“orWhereHas”来过滤其中任何一个。