如果在Laravel Eloquent语句

Question

我有两个模型，通过三个ID连接到一个1 ID。型号AdviceProtocol有三个定义的ID：threshold_category1_id,threshold_category2_id和threshold_category3_id。这三个都与Categories型号的id有关。 threshold_cateogry_id可以是链接到categories.id的数字，也可以是null。

在我的查询中，我需要通过threshold_catogory_ids中的三个，如果它们是NOT NULL，请检索categories.name。我现在的查询：

$advicePreparationsQuery = AdviceProtocol::select(['advice_protocols.name', 'advice_protocols.category', 'questions.name AS question_name', 'categories.name as Category_Name','categories.name as Category_Name2']) 
      ->join('questions', 'advice_protocols.user_goal_id', '=', 'questions.id') 
      ->join('categories', function ($join) 
      { 
       $join->on('advice_protocols.threshold_category1_id', '=', 'categories.id')->orOn('advice_protocols.threshold_category2_id', '=', 'categories.id')->orOn('advice_protocols.threshold_category3_id', '=', 'categories.id'); 
      }) 

此查询现在返回两次相同的category.name。 我试图寻找其他人如何解决这个问题，但我无法找到准确的在线。查询即按名称

Answer 1

我想尝试三连三个不同的别名加入，然后抢名称为每个不同的别名的每个类别名称：

$advicePreparationsQuery = AdviceProtocol::select(['advice_protocols.name', 'advice_protocols.category', 'questions.name AS question_name', 'categories1.name as Category_Name','categories2.name as Category_Name2','categories3.name as Category_Name2']) 
      ->join('questions', 'advice_protocols.user_goal_id', '=', 'questions.id') 
      ->join('categories as categories1', 'advice_protocols.threshold_category1_id', '=', 'categories1.id') 
      ->join('categories as categories2', 'advice_protocols.threshold_category2_id', '=', 'categories2.id') 
      ->join('categories as categories3', 'advice_protocols.threshold_category3_id', '=', 'categories3.id'); 

Answer 2

使用组->groupBy('category.name')

$advicePreparationsQuery = AdviceProtocol::select(['advice_protocols.name', 'advice_protocols.category', 'questions.name AS question_name', 'categories.name as Category_Name','categories.name as Category_Name2']) 
     ->join('questions', 'advice_protocols.user_goal_id', '=', 'questions.id') 
     ->join('categories', function ($join) 
     { 
      $join->on('advice_protocols.threshold_category1_id', '=', 'categories.id')->orOn('advice_protocols.threshold_category2_id', '=', 'categories.id')->orOn('advice_protocols.threshold_category3_id', '=', 'categories.id'); 
     } 
     ->groupBy('category.name'))