Laravel 5.4原始联接查询 [英] Laravel 5.4 Raw Join Query

问题描述

我有一个表 TBL_POST 用于存储博客文章.帖子可以分配给多个类别，其中有一列 cat_id 以逗号分隔的模式(例如 2,4,6 )存储类别ID.我想在此行中使用 FIND_IN_SET()方法

I have a table TBL_POST used to store blog posts. A post can be assigned to multiple categories, there is a column, cat_id that stores category ID's in comma separated pattern like 2,4,6. I want to use FIND_IN_SET() method in this line

->leftJoin(TBL_CAT.' as c', 'p.cat_id', '=', 'c.id')

以显示相关的类别名称.我该怎么办?

to show the associated category names. How can I do that?

public static function getPostWithJoin($status="")
{
    $query = DB::table(TBL_POST .' as p')
            ->select('p.id','p.post_title','p.post_status','u.name as author','c.name as cat_name','p.updated_at')
            ->leftJoin(TBL_ADMINS.' as u', 'p.post_author', '=', 'u.id')
            ->leftJoin(TBL_CAT.' as c', 'p.cat_id', '=', 'c.id')
            ->where('p.post_type','post');
    if($status!="all") {
        $query->where('p.post_status',$status); 
    }

    $query->orderby('p.id','DESC');
    $data = $query->paginate(20);

    return $data;       
}

推荐答案

您可以使用回调创建更复杂的联接查询.

You can use callback to create more complicated join query.

->leftJoin(TBL_CAT, function($query){
     $query->on(TBL_CAT.'id', '=', 'p.cat_id')->where("**", "**", "**");
})

这是laravel文档上的链接- https://laravel.com/docs/5.4/query#joins 高级连接子句"部分.

Here is link on laravel doc - https://laravel.com/docs/5.4/queries#joins "Advanced Join Clauses" section.

UPD ::正如评论中提到的，对于此类数据使用字符串不是一个好主意.因为相等搜索比字符串检查要简单得多.即使您的数据量没有太大差异，您也不知道将来应用程序会发生什么.

UPD:: As mentioned in comment it is not good idea to have string for such types of data. Cause search by equality should be much simpler than string check. Even if your amount of data should not have big difference, you never know what will happen with your app in future.

但是，如果您仍然想这样做，我想您可以尝试这样

But if you still want to do that i think you can try like this

->leftJoin(TBL_CAT, function($query){
     $query->where(DB::raw("FIND_IN_SET(".TBL_CAT.".id, p.cat_id)"), "<>", "0");
})

将检查cat_id中是否存在id的联接.

Join that will check existence of id in cat_id.

这篇关于Laravel 5.4原始联接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！