php - Eloquent join table on date

Question

点击表架构:

user_id as (integer)
created_at as (Y-m-d H:i:s)

View 表

user_id as (integer)
created_at as (Y-m-d H:i:s)

我需要按日期而非日期时间对第一个表 + 第二个表中的记录进行计数:

现在我正在使用 2 个查询:

$clicks = $user->clicks()
->select(\DB::raw("DATE_FORMAT(created_at, '%d/%m/%Y') as date"),
        \DB::raw("COUNT(*) as clicks"))
->groupBy('date')
->orderBy('date', 'ASC')
->get()->toArray();

$views = $user->views()
->select(\DB::raw("DATE_FORMAT(created_at, '%d/%m/%Y') as date"),
    \DB::raw("COUNT(*) as views"))
->groupBy('date')
->orderBy('date', 'ASC')
->get()->toArray();

在我有一个循环之后

$out = [];
    for ($i=0; $i < count($views); $i++) { 
        $out[] = [
            'date' => $views[$i]['date'],
            'clicks' => $clicks[$i]['clicks'],
            'views' => $views[$i]['views']
        ];
    }

但是如果点击存在并且在某个日期没有查看它不会将其添加到数组中可以按日期格式加入 created_at 吗？没有时间？

Answer 1

因为 MySQL 不支持 full join 试试这个

select date(clicks.created_at)as 'Date',
count(clicks.user_id) as 'clicks',
count(views.user_id)as 'views'
from clicks
left join views on date(views.created_at)=date(clicks.created_at)
group by date(clicks.created_at)

union

select date(views.created_at)as 'Date',
count(clicks.user_id) as 'clicks',
count(views.user_id)as 'views'
from views
left join clicks  on date(views.created_at)=date(clicks.created_at)
group by date(views.created_at)

因为你在查询中有 count 试试这个查询

select a.Date,a.clicks,b.views
 from(select date(clicks.created_at)as 'Date',
       count(clicks.user_id) as 'clicks'
       from clicks
       group by date(clicks.created_at))a
 left join (select date(views.created_at)as 'Date',
       count(views.user_id) as 'views'
       from views
       group by date(views.created_at))b
 on a.date=b.date    

union

select a.Date,b.clicks,a.views
 from(select date(views.created_at)as 'Date',
       count(views.user_id) as 'views'
       from views
       group by date(views.created_at))a
 left join (select date(clicks.created_at)as 'Date',
       count(clicks.user_id) as 'clicks'
       from clicks
       group by date(clicks.created_at))b
 on a.date=b.date