Level 51
Anyone please willing to shed some light on this.
Oct 11, 2018
14
Level 51
Grouping by 'date' field
Hi all,
I'm having a brain freeze and can't figure out how to loop through a bunch of records and then show the month and year as one table with the records for that month in there, and then likewise for the next month and so on.
Kinda like this, but as you can see it's wrong, as October only has 3, then November should show as 4 and so on.
Current code I have is:
public function tour()
{
$dates = TourDates::where('date', '>=', Carbon::yesterday())
->orderBy('date', 'asc')
->groupBy(DB::raw('MONTH(date) DESC'))
->get();
$tours = TourDates::where('date', '>=', Carbon::yesterday())
->orderBy('date', 'asc')
->get();
return view('tour', compact('dates', 'tours'));
}
Level 9
Hard to say without seeing your view's code. But I don't think you need DESC in your groupBy method.
Level 51
View is as follows, which outputs the above screen shot:
@foreach ($dates as $gig)
<div class="row">
<div class="col">
<h4 class="text-center">{{ $gig->date->format('F') }} {{ $gig->date->format('Y') }}</h4>
<div class="table-responsive">
<table class="table">
<thead class="thead-light">
<tr>
<th>Date</th>
<th>Venue</th>
<th>Location</th>
<th>Box Office</th>
<th>Tickets</th>
</tr>
</thead>
<tbody>
@foreach($dates as $index => $tour)
<tr>
<td>{{ $tour->date->format('D') }} {{ $tour->date->format('jS') }} {{ $tour->date->format('M') }}</td>
<td>{{ $tour->name }}</td>
<td>{{ $tour->venue }}</td>
<td>@if($tour->box_office) {{ $tour->box_office }} @else n/a @endif</td>
<td><a href="{!! $tour->ticket_url !!}" target="_blank">Book Now</a></td>
</tr>
@endforeach
</tbody>
</table>
</div>
</div>
</div>
@endforeach
Level 9
What does it look like if you dd($dates)? Also did you see my comment about DESC above?
Level 51
Yes I removed DESC but still the same.
dd looks like:
0 => TourDates {#249 ▼
#fillable: array:4 [▶]
#table: "tour_dates"
#dates: array:1 [▶]
#connection: "mysql"
#primaryKey: "id"
#keyType: "int"
+incrementing: true
#with: []
#withCount: []
#perPage: 15
+exists: true
+wasRecentlyCreated: false
#attributes: array:8 [▼
"id" => 71
"date" => "2018-10-13"
"name" => "The Musician"
"venue" => "LEICESTER"
"ticket_url" => "http://www.themusicianpub.co.uk/"
"box_office" => null
"created_at" => "2017-09-20 15:57:16"
"updated_at" => "2018-01-23 16:39:48"
]
#original: array:8 [▶]
#changes: []
#casts: []
#dateFormat: null
#appends: []
#dispatchesEvents: []
#observables: []
#relations: []
#touches: []
+timestamps: true
#hidden: []
#visible: []
#guarded: array:1 [▶]
}
1 => TourDates {#250 ▼
#fillable: array:4 [▶]
#table: "tour_dates"
#dates: array:1 [▶]
#connection: "mysql"
#primaryKey: "id"
#keyType: "int"
+incrementing: true
#with: []
#withCount: []
#perPage: 15
+exists: true
+wasRecentlyCreated: false
#attributes: array:8 [▼
"id" => 33
"date" => "2018-11-03"
"name" => "THE FLOWERPOT"
"venue" => "DERBY"
"ticket_url" => "http://www.rawpromo.co.uk/"
"box_office" => null
"created_at" => "2017-01-25 18:15:42"
"updated_at" => "2017-12-04 13:23:23"
Level 9
It's not surprising that you're showing all your dates underneath October, because that's what the second foreach statement is doing: @foreach($dates as $index=>$tour). I don't have time at the moment to try to replicate your code but you should take a look there. Alternatively I'm sure someone else will see the problem.
Level 51
So I changed the method within the controller to:
public function tour()
{
$dates = TourDates::where('date', '>=', Carbon::yesterday())
->orderBy('date', 'asc')
->groupBy(DB::raw('MONTH(date)'))
->get();
return view('tour', compact('dates'));
}
and from the view to:
@foreach ($dates as $gig)
<div class="row">
<div class="col">
<h4 class="text-center">{{ $gig->date->format('F') }} {{ $gig->date->format('Y') }}</h4>
<div class="table-responsive">
<table class="table">
<thead class="thead-light">
<tr>
<th>Date</th>
<th>Venue</th>
<th>Location</th>
<th>Box Office</th>
<th>Tickets</th>
</tr>
</thead>
<tbody>
<tr>
<td>{{ $gig->date->format('D') }} {{ $gig->date->format('jS') }} {{ $gig->date->format('M') }}</td>
<td>{{ $gig->name }}</td>
<td>{{ $gig->venue }}</td>
<td>@if($gig->box_office) {{ $gig->box_office }} @else n/a @endif</td>
<td><a href="{!! $gig->ticket_url !!}" target="_blank">Book Now</a></td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
@endforeach
Although in the correct order and such now, only shows one record.
Level 9
Level 51
@unlikenesses Yeah that kinda rings bells with my stupid brain lol, I'll give it a whirl!
Level 51
Trying to get property of non-object is what I get and after changing from ->name to ['name'] that also returns an error.
Level 51
Can anyone suggest any thing here, really stuck with this.
Level 73
Is the query still giving you the same result when you do a dd($dates);?
Level 51
Only shows one result per month yes, whereas like I said October only has 3, then November should show as 4 and so on
Level 51
I'm still no further forward with this and really looking for some help for you wonderful people.
Please or to participate in this conversation.