Schema Design & Eloqeunt Model Relationships

You only need 2 models. Tournament and Course. Both are related via many to many ( belongsToMany() )

Just make sure you also create the pivot table.

In your tournament model you specify the relationship in a public method named courses().

I'm on my phone right now so I can't use the code functionality here but I can update this as soon as I'm home with a more concrete example.

Okay thanks. Just to clarify.

TournamentCourse is my pivot table.

Here is my current Model details:

Tournament 
------------------
-id 
-tournamentname

TournamentCourse 
----------------------------
id
tournament_id
course_id
date_play
par

Course
---------------------
id
coursename

Initially I did not had "id" a/increment field in pivot, TournamentCourse. Added that recently as I realized that a Tournament could consist of the same course multiple times.

And also I did use belongsToMany() relation but since my relation depended on course_id & tournament_id it did not return the correct row. For the same reason - having same course more than one time in tournament. Thus your tournament_id and course_id duplicates and unique constraint broken. So I included the pivot table "id" field, auto increment. Was this the correct solution?

Thanks in advance :)

First off: I see this a lot here but don't really understand why you have a dedicated Model for the pivot table. In my experience you don't need it.

First I'll go into setting up the models:

class Tournament extends Model {

    // Table that is associated with the model
    // You don't need that if it follows convention
    // but I put it here for reference
    protected $table = "tournaments";

    // Function you need for querying the relationship to courses
    // Please note that Courses::class is PHP 5.6 only and works
    // If you have a use statement for the appropriate class. If you use
    // PHP < 5.6 you will need to substitute this for the fully qualified classname
    // for example 'App\Models\Course'
    //
    // The second parameter is the name of the pivot table.
    // The third parameter is the name of the foreign key for the current model
    // The fourth parameter is the name of the key for the related model
    // You don't have to set those if you follow convention but I included them for completeness.
    
    public function courses()
    {
        return $this->belongsToMany(Course::class, 'tournament_courses', 'tournament_id', 'course_id');
    }
}

We do the same for the Course model in case you want to query tournaments based on courses. (I'll spare the comments as it's the same as in the Tournament model):

class Course extends Model {
    protected $table = "courses"

    public function tournaments() {
        return $this->belongsToMany(Tournament::class, 'tournament_courses', 'course_id', 'tournament_id');
    }
}

If you want to query those relationships you have 2 options: You can use the functions or use them as dynamic properties:

$courses = $tournament->courses()->get();
$courses = $tournament->courses;

To insert a new course into a tournament you simply do this:

$course = Course::find($id); //Or whatever query you need to do to get the course

$tournament = Tournament::find(1);
$tournament->courses()->save($course);

If you want to insert multiple courses into a tournament build an array of the courses:

$courses = Courses::all(); // You have to build the array of courses according to your business needs, I use this just to illustrate

$tournament = Tournament::find(1);

$tournament->courses()->saveMany($courses);

And your pivot table migration would probably look something like this:

class CreateTournamentCoursesTable extends Migration {
    
    public function up()
    {
        $this->schema->create('tournament_courses', function(Blueprint $table) {

            $table->increments('id');
            $table->integer('tournament_id')->unsigned();
            $table->integer('course_id')->unsigned();

            $table->timestamps();

            $table->index('tournament_id');
            $table->index('course_id');
            
            $table->foreign('tournament_id')
                   ->references('id')->on('tournaments')
                   ->onDelete('cascade');

            $table->foreign('course_id')
                   ->references('id')->on('courses')
                   ->onDelete('cascade');           

        });
    }
}

I have not tested any of this so I can't guarantee that it works but it should. Let me know if it does and if not I'll go ahead and do some testing for you.

Best,

Marcel

Hi Marcel,

Really appreciate all your effort. Just want to respond to the first part of your reply. Why the dedicated model for pivot table? There is extra Pivot fields/data that needs to be stored as well. For example: DatePlay. How else would I do this? Your suggestion please :)

And then for the rest of your reply. I have the Models set up exactly in the same manner as you suggested.

But here is the issue:

1. A Tournament can consist of a duplicate Course:

TournamentCourses (pivot table):

tournament_id   | course_id | dateplay
--------------------     --------------   ------------
1                          |   1               |  2015.01.01
1                          |   1               |  2015.01.02
1                          |   2              |  2015.01.03

I can store (attach/synch) the pivot data with no problem. It saves correctly. But when I want to return the pivot data with additional pivot property (dateplay) it do not return correct lines. It gets all the course but wrong dates cause same course exists twice.

