i have a table called visitors :
visitors : (id , user_id , ip , created_at , route , refer )
i store a row for each page visit in the middleware wrapping my links
i want to show these in my admin panel but s you imagine there are a lot of them , so i want to show first visit of each day for each user like if i have this in db
1 , 356 , 127.0.0.1 , 2017-08-30 22:41:49
1 , 356 , 127.0.0.1 , 2017-08-30 22:50:49
1 , 356 , 127.0.0.1 , 2017-08-30 22:53:49
1 , 356 , 127.0.0.1 , 2017-08-30 23:11:49
1 , 400 , 127.0.0.1 , 2017-08-30 23:50:49
the query result should be
1 , 356 , 127.0.0.1 , 2017-08-30 22:41:49
1 , 356 , 127.0.0.1 , 2017-08-30 23:11:49
1 , 400 , 127.0.0.1 , 2017-08-30 23:50:49
so what i want is something like
DISTINCT(DATE(created_at))
but this will effect all users .. i want it to effect each user
its very complicated im not sure how should i go about this
I suppose you can try this way:
DB::table('visitors')
->distinct('created_at')
->orderBy('created_at', 'asc')
->get();
Another way is to have additional field for User entity like fisrt_visit if it's important
As mentioned, you want to use the MIN function to get the first time they logged in, with GROUP BY to group it by the user id:
DB::table('visitors')
->select(['user_id', DB::raw('MIN(created_at)')])
->groupBy('user_id')
->get();
You can add in the other columns if you want, but the key is DB::raw('MIN(created_at)') which will get the earliest created_at timestamp for each user. The DB::raw will pass in that string exactly as you specify.
Try this
DB::table('visitors')->select('id','user_id','ip','created_at')->groupBy(DB::raw('DATE(created_at)'))->groupBy('user_id')->get();
This will give unique users in a day.