Get distinct dates data

Published 1 year ago by lorvent

I have a table where lets say i have following data

https://gist.github.com/lorvent/f8ff99e5af58903983b054b8234e3d78

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?

thanks.

| 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|

jlrdw
jlrdw
1 year ago (202,050 XP)

Limit 1 for that date.

lorvent
lorvent
1 year ago (13,385 XP)

i mean, how?

what is the syntax?

jlrdw
jlrdw
1 year ago (202,050 XP)

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

willvincent

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.

jlrdw
jlrdw
1 year ago (202,050 XP)

@willvincent I think they won't all the users and first date for all users kind of like a loop but I kind of misunderstood also.

willvincent

I dunno, @lorvent specifically said "user_id 1"

But really all of that query builder stuff could be setup as a relationship to a user.

jlrdw
jlrdw
1 year ago (202,050 XP)

Missed that, but should have a good starting point now.

lorvent
lorvent
1 year ago (13,385 XP)

@willvincent exactly, when searching for a user records,

  • i want to get only one record per day (as per created_at column)
  • which is most recent

so on a day, if we have 3 rows, i want to pick only one.

Currently, i found a workaround for it

  1. get all data from database
  2. store it into new array while formatting created_at to Y-m-d format
  3. applying array_unique($final_array,SORT_REGULAR)

so that i am getting only one row per day.

but this is not efficient because i am getting all rows from database...

jimmck
jimmck
1 year ago (67,625 XP)

As long as its a TIMESTAMP. Just get the max(created_at).

willvincent

max() should work on any date type field.. timestamp, date, datetime..

jimmck
jimmck
1 year ago (67,625 XP)

@willvincent Yes. The milli's give resolution. Oh and the App Cache nod was great. Thanks again!

lorvent
lorvent
1 year ago (13,385 XP)

@willvincent @jimmck can you help me write eloquent code for that max? instead of raw queries

my current code looks like this

$arr = [];

        $competitor = Competitor::findOrFail($id);
        $competitorData = CompetitorSaleCount::where('competitor_id',$competitor->id)
            ->get();
        foreach ($competitorData as $competitorDataObject) {
            $newArray = [];
            $newArray[] = $competitorDataObject->created_at->format('Y-m-d');
            $newArray[] = $competitorDataObject->sales;


            array_push($arr, $newArray);
        }
//        return collect($arr)->unique()->values();
//        return array_unique($arr,SORT_REGULAR);
        JavaScript::put([
            'foo' => 'bar',
            'user' => 'seenu',
            'age' => 29,
            'data' => collect($arr)->unique()
        ]);
        return view('admin.competitors.show', compact('competitor','competitorData'));

lorvent
lorvent
1 year ago (13,385 XP)

I am also thinking, is it better to store created_at (or other field) in just Y-m-d format so that i can get unique values easily?

willvincent

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.

lorvent
lorvent
1 year ago (13,385 XP)

lol ok

Please sign in or create an account to participate in this conversation.