laravel跨库查询

一、SQL语句

跨数据库：env(‘LARGE_DATABASE’)

            $sql = "
                    SELECT b.operator,count(DISTINCT a.id) qty FROM proofing_notice a JOIN proofing_notice_operator b ON a.id = b.notice_id
                    LEFT JOIN deve_color c on a.color_id = c.id
                    LEFT JOIN ". env('LARGE_DATABASE') ."large_color d on a.color_id = d.id
                    WHERE a.id= b.notice_id and a.status = ". $status_for_sql ." AND b.flag = ". $flag_for_sql ." AND a.area_id in (" .$place_from_str.")
                    AND a.deleted_at IS NULL
                    AND c.deleted_at IS NULL
                    AND d.deleted_at is NULL
            ";
            if ($status_for_sql == 9) {
                $sql .= " AND !(a.paper_type_id =0 AND a.delivery_date IS NULL) GROUP BY b.operator";
            }else{
                $sql .= "  GROUP BY b.operator";
            }
            $nodo = DB::select($sql);

二、laravel模型

跨数据库 LARGE_DATABASE： from(DB::raw(env(“LARGE_DATABASE”)

$grid = new Grid(new ProofingNotice());
        $grid->model()->with(['sizegroup','notice_cad.user_name','notice_operater.user_name','area',
            'notice_node_new'=>function($q){
                $q->with(['deve_sales_name','deve_aid_name','deve_buyer_name']);
            },
            //开发
            'exploit'=>function($q) {
                $q->with(['client','brand', 'quarters','client_branch','devedep']);
            },
            'colors'=>function($q){
                $q->with(['place','devecert', 'sizegroup']);
            },
            'colors_with_trashed'=>function($q){
                $q->with(['place','devecert', 'sizegroup']);
            },
            //大货
            'largesizegroup', 'large_styles'=>function($q){
                $q->with(['quarters','client','cate','deve_style']);
            },
            'large_colors'=>function($q){
                $q->with(['order'=>function($qa){
                    $qa->with(['client_dept','brand','size_group']);
                }]);
            },
            'large_colors_with_trashed'=>function($q){
                $q->with(['order_with_trashed'=>function($qa){
                    $qa->with(['client_dept','brand','size_group','deve_style']);
                }]);
            }])
            ->whereIn('area_id',$area_id);
        //查询
        if($search) {
            //有查询条件
            $grid->model()->where('status','>',$status1);
            $grid->model()->where('status','<',18);
            //查开发
            $grid->model()->whereHas('exploit',function ($q) use($search){
                $q->where('exploit_no', 'like', "%{$search}%")->orWhere('style_name', 'like', "%{$search}%");
            });
            $grid->model()->orWhereHas('colors_with_trashed',function ($q) use ($search){
                $q->where('color_no', 'like', "%{$search}%");
            });
            //查大货
            $grid->model()->orWhereHas('large_styles',function ($q) use ($search){
                $q->from(DB::raw(env("LARGE_DATABASE")."large_style"))->where(env("LARGE_DATABASE").'large_style.large_no', 'like', "%{$search}%");
            });
            $grid->model()->orWhereHas('large_colors_with_trashed',function ($q) use ($search){
                $q->from(DB::raw(env("LARGE_DATABASE")."large_color"))->where(env("LARGE_DATABASE").'large_color.color_no', 'like', "%{$search}%");
            });
            $grid->model()->orWhereHas('large_colors_with_trashed',function ($q) use ($search){
                $q->from(DB::raw(env("LARGE_DATABASE")."large_color"));
                $q->whereHas('order_with_trashed',function($qa) use ($search){
                    $qa->from(DB::raw(env("LARGE_DATABASE")."large_order"))->where(env("LARGE_DATABASE").'large_order.large_name', 'like', "%{$search}%");
                    $qa->from(DB::raw(env("LARGE_DATABASE")."large_order"))->orWhere(env("LARGE_DATABASE").'large_order.order_no', 'like', "%{$search}%");
                });
            });
        }
        else{
            //没有查询条件
            if ($tag == 1){
                $grid->model()->whereIn('status',$status2);
                if($type == 11){
                    $grid->model()->where(function ($q){
                        $q-> where('paper_type_id', 1);
                        $q->orWhere(function ($qa){
                            $qa->where('paper_type_id', 0);
                            $qa->whereNotNull('delivery_date');
                        });
                    });
                }
            }
            else{
                //技术部审批
                $grid->model()->whereIn('state',$status2);
            }
        }