时间戳

首页 > 解决方案 > Advanced Laravel Query

sql - Advanced Laravel Query

问题描述

Here is my Schema:

The User model is normal.

Question
  - team_id
  - question_body

Answer
  - user_id
  - question_id
  - order
  - is_private

A user can have a primary answer for a question which is essentially the answer that has the lowest order and is public.

I need to get the primary answer of a question for each user except the authenticated user. I have tried multiple ways but can't seem to figure it out. Here is what I have so far on the Question model:

public function topAnswerForEachMember()
{
    return $this->load(['answers' => function($query) {
        return $query->public()
            ->where('user_id', '!=', Auth::user()->getKey());
    }]);
}

This returns all public answers to the question not owned by the auth user. Now from here I need to get the one with the lowest order from each user. I have tried groupBy but am getting aggregate errors for that. I'm guessing this is going to require a sub query of sorts but I am unfamiliar with those. If anyone has an idea it would be much appreciated. Thanks!

标签： sqldatabaselaravelpostgresqleloquent

解决方案

如果我了解您要做什么，我认为如果您从相反的角度进行操作，这可能是最简单的。我会使用用户模型并从那里提取你的答案。这是一个更简单的查询。

我不确切知道您的关系是什么，或者该public()方法的作用是什么，但是这样的事情应该接近您的意图：

$users = User::whereHas('answers', function($query) use($question_id){
   $query->where('question_id', $question_id)
         ->orderBy('order', 'asc')->first();
})
  ->where('id', "!=", Auth::user()->id)
  ->get();

从这里开始，user该集合中的每一个都应该有一个单一的、最低的排序answer到question您正在查看的内容。如果您还希望获得多个答案，您也可以替换limit()。first()

推荐阅读