Hope this makes sense :)

Hi Sven,

that's a good point generally and I guess it's more personal preference. I usually do stuff like this in a separate model/table which is simply a different engineering approach and depends on your business needs. I usually start with writing out a scenario to get a good mental model of what I want to build.

In your case I assume you want to store when a Player/User completed a specific course in a tournament.

I would do that in a dedicated Progress or Log model/table which can store these relationships making the implementation a lot cleaner. You can fire an event when a User/Player completes a course and have an Observer create the appropriate Log/Progress entry.

If you can outline what you are trying to do I can come up with something more specific.

Best,

Marcel

Hey Sven,

as I said in my previous post if I have a little more info on your business needs I can help more. Feel free to send me an email at marcelbensch@gmail.com if you don't want to post it here :)

Best,

Marcel

Okay let me give you real live example.

User will create a Tournament. He will then assign several Course(s) to the Tournament. He will then also select Player that will compete in this tournament. and now the Tournament starts. So all the set up happens before one can actually log the scores.

Once the Tournament finishes the user will go back and capture the Scores for each Player per Course and get his total scores for the Tournament.

This is for a Golf management tool.

Important to note - that the Tournament, Course assignment and Player assignment happens first. The Tournament starts and after completion user can record all the scores.

Hi Sven,

okay let's break this down in relationships:

A User can have many Tournament. (Creator of Tournament) A Tournament has many Courses. A Course belongs to many Tournaments. A User can belong to many Tournaments (Competing Players) A User can have many Scores. A Score belongs to Course in a Tournament.

So the problem right now is: How do you model that last relationship?

My Solution:

Create a dedicated Score Model which has foreign keys to a Course, Tournament and User. At the time a User completes a Course simply generate a new Score with the appropriate data.

That makes for a really clean implementation. You can create leaderboards for tournaments, statistics for users, for courses etc. Everything by just using the Score model and appropriate query methods/properties.

And the Score also solves your problem with storing the date as this is simply a property on the Score model. You can then query and filter scores per tournament and user and add up to a total score etc. It's a very flexible architecture

But if you really want to deal with metadata on the pivot table you have to specify the fields on the pivot table:

As soon as you define a belongsToMany() relationship Eloquent will give you a "pivot" property on your model which represents the pivot table and can be used as any other model.

For example:

$tournament = Tournament::find(1);

foreach ($tournament->courses as $course)
{
    echo $course->pivot->created_at;
}

By default only the keys are available on the table, if you want your metadata to be available you have to specify that on the relationship:

$this->belongsToMany(Course::class)->withPivot('date_played','position');

You can find more info about that here: http://laravel.com/docs/5.0/eloquent#working-with-pivot-tables

I hope that makes sense.

Best,

Marcel

Thanks Marcel :) I have it working with my pivot data and everything was working perfectly. Scoring, Leaderboard etc...

But then my client informed me that i need to change the logic as follows: A Tournament can have several Courses (i got that) BUT the big change is that not only several Courses but also can Tournament consists of several of the very same Course.

Perhaps let me say given all the background info. Lets forget about all the other related Models. So far I've got them working. Actually very similar to your suggestion.

How do one handle a many to many relationship where your pivot table could contain exact duplicates (legally) in terms of your foreign keys ?

The system was working before this change. But obviously it was working because you can only have unique Courses per Tournament. No playing the same Course twice for a given Tournament. And this is what changed.

Thanks again! :)

Hi Sven,

it shouldn't make a difference as long as you have a dedicated primary key and not a combined one. It shouldn't be a problem to just add a course multiple times.

In your database it would look like this:

|     id     |     tournament_id     |     course_id     |
----------------------------------------------------------
|     1      |                1                   |            1             |
----------------------------------------------------------
|     2      |                1                   |            3             |
----------------------------------------------------------
|     1     |                1                   |            1             |

Let me go ahead and try to model that in code and test it. Should take about 15 minutes. Will be back with results.

Best,

Marcel

Hey Sven,

checkout my code here: https://github.com/mbensch/sven-model-test

Most of the action happens in Console\Commands because I just did it quick and dirty for testing. I had no problems saving or retrieving courses for a tournament even if I add the same course multiple times.

If you have your code in a repository, I can probably help you fix it really quick.

Best,

Marcel

My database content for the pivot table looks like that:

| id | tournament_id | course_id | created_at | updated_at | | :--: | :--------------------: | :-------------: | :--------------: | :---------------: | | 1 | 1 | 1 | "2015-03-19 09:16:31" | "2015-03-19 09:16:31" | | 2 | 1 | 4 | "2015-03-19 09:16:31" | "2015-03-19 09:16:31" | | 3 | 1 | 7 | "2015-03-19 09:16:31" | "2015-03-19 09:16:31" | | 4 | 1 | 3 | "2015-03-19 09:16:31" | "2015-03-19 09:16:31" | | 5 | 1 | 2 | "2015-03-19 09:16:31" | "2015-03-19 09:16:31" | | 6 | 1 | 4 | "2015-03-19 09:16:31" | "2015-03-19 09:16:31" | | 7 | 1 | 1 | "2015-03-19 09:16:31" | "2015-03-19 09:16:31" |

Okay cool - I also manage to get the pivot data and metadata stored correctly. But when reading/retrieving pivot data I also get all the rows returned BUT: the metadata fields (dateplay, par) do not return correctly. It is as if returns the first rows metadata for all other rows.

This is my Tournament model:

public function courses() {
 return $this->belongsToMany("Course", "tournamentcourses", "tournamentid", "courseid")->withPivot("dateplay",       "par"); //Defining extra Pivot/Junction table fields
}

And my Tournament controller method to edit model:

    public function edit($id)
    {
        $tournament = $this->tournament->edit($id);

        if ($tournament) {
            // $courses = Course::select(array("id", "coursename"))->orderBy("coursename")->lists("coursename", "id");
            // $groups = Group::select(array("id", "groupname"))->orderBy("groupname")->lists("groupname", "id");

            return View::make("tournament.create")->with(array("tournament"=>$tournament, "courses"=>$this->tournament->courses(), 
                "groups"=>$this->tournament->groups(), "coursesadded"=>$tournament->courses));
        }else{
            $this->setNotFoundMessage($this->entity_name);
            return Redirect::route("tournament.index");
        }       
    }

and my view to Iterate over Tournament Courses() to load all the Courses and pivot metadata for display:

              @foreach($coursesadded as $key=>$course)
                <?php $rownumber++ ?>
                <div class="row form-group" id="{{ $rownumber }}">
                  <div class="col-sm-3">
                    {{ Form::hidden("round" . $rownumber, $course["id"]) }}
                    {{ Form::text( "course" . $rownumber, $course["coursename"], array("class"=>"form-control", "readonly"=>"readonly") ) }}
                  </div>
                  <div class="col-sm-3">                    
                    {{ Form::input("date", "date" . $rownumber, $tournament->courses()->where("courseid", $course["id"])->first()->pivot->dateplay, array("class"=>"form-control", "readonly"=>"readonly" )) }}
                  </div>
                  <div class="col-sm-3">                    
                    {{ Form::text("par" . $rownumber, $tournament->courses()->where("courseid", $course["id"])->first()->pivot->par, array("class"=>"form-control", "readonly"=>"readonly" )) }}
                  </div>
                  <div class="col-sm-3">                    
                    {{ Form::button("Remove", array("class"=>"btn btn-danger btn-sm", "id"=>$rownumber, "onclick"=>"deleteCourse(this.id)") ) }}
                  </div>
                </div>
              @endforeach

So I guess my real issue is to read the pivot metadata given I have duplicate course_id's for same Tournament.

Well you have an error in your query:

If you do

$tournament->courses()->where("courseid", $course["id"])->first()->pivot->dateplay

you will always get the meta data for the first time a course id is found. Which is correct behavior. If you were to translate this into actual language:

1. Select all courses
2. Find the first course with id $id
3. Select dateplay field in pivot table

Think about what data you have: You are already iterating over the pivot data in $coursesadded so no need to query it again. Just do:

$course->pivot->dateplay

Best,

Marcel

Let me just test this in my code really quick.

Just tested it and it worked. So all you have to do is treat $course as an object and not an array and access the pivot data directly as described above.

Best,

Marcel

Wow!!!! Miracle thanks.

I actually did that before I saw your post - just now and it work. Just to come back here and refresh page and see you suggesting the same.

But still all credit to you = helped me to actually go through everything step by step with your assistance.

So in the end it was me referencing the pivot metadata incorrectly when reading it :)

Would you mind if I add you on Facebook? Go your profile here open...

No problem :D Just go ahead and add me. Always happy about some more developers.