Laravel Eloquent limiting result per user - date combination

Question

In my database, i got the following table with the following structure:

[taskdate_user]
id : int
user_id : int
taskdate_time : datetime

If i got, for example, 2 rows with the following data:

id : 1
user_id : 1
taskdate_time : 2013-01-25 10:15

id : 2
user_id : 1
taskdate_time : 2013-01-25 11:15

I need to filter out the earliest time for the user and taskdate_time combination. I got the following query right now :

// Get all the TaskdateUser objects for this week
$taskdateuser = TaskdateUser::where('taskdate_time', '>=', $this_monday)->where('taskdate_time', '<', $this_sunday)->order_by('taskdate_time', 'ASC')->get();

This will get me all TaskdateUser objects for the current week, but it does return all of them. Right now, i am ordering by taskdate_time and i'm doing that ascending. So i pick each first object for the user_id - taskdate_time combination.

This seems like a lot of extra code to filter out the first user_id - date combination. Does anybody has a suggestion to simplify this? Many thanks in advance!

EDIT 2 :

I need the earliest time, per user, per date.

I've edited my query in the following way :

    $taskdateuser = TaskdateUser::where('taskdate_time', '>=', $monday)
                                ->where('taskdate_time', '<', $sunday)
                                ->raw_where('id IN 
                                    (
                                        SELECT id
                                        FROM taskdate_user 
                                        GROUP BY date_format(taskdate_time, "%Y-%m-%d"), user_id
                                     )')
                                ->order_by('taskdate_time', 'ASC')
                                ->get();

If i echo all the taskdate_times, the output is as follows :

2013-01-21 14:14:00
2013-01-21 17:47:02
2013-01-25 10:56:00
2013-01-26 17:56:41

I have multiple taskdate_time's on 2013-01-26 and 2013-01-25 which will show up only 1 time. And the 2013-01-21 always shows up 2 times. Why is this?

http://paste.laravel.com/fZM – Jack Sierkstra Jan 28 '13 at 13:11 — Jack Sierkstra, Jan 28 '13 at 13:11

score 2 · Accepted Answer · edited May 23 '17 at 12:21

2

Get the latest date from grouped MySQL data

You will have to use RAW queries, but if I understand what you want, that should help you.

Something like:

$taskdateuser = DB::query(SELECT `id`,`user_id`,`taskdate_time` FROM `taskdate_user` WHERE `taskdate_time` IN (SELECT max(`taskdate_time`) FROM `taskdate_user`));

edited May 23 '17 at 12:21

Community

1
1

answered Jan 26 '13 at 07:51

Nicekiwi

4,567
11
49
88

That is a limit, it will limit the result to one object. And that is definitely not what i am looking for. – Jack Sierkstra Jan 26 '13 at 08:22
@Jack, updated my answer. You'd get better responses if you added the MySQL tag to your question too. – Nicekiwi Jan 26 '13 at 11:56
Ezra, thank you but this isn't really the answer either. I need the taskdate_time and user_id combination to be unique. this only returns the first taskdate_time. So if i have more users, the earliest time of that user must be shown. – Jack Sierkstra Jan 28 '13 at 07:03
how do you mean unique? There is only what exists in your database, that will get the you latest tasktime. What else did you want? – Nicekiwi Jan 28 '13 at 08:27
Ahh, so earliest time, per user per date? – Nicekiwi Jan 28 '13 at 09:46
Sorry, i do receive the right results out of the query. I didn't realize the two dates belongs to different user_id's. – Jack Sierkstra Jan 29 '13 at 09:32

Laravel Eloquent limiting result per user - date combination

1 Answers1