Published 2 years ago by lorvent
I have a table where lets say i have following data
Now, i want to get user_id 1's data but i want to get only one row for each date
but here for
2016-07-16 i have 3 rows, i want to select only one.
can i anyone guide me, how to get that?
| id | user_id | value | created_at | updated_at | |---|---|---|---|---| |1 | 1 | 1329 | 2016-07-16 21:37:54 | 2016-07-16 21:37:54 | |2 | 2 | 860 | 2016-07-16 21:37:55 | 2016-07-16 21:37:55 | |3 | 1 | 1329 | 2016-07-16 21:38:53 | 2016-07-16 21:38:53 | |4 | 2 | 860 | 2016-07-16 21:38:53 | 2016-07-16 21:38:53 | |5 | 1 |1329 |2016-07-16 21:39:06 |2016-07-16 21:39:06| |6 | 2 |860|2016-07-16 21:39:06|2016-07-16 21:39:06|
You may want to look into a group by where you select the top date for a user. Have you worked some of the querybuilder examples in documentation? An example of formulating up Advanced query https://laracasts.com/discuss/channels/eloquent/orderby-computed-related-attribute
I'm not entirely sure I understand what you're trying to get, but I think what you're wanting to accomplish is to fetch the last record for each day, and only one per day. Correct? So you don't just want only the one most recent record, but rather the most recent record for each day that has one or more.. yes?
That is a little bit of a complex query, you really need to do a join on a sub-select to get just the largest datetime value of each day, and then group by the day.
Something like this:
$data = DB::table('your_table', 't1') ->join(DB::raw('(SELECT max(updated_at) AS max FROM your_table GROUP BY DATE(updated_at)) AS t2', 't1.updated_at', '=', 't2.max') ->where('user_id', '=', $uid) ->select('id', 'user_id', 'value', 'DATE(updated_at) AS day') ->orderBy('day', 'desc') ->get();
Untested, but I think that should work, at least if your db is mysql/maria/percona may not work right with postgres/sqlite. Obviously change
your_table in both instances to the proper name of your table.
@willvincent exactly, when searching for a user records,
so on a day, if we have 3 rows, i want to pick only one.
Currently, i found a workaround for it
so that i am getting only one row per day.
but this is not efficient because i am getting all rows from database...
my current code looks like this
can you help me write eloquent code for that max? instead of raw queries
No. Either figure it out yourself, by reading the docs (which I what I would have to do), or use it as it is... there's nothing wrong with query builder and db raw.. it lets you do a helluva lot more than eloquent syntax alone, and in some cases (this might be one of them given the subquery join) you simply cannot do everything with eloquent syntax alone.