Help working out relationships

Level 88

You can do it like this

// Events.php

// An event has multiple venues
public function venues()
{
    return $this->hasMany('App\Venue');
}

// An event has one instance with a date
public function instance()
{
    return $this->hasOne('App\Instance');
}

Now in your controller you can do something like this

public function show($id)
{
    $event = Event::with(['venues', 'instance'])->where('id', '=', $id)->get();

    view('myView', compact($event));
}

And then in your view you can access all data you need

// Show the date
$event->instance->date

@foreach($event->venues as $venue)
    // Display all venues
@endforeach

Level 4

The thing is, an event can have multiple instances on the same date. It might run on 19/05/2015 in New York and Los Angeles, in other words. And of course, each event may have many instances; it might run on the 19th of each month in 3 different venues.

In terms of the SQL tables, the Events have no relationship to the Venues. The Instance table is the only one that refers to both.

Level 88

I would rethink my database structure if I were you! I can give you an example on how I would do it if you want to

Level 4

Please do! The basic structure of the app is that each Event page lists all the instances (and the associated info like Venue and costs).

Level 88

I would do it like this:

Table: events 
Columns: id, name, description

Table: instances
Columns: id, event_id, date

Table: venues
Columns: id, name, address

Table: instance_venue // pivot table
Columns: instance_id, venue_id

Now the though about this is that an event has mulitple instances. So for each instance you create you connect it to an event. An instance can have multiple venues, you can do that by using a pivot table.

// App\Event.php
public function instances()
{
    return $this->belongsToMany('App\Instance');
}

// App\Instance.php
public function venues()
{
    return $this->belongsToMany('App\Venue'); 
}

Now when you retrieve an event you can do something like this

public function show($id)
{
    $event = Event::with(['instances.venues'])->where('id', '=', $id)->get();
    
    view('myView', compact($event));
}

Now you can do this in your view

@foreach($event->instances as $instance)
    // Display instance stuff

    @foreach($instance->venues as $venue)
        // Display venue stuff
    @endforeach
@endforeach

Level 4

Ok, I've made a test project using this exact code but I get the following error when I navigate to my events page (e.g. event/1):

Column not found: 1054 Unknown column 'venues.instance_id' in 'where clause' (SQL: select * from `venues` where `venues`.`instance_id` in (1, 2))

EDIT:

Ok, after some research online, I've found that changing the relationship type to belongsToMany for both the venue and instance seems to get past this error, but now I get:

Undefined property: Illuminate\Database\Eloquent\Collection::$instances (View: /home/vagrant/Code/testing/resources/views/events/show.blade.php)

Which is odd, because if I just return the $event variable from the controller, I get a nice JSON object with all the data I expected to be there Event->Instances->Venues

Level 88

Sorry for the mistake on the relation! I updated my comment ;)

Can you show how you do your query in your controller and how you display it in your view?

Level 4

Well, I would, but having just made a new project to test it all out here at work (forgot to upload it to Github last night >.<) I now get a NotFoundHttpException when I try to visit the damn page... I've tried everything, composer dump-autoload, setting the storage folder permissions, restarting Homestead, you name it. It doesn't even work if I just return a string from my show() method. Any ideas?

EDIT:

Ok, got that working, now I get FatalErrorException in Builder.php line 461: Call to a member function getQuery() on null (－‸ლ)

EDIT:

Ok, I'm an idiot - I forgot to return the relationships. Now I've done that, I now get:

QueryException in Connection.php line 620: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'pivottest.event_instance' doesn't exist (SQL: select instances.*, event_instance.event_id as pivot_event_id, event_instance.instance_id as pivot_instance_id from instances inner join event_instance on instances.id = event_instance.instance_id where event_instance.event_id in (1))

This is with Event->belongsToMany Instances, Instances->belongsToMany Events, and Venues->belongsToMany Instances, though if I remove the last one I get the same error anyway.