I have three boards. One is for tasks to be managed, the other is for those responsible for carrying them out, and the other is for the cities where they are carried out. I also have a pivot table that must relate the three tables, through the id. The pivot table must also have an indicator of whether or not the task is active (a field with a 0
or a 1
, which is called activa
).
The migrations are the following. The one for the task table ( tasks
) is like this:
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateTasksTable extends Migration
{
public function up()
{
Schema::create('tasks', function (Blueprint $table) {
$table->increments('id');
$table->string('tarea', 100);
$table->text('descripcion')->nullable();
$table->date('f_inicio');
$table->date('f_final');
$table->timestamps();
});
}
public function down()
{
Schema::dropIfExists('tasks');
}
}
The one in the responsible table ( managers
) is like this:
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateManagersTable extends Migration
{
public function up()
{
Schema::create('managers', function (Blueprint $table) {
$table->increments('id');
$table->string('nombre', 60);
$table->string('cargo', 60);
$table->timestamps();
});
}
public function down()
{
Schema::dropIfExists('managers');
}
}
The one for the cities table ( cities
) is like this:
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateCitiesTable extends Migration
{
public function up()
{
Schema::create('cities', function (Blueprint $table) {
$table->increments('id');
$table->string('ciudad', 60);
$table->string('pais', 60);
$table->timestamps();
});
}
public function down()
{
Schema::dropIfExists('cities');
}
}
The one for the pivot table ( city_manager_task
) is like this:
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateCitiesManagersTasksTable extends Migration
{
public function up()
{
Schema::create('city_manager_task', function (Blueprint $table) {
$table->increments('id');
$table->integer('city_id')->unsigned();
$table->foreign('city_id')->references('id')->on('cities');
$table->integer('manager_id')->unsigned();
$table->foreign('manager_id')->references('id')->on('managers');
$table->integer('task_id')->unsigned();
$table->foreign('task_id')->references('id')->on('tasks');
$table->char('activa', 1);
$table->timestamps();
});
}
public function down()
{
Schema::dropIfExists('city_manager_task');
}
}
I have the models defined like this. The city model ( City
) is as follows:
<?php
namespace App;
use App\Manager;
use App\Task;
use Illuminate\Database\Eloquent\Model;
class City extends Model
{
protected $table = 'cities';
protected $fillable = [
'ciudad',
'pais',
];
public function managers()
{
return $this->belongsToMany(Manager::class, 'city_manager_task')->withTimestamps()->withPivot('activa');
}
public function tasks()
{
return $this->belongsToMany(Task::class, 'city_manager_task')->withTimestamps()->withPivot('activa');
}
}
The responsible model ( Manager
) is as follows:
<?php
namespace App;
use App\City;
use App\Task;
use Illuminate\Database\Eloquent\Model;
class Manager extends Model
{
protected $table = 'managers';
protected $fillable = [
'nombre',
'cargo',
];
public function cities()
{
return $this->belongsToMany(City::class, 'city_manager_task')->withTimestamps()->withPivot('activa');
}
public function tasks()
{
return $this->belongsToMany(Task::class, 'city_manager_task')->withTimestamps()->withPivot('activa');
}
}
The task model ( Task
) is like this:
<?php
namespace App;
use App\City;
use App\Manager;
use Illuminate\Database\Eloquent\Model;
class Task extends Model
{
protected $table = 'tasks';
protected $fillable = [
'tarea',
'descripcion',
'f_inicio',
'f_final',
];
public function cities()
{
return $this->belongsToMany(City::class, 'city_manager_task')->withTimestamps()->withPivot('activa');
}
public function managers()
{
return $this->belongsToMany(Manager::class, 'city_manager_task')->withTimestamps()->withPivot('activa');
}
}
The last model is that of the pivot table ( CityManagerTask
) is like this:
namespace App;
use App\City;
use App\Manager;
use App\Task;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\Pivot;
class CityManagerTask extends Pivot
{
protected $table = 'city_manager_task';
protected $fillable = [
'city_id',
'manager_id',
'task_id',
'activa',
];
}
I also have the factories. The one for cities ( CityFactory
) is like this:
<?php
use Faker\Generator as Faker;
$factory->define(App\City::class, function (Faker $faker) {
return [
'ciudad' => $faker->city,
'pais' => $faker->country
];
});
That of responsible ( ManagerFactory
) is as follows:
<?php
use Faker\Generator as Faker;
$factory->define(App\Manager::class, function (Faker $faker) {
return [
'nombre' => $faker->name(),
'cargo' => $faker->randomElement([
'Team leader',
'Controller',
'Advisor',
'Manager'
]),
];
});
The task ( TaskFactory
) is like this:
<?php
use Faker\Generator as Faker;
$factory->define(App\Task::class, function (Faker $faker) {
return [
'tarea' => $faker->randomElement([
'Gestión de marketing',
'Control de divisas',
'Mobiliario urbano',
'Medición de ruido',
'Creación de equipos',
'Estructuración de paisajes'
]),
'descripcion' => $faker->sentence(20),
'f_inicio' => $faker->dateTimeInInterval('- 2 years', '+ 6 months'),
'f_final' => $faker->dateTimeInInterval('- 1 years', '+ 6 months'),
];
});
Finally, the seeder (DatabaseSeeder) for the factories to be executed is as follows:
<?php
use App\City;
use App\Manager;
use App\Task;
use Illuminate\Database\Seeder;
class DatabaseSeeder extends Seeder
{
public function run()
{
$cities = factory(City::class, 20)->create();
$managers = factory(Manager::class, 10)->create();
factory(Task::class, 100)->create()
->each(function ($task) use ($cities, $managers)
{
$task->cities()
->attach($cities
->random(mt_rand(5, 15))
->pluck('id')
)->managers()
->attach($managers)
->pluck('id');
}
);
}
}
The migrations are running fine, and the structures are created perfectly, with their foreign keys, without problems. The problem comes when I try to run the seeder, so that it runs the factories. It's supposed to populate all three data tables (it does that well), but it also has to populate the pivot table. When I run it, it responds:
Illuminate\Database\QueryException : SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: city_manager_task.manager_id (SQL: insert into "city_manager_task" ("city_id", "created_at", "task_id", "updated_at") select 2 as "city_id", 2019-01-11 12:47:54 as "created_at", 1 as "task_id", 2019-01-11 12:47:54 as "updated_at" union all select 3 as "city_id", 2019-01-11 12:47:54 as "created_at", 1 as "task_id", 2019-01-11 12:47:54 as "updated_at" union all select 7 as "city_id", 2019-01-11 12:47:54 as "created_at", 1 as "task_id", 2019-01-11 12:47:54 as "updated_at" union all select 8 as "city_id", 2019-01-11 12:47:54 as "created_at", 1 as "task_id", 2019-01-11 12:47:54 as "updated_at" union all select 9 as "city_id", 2019-01-11 12:47:54 as "created_at", 1 as "task_id", 2019-01-11 12:47:54 as "updated_at" union all select 10 as "city_id", 2019-01-11 12:47:54 as "created_at", 1 as "task_id", 2019-01-11 12:47:54 as "updated_at" union all select 12 as "city_id", 2019-01-11 12:47:54 as "created_at", 1 as "task_id", 2019-01-11 12:47:54 as "updated_at" union all select 19 as "city_id", 2019-01-11 12:47:54 as "created_at", 1 as "task_id", 2019-01-11 12:47:54
as "updated_at")
at C:\xampp\htdocs\trestablas\vendor\laravel\framework\src\Illuminate\Database\Connection.php:664
660| // If an exception occurs when attempting to run a query, we'll format the error
661| // message to include the bindings with SQL, which will make this exception a
662| // lot more helpful to the developer instead of just the database's errors.
663| catch (Exception $e) {
> 664| throw new QueryException(
665| $query, $this->prepareBindings($bindings), $e
666| );
667| }
668|
Exception trace:
1 PDOException::("SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: city_manager_task.manager_id")
C:\xampp\htdocs\trestablas\vendor\laravel\framework\src\Illuminate\Database\Connection.php:458
2 PDOStatement::execute()
C:\xampp\htdocs\trestablas\vendor\laravel\framework\src\Illuminate\Database\Connection.php:458
And, I have no idea what I'm doing wrong. Whatever, it's in the seeder, I think. In addition, when completing the pivot table, I need to enter, in each record, in the field activa
, the value 0
o 1
. Namely. Each pivot table record should have the id of a task, that of a person in charge, that of a city, and the value that indicates whether the task is active or not.
The problem is the way the relationships with the pivot table (which you defined as a model) are defined, since you don't take into account the key of the table not involved.
In Task you have:
Basically the seeder fails when it tries to add the relationship with cities because the manager key is not included in said relationship
'manager_id
, and said field cannot be null. In any case, if it worked, it would generate two records in the pivot table, one for cities and one for managers.Taking into account that Laravel cannot natively handle a relationship of three tables with a pivot, you can solve it in several ways, perhaps the one that follows the Laravel conventions the most is to add the key in mention to the relationship as an additional field :
You could also do the same for managers.
As for the seeder, it would no longer be necessary to try to add the two relationships to the pivot table, everything would be done from just one, in this case we will do it with cities:
The two additional fields of the table are passed as the second parameter of the attach method, in this case as an array.