You are in case of Many to Many relation. Look at documentation to achieve it.
Eloquent One-to-Many Relationship
I would like to assign all of my users to groups. Each user may be assigned to multiple groups. In the database, there are three tables to handle this: users, groups, and user_groups.
Here is the schema for each:
users id (PK, bigint, not null) email (nvarchar(255), not null) email_verified_at (datetime, null) password (nvarchar(255), not null) remember_token (nvarchar(100), null) created_at (datetime, null) updated_at (datetime, null) first_name (nvarchar(50), not null) last_name (nvarchar(75), not null)
groups id (PK, smallint, not null) group_name (nvarchar(255), not null)
user_groups user_id (PK, FK, bigint, not null) group_id (PK, FK, smallint, not null)
I have two models defined: App\User and App\Group:
<?php
namespace App;
use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
class User extends Authenticatable
{
use Notifiable;
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $fillable = [
'first_name', 'last_name', 'email', 'password',
];
/**
* The attributes that should be hidden for arrays.
*
* @var array
*/
protected $hidden = [
'password', 'remember_token',
];
/**
* The attributes that should be cast to native types.
*
* @var array
*/
protected $casts = [
'email_verified_at' => 'datetime',
];
public function group()
{
return $this->hasMany('App\Group');
}
}
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Group extends Model
{
public $timestamps = false;
public function users()
{
return $this->belongsTo('App\User');
}
}
The relationship that I've defined doesn't return the groups to which the user belongs. I get the following error:
Illuminate/Database/QueryException with message 'SQLSTATE[42S22]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'user_id'. (SQL: select * from [groups] where [groups].[user_id] = 1 and [groups].[user_id] is not null)'
I assume this is because Laravel isn't expecting the user_groups table in my database. How should I define the relationship in my models and my database?
Please or to participate in this conversation.