Laravel - Eloquent Relationship hasMany but also hasOne?

Question

I've been all over the web and struggling with this for around 2 hours.

I have a USER model, a RUN model and a TIME model.

In the real world the user is in a race and gets their time entered in the database along with a USER_id and a RUN_id.

A user should only be able to have one row in the TIMES table for each RUN_id - if that makes sense!

Is this something I need to manage at the controller level? Or is there a relationship I can setup to ensure that a duplicate entry of this style can not enter the database?

Database structure at present:

USERS:

name

RUNS:

name

TIMES:

time

user_id

run_id

The Models:

USER:

public function times()
{
    return $this->hasMany(Time::class);
}

RUN:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Run extends Model
{    
    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function times()
    {
        return $this->hasMany(Time::class);
    }
}

TIME:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Time extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function run()
    {
        return $this->belongsTo(Run::class);
    }

}

the USER table must have an id field wich is primary key, and TIME table too. — Eric, Dec 17 '19 at 15:09
@L.Fox - this is a seperate relationship, I will remove from above — user3274489, Dec 17 '19 at 15:13
@Eric - these have all the usual primary keys, I just didn't specify them for the example — user3274489, Dec 17 '19 at 15:14
@L.Fox - not directly, no - it does not have a relationship with run. The user only has a relationship with times that have a relationship with run — user3274489, Dec 17 '19 at 15:15
do you have done a Migration code for Time Table ? if yes, in the CreateTimesTable function (in database\migration\xxxxx_create_times_table.php) you set indexes; at this point you can set a unique index based on run_id + user_id. see here : https://stackoverflow.com/questions/20065697/schema-builder-laravel-migrations-unique-on-two-columns — Eric, Dec 17 '19 at 15:19

Alec Joy · Accepted Answer · 2019-12-17T15:54:13.777

3

You can add a unique key constraint on the times table to enforce unique combinations of user_id and run_id

$table->unique(['user_id, 'run_id']);

To validate uniqueness at the application level, we can also add a constraint to the form validation. Assuming that you are passing both user_id and run_id in the request to create a new time, you can add the following to a form request

/**
     * Get the validation rules that apply to the request.
     *
     * @return array
     */
    public function rules()
    {
        return [
            'user_id' => Rule::unique('times')->where('run_id', $this->input('run_id'))
        ];
    }

    public function messages()
    {
        return [
            'user_id.unique' => 'A user may only have 1 time entry per Run'
        ];
    }

This will enforce the user_id is unique in the times table, filtered by that run id. The messages function also returns a more useful error message, since "user_id must be unique" is unhelpful in this situation.

edited Dec 17 '19 at 15:54

answered Dec 17 '19 at 15:25

Alec Joy

1,848
8
17

This worked, thank you! I will now have to post a separate question I think :) – user3274489 Dec 17 '19 at 15:33
How would you check for this when adding rows to the database? Or is this just for database integrity and not for Laravel to handle? – user3274489 Dec 17 '19 at 15:33
Laravel's validation rules can also work with compound keys, let me try to write something up and I'll edit my answer – Alec Joy Dec 17 '19 at 15:38
Okay, updated the answer with a form validation rule you can add. – Alec Joy Dec 17 '19 at 15:54
However - now when I try to submit a run time, I always get the message "A user may only have 1 time entry per run" on every single user.. – user3274489 Dec 18 '19 at 10:37
Here is a screencast of the issue: https://www.screencast.com/t/LwrnWjEVrI – user3274489 Dec 18 '19 at 10:52
try changing `Rule::unique('times')` to` Rule::unique('times', 'user_id')` and leaving the rest as is, I'm not sure if the rule is correctly interpreting which column actually has to be unique – Alec Joy Dec 18 '19 at 14:17
Strange - same issue! – user3274489 Dec 18 '19 at 14:58

score 1 · Answer 2 · answered Dec 17 '19 at 15:44

1

This answer should supplement the accepted answer. You should still define the pair of user_id,run_id as a unique key. However, in your case user and run have an N-N relationship with times as a pivot table. You should code it as such.

User

public function runs()
{
    return $this->belongsToMany(Run::class, 'times')->withPivot('time');;
}

Run:

public function users()
{
    return $this->belongsToMany(User::class, 'times')->withPivot('time');
}

Then you can retrieve them as:


$runs = User::find($userId)->runs; // Collection of all runs a user participated in
// $runs[X]->pivot->time has the time

You can check the documentation for more info

answered Dec 17 '19 at 15:44

apokryfos

38,771
9
70
114

1

I wish I could give two answers "best answer". A combination of this and the other answer helped me achieve the overall goal. This is also how I retrieve users that do NOT have a run: $runners = User::whereDoesntHave('runs', function($q) use($run) { $q->where('run_id', $run->id); })->whereHas("roles", function($q){ // get users only of certain roles $q->where("name", "Student") ->orWhere("name", "PLT Student"); })->get(); – user3274489 Dec 18 '19 at 09:03
Just realised that maybe a pivot table isn't the best way to do this? As as soon as I want to delete a "time" it causes problems. Pivot tables don't usually have ID's and when I want to delete a "time" I cannot access it in the normal way to delete it – user3274489 Dec 18 '19 at 11:20
Yes in that case you would need to "dettach" a run from a user. – apokryfos Dec 18 '19 at 16:25

Laravel - Eloquent Relationship hasMany but also hasOne?

2 Answers2