2

Is there a way I could retrieve only one record from date records with the same value in the collection?

My records goes like this,

id | date       | event_name   | type
----------------------------------------
1  | 2016-01-23 | event 1      | event 1
2  | 2016-01-23 | event 1      | event 2
3  | 2016-03-15 | event 2      | event 1
4  | 2016-03-15 | event 2      | event 2

So as you can see, there are records with same date and I want to fetch only one on that same date records. So I expect to get,

2016-01-23, event 1
2016-03-15, event 2

I tried,

$events = events::select( events.*',
            DB::raw('(select date from events where date = events.date) as events'))
            ->get();

but unfortunately its not working. It gives me an empty array from

dd(var_dump($events->toArray()));

Any ideas, help please?

Hari Harker
  • 702
  • 1
  • 12
  • 29
Juliver Galleto
  • 8,831
  • 27
  • 86
  • 164

3 Answers3

2

The actual query:

SELECT * FROM events GROUP BY date

For Laravel: 

$events = DB::table('events')->groupBy('date')->get();
Depzor
  • 1,126
  • 1
  • 12
  • 21
  • It is not possible to order the events, to that i only get the last events instead of the first events. When adding `->orderBy('id', 'DESC')` I still get the first events. How do you manage to do it? – Dominik Vogt Sep 18 '18 at 14:36
2

If you simply want to get one result for every redundant date value in your table, then use the DISTINCT keyword in your query.

Try this if you are using MySql:

select DISTINCT date from events where date IS NOT NULL

Note: DISTINCT considers null value also as a distinct candidate. So, it's advisable to check for null on your where clause.

Also, DISTNCT is faster than Group By.

Community
  • 1
  • 1
Hari Harker
  • 702
  • 1
  • 12
  • 29
1

If using Eloqunt, you can use the built in groupBy method:

Events::groupBy('browser')->get();
Chris
  • 54,599
  • 30
  • 149
  • 186
  • Shouldn't group by be used for performing some calculations? And the question was to get only one date value, not grouped date into one value right? – Hari Harker Aug 03 '16 at 07:10
  • There is no requirement with groupby to do a calculation - either in laravel or the underlying database. If no calc function is provided, it will simply grab the first one - it kind of collapses it. – Chris Aug 03 '16 at 07:13
  • @HariHarker Sidenote - I see what you are saying now - but looking at the question, he/she seems happy with the idea of loosing those extra records – Chris Aug 03 '16 at 07:22
  • What's bad about using DISTINCT with a where clause to omit null values in the result? I feel it's a bit more cleaner. – Hari Harker Aug 03 '16 at 07:24