Froxz Froxz - 1 month ago 13
MySQL Question

Eloquent join table on date

Clicks table schema:

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


Views table

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


I need to count records in 1st table + 2nd table group by date NOT datetime:

Right now I am using 2 queries:

$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();


And after I have a loop

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


But this way if click exist and view no on some date it will not add it toarray
It is possible to join on created_at by date format? without time?

Answer

since MySQL not supported full join try this

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)

since you have count in your query try the this query

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