条件表连接 [英] 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 $ c选择
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屋!