条件表连接 [英] Conditional table joins

问题描述

我在我的雄辩模型中有以下范围，我想添加两个条件。我需要帮忙。

  public function scopeImages（$ query）{
 $ query-> join（'images as i'，function （$ join）{
 $ join-> on（'i.vessel_id'，'='，'vessel.id'）
  - >其中（'i.sort'，'=' ，'min（i.sort）'）; 
}）
  - > leftjoin（'users'，'users.id'，'='，'vessel.user_id'）
 - > select（'vessel。*'，'i.image as image'，'users.name as brokername'）; 
} 
  

图像表有特色和排序列。我想选择一行，其中 images.featured是返回结果的1 和 min sort 。如果没有 images.featured = 1 然后我想选择 min的排序。

目前，上述范围为每个 vessel_id min sort 的图像$ c>

解决方案

您需要一个相关的子查询

https://dev.mysql.com/doc/refman/5.5/en /correlated-subqueries.html

  SELECT v。*，
（SELECT`image` FROM images WHERE vessel_id = v.id ORDER BY feature DESC，sort LIMIT 1）AS image 
 FROM`vessel` AS v 
  

@EDIT
你可以尝试一下：

  public function scopeImages（$ query） {
 return $ query 
  - > leftjoin（'users'，'users.id'，'='，'vessel.user_id'）
  - > select（\DB： ：raw（vessel。*，（SELECT`image` FROM images WHERE vessel_id = vessel.id ORDER BY features DESC，sort LIMIT 1）AS image，users.name as brokername））; 
} 
  

I have following scope in my eloquent model and I want to add two conditions to it. I need help in doing it.

public function scopeImages($query) {
    $query->join('images as i', function ($join) {
        $join->on('i.vessel_id', '=', 'vessel.id')
        ->where('i.sort', '=', 'min(i.sort)');
    })
    ->leftjoin('users', 'users.id', '=', 'vessel.user_id')
    ->select('vessel.*', 'i.image as image', 'users.name as brokername');
}

images table has featured and sort columns. I want to select one row where images.featured is 1 and min sort of the returned results. If there are no images.featured=1 then I want to select min of sort.

Currently the above scope selects image of min sort for each vessel_id

解决方案

You need a correlated subquery

https://dev.mysql.com/doc/refman/5.5/en/correlated-subqueries.html

    SELECT  v.*, 
(SELECT `image` FROM images WHERE vessel_id = v.id ORDER BY featured DESC, sort LIMIT 1) AS image
    FROM `vessel` AS v

@EDIT You Can give this a try:

public function scopeImages($query) {
    return $query
        ->leftjoin('users', 'users.id', '=', 'vessel.user_id')
        ->select(\DB::raw("vessel.*, (SELECT `image` FROM images WHERE vessel_id = vessel.id ORDER BY featured DESC, sort LIMIT 1) AS image, users.name as brokername"));
} 

这篇关于条件表连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！