优化一下 laravel 的查询语句

Question

Answer 1

$data2 = DB::table('jtgw_oarea as a')->leftJoin('jtgw_oarea_type as c', 'a.tid','=','c.id')
                    ->leftJoin('jtgw_oarea_content as t', 'a.id','=','t.cid')
                    ->select($field)
                    ->where('pub_time','<=',time())
                    ->where('a.is_delete','=',1)
                    ->where(function($query) use ($search) {
                        $query->where('a.title', 'like', '%'. $search.'%')
                              ->orWhere('a.short_title', 'like', '%'. $search.'%'); 
                    })
                    ->orderBy('a.pub_time','desc')
                    ->get()
                    ->toArray();

Answer 2

去掉后面整个orWher，只考虑一个条件的情况下（红框内有两个）,把pub_time那个条件当作a，闭包内的short_time当作c，相当于a ||(a&c),最后的结果取决与a，a为true(查询的到的话),结果为ture,false则结果为false,所以等价于a||(a&c) = a

Answer 3

建立模型关联，然后再辅以合适的scope封装，代码应该可以优雅很多，由于不知道你系统里的有哪些模型和模型之间的关系，所以无法给出具体的优化方案。如果给出模型以及之间的关系，可以优化。

Answer 4

如果你有三个模型相关联，那么这一行代码
DB::table('jtgw_oarea as a')->leftJoin('jtgw_oarea_type as c', 'a.tid','=','c.id') ->leftJoin('jtgw_oarea_content as t', 'a.id','=','t.cid')
就等价于
jtgw_oarea::with('jtgw_oarea_type','jtgw_oarea_content')
这一行代码
且where('a.is_delete', '=', 1)也可以写成where('a.is_delete', 1)
且where('a.title', 'like', '%'. $search.'%')也可以写成where('a.title', 'like', "%$search%")

Answer 5

如果仅仅解决你框中重复的问题，一下代码足够了

$data2 = DB::table('jtgw_oarea as a')->leftJoin('jtgw_oarea_type as c', 'a.tid','=','c.id')
                    ->leftJoin('jtgw_oarea_content as t', 'a.id','=','t.cid')
                    ->select($field)
                    ->where('pub_time','<=',time())
                    ->where('a.is_delete','=',1)
                    ->where(function($query) use ($search) {
                        $query->where('a.title', 'like', '%'. $search.'%')
                              ->orWhere('a.short_title', 'like', '%'. $search.'%'); 
                    })
                    ->orderBy('a.pub_time','desc')
                    ->get()
                    ->toArray();