laravel join

laravel join on多个

$result = DB::connection('report_forms')->table('advertisement')
->join('daohang', 'advertisement.adv_name', '=', 'daohang.channel')
->join('ad', function ($join) {
$join->on('ad.channel_id', '=', 'daohang.channel')->on('ad.date', '=', 'daohang.date');
});

laravel db 查询 toArray

https://stackoverflow.com/questions/37517728/laravel-5-1-dbselect-toarray

$result = array_map(function ($value) {
    return (array)$value;
}, $result);

https://blog.csdn.net/u013032345/article/details/82840583

laravel groupby sum

$reports = $reports->groupby('app_id')->selectRaw('app_id, sum(request_num) as request_num,sum(click_num) as click_num,sum(show_num) as show_num')->get()->toArray();

between and 

if ($start_time && $end_time) {
    $reports = $reports->whereBetween('day', [$start_time, $end_time]);
}

<?php

$users = DB::connection('ssp')->table('throws_codes')
->join('throws_datetime', 'throws_codes.throws_id', '=', 'throws_datetime.throws_id')
->join('throws', 'throws.id', '=', 'throws_codes.throws_id')
->join('throws_assets', 'throws.id', '=', 'throws_assets.throws_id')
->join('assets', 'throws_assets.assets_id', '=', 'assets.id')
->select('throws_assets.assets_id AS assets_id',
'assets.assets_code AS assets_code',
'throws_datetime.start_time AS promote_start_time',
'throws_datetime.end_time AS promote_end_time',
'throws.updated_at AS updated_at',
'throws.priority AS priority',
'throws.id AS throws_id')
->get();
echo "<pre>";
print_r($users);
echo "</pre>";
exit;
$throws_id = $v['id'];
$assets = Throws_assets::join('assets','throws_assets.assets_id','assets.id')
->where('throws_assets.throws_id','=',$throws_id)
->select('assets.id')
->get()
->toArray();

$users = Throws::join('Throws_assets','Throws.id','throws_assets.throws_id')
            ->select('Throws.*','throws_assets.assets_id')
            ->get()
            ->toArray();
?>

CREATE TABLE `assets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `assets_name` varchar(50) DEFAULT NULL COMMENT '名称',
  `assets_size` varchar(50) DEFAULT NULL COMMENT '尺寸',
  `assets_code` text COMMENT '代码',
  `assets_status` tinyint(4) DEFAULT NULL COMMENT '状态',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

CREATE TABLE `throws` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL COMMENT '名称',
  `priority` tinyint(4) DEFAULT NULL COMMENT '优先级',
  `limit` int(11) DEFAULT NULL COMMENT '投放上限',
  `mount` int(11) DEFAULT NULL COMMENT '投放量',
  `frequency_limit` tinyint(1) DEFAULT NULL COMMENT '频次控制',
  `arealist` text,
  `status` tinyint(1) DEFAULT NULL COMMENT '状态',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

CREATE TABLE `throws_assets` (
  `throws_id` int(11) DEFAULT NULL,
  `assets_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `throws_codes` (
  `throws_id` int(11) DEFAULT NULL,
  `codes_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `throws_datetime` (
  `throws_id` int(11) DEFAULT NULL,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

内连接 ( 等值连接 )
$users = Book::join('auth','book.auth_id','auth.id')
->select('book.*','auth.name as auth_name')
->get()
->toArray()
转换成SQL语句 :
select `bl_book`.*, `bl_auth`.`name` as `auth_name` from `bl_book` inner join `bl_auth` on `bl_book`.`auth_id` = `bl_auth`.`id`
左连接
如果你想使用左连接 而不是 内连接 可以使用 left join 用法与join 一样
$users = Book::leftJoin('auth','book.auth_id','auth.id')
->select('book.*','auth.name as auth_name')
->get()
->toArray()
交叉连接
要执行“交叉连接”可以使用 crossJoin方法，传递你想要交叉连接的表名到该方法即可。交叉连接在第一张表和被连接表之间生成一个笛卡尔积：
$users = User::crossJoin('auth')
        ->get();
高级连接语句
你还可以指定更多的高级连接子句，传递一个闭包到join 方法作为第二个参数，该闭包将会接收一个JoinClause 对象用于指定 join 子句约束：
users  = Book::join('auth',function ($join){
                $join->on('book.auth_id','auth.id')
                    ->Where('book.id' , '>','3');
        })
        ->get()
        ->toArray();
联合（Union）
查询构建器还提供了“联合”两个查询的快捷方式，比如，你可以先创建一个查询，然后使用 union 方法将其和第二个查询进行联合：
$user_name = User::select('name');  
$users = User::select('password')->union($user_name)->get(); 
注：unionAll 方法也是有效的，并且和 union 使用方式相同。