Laravel creating different views from query

Question

I have the following query:

    $appointments = Appointment::orderBy('appointment', 'asc')
        ->leftJoin('appointments_labels', 'appointments_labels.id', '=', 'appointments.label_id')
        ->leftJoin('appointments_statuses', 'appointments_statuses.id', '=', 'appointments.status_id')
        ->select('appointments.id', 'appointments.appointment', 'appointments.label_id', 'appointments.status_id', 'appointments_labels.label', 'appointments_statuses.status')
        ->get();

What I am using this for is basically to display all my appointments like so (simplistic version of my view):

@foreach($appointments as $appointment)
    <table>
        <tr>
            <th>Appointment Name</th>
            <th>Label</th>
            <th>Status</th>
        </tr>

        <tr>
            <td>{{ $appointment->appointment }}</td>
            <td>{{ $appointment->label }}</td>
            <td>{{ $appointment->status }}</td>
        </tr>
    </table>
@endforeach

This will display all my appointments, including the correct label name and status name from the proper database tables. My wish is, to be able to create a view like so:

@foreach($appointments as $appointment)
    <table>
        <tr>
            <th>{{ $appointment->label }}</th>
            <th>Status</th>
        </tr>

    @foreach($appointment->appointments as $label)

        <tr>
            <td>{{ $label->appointment }}</td>
            <td>{{ $label->status }}</td>
        </tr>

    @endforeach

    </table>
@endforeach

So it lists the first label inluding all appointments which have that particular label, and then the next label with it's appointments, and so on. Is that simply possible with my above query? Or should I create relations in my models instead and work from there? If there is a better way to do all this ofcourse, I'm all ears.

Below is an export of my database tables for a better understanding of my code perhaps.

CREATE TABLE IF NOT EXISTS `appointments` (
  `id` int(11) NOT NULL,
  `appointment` varchar(255) NOT NULL,
  `location` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `start` datetime NOT NULL,
  `end` datetime NOT NULL,
  `label_id` int(11) NOT NULL,
  `status_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
)

CREATE TABLE IF NOT EXISTS `appointments_labels` (
  `id` int(11) NOT NULL,
  `label` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
)


CREATE TABLE IF NOT EXISTS `appointments_statuses` (
  `id` int(11) NOT NULL,
  `status` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
)

Example:

Iamzozo · Accepted Answer · 2015-10-31 08:14:25Z

2

You can use laravel collections groupBy:

Import it in your header:

use Illuminate\Support\Collection;

Then:

$appointments = Appointment::orderBy('appointment', 'asc')
    ->leftJoin('appointments_labels', 'appointments_labels.id', '=', 'appointments.label_id')
    ->leftJoin('appointments_statuses', 'appointments_statuses.id', '=', 'appointments.status_id')
    ->select('appointments.id', 'appointments.appointment', 'appointments.label_id', 'appointments.status_id', 'appointments_labels.label', 'appointments_statuses.status')
    ->get();

$appointments = Collection::make($appointments)->groupBy('label');
var_dump($appointments);

answered Oct 31, 2015 at 8:14

Iamzozo

2,35618 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hardist Over a year ago

Awesome, will give this a go and see if I can get with this what I need, thanks :)

Hardist Over a year ago

Couldn't get this to work, couldn't figure it out. So I ended up creating relations using hasMany and belongsTo in my models, and creating the views from there. Thanks for your answer though :)

Iamzozo Over a year ago

Strange, I'm going to test with your code, I just tested on one of my simple table, without joins..

Hardist Over a year ago

No worries, I just never worked with collections yet and it just didn't work out for me, so it's really my fault :)

Collectives™ on Stack Overflow

Laravel creating different views from query

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related