Level 122
I have to ask... why a separate table when you could just add a type column to the users table?
Mar 30, 2023
7
Level 1
How to add a foreign ID column with a default when data exists
So let's say I have table users in production. Later I want to create table user_types. This table would be a reference table with id and name. First row ['id' => 1, 'name' => 'regular'] and second row ['id' => 2, 'name' => 'special'].
So I create a migration for the new table, and then another migration to add the new field into users table. There is my issue.
public function up(): void
{
Schema::table('users', function (Blueprint $table) {
$table->foreignId('user_type_id')->default(UserType::whereName('regular')->id);
$table->foreign('user_type_id')->references('id')->on('user_types');
});
}
So, of course at this point UserType::whereName('regular') is null. I could default to 0 or 1, but then creating the foreign fails anyway since the user_types table is not yet populated. The seeder is ready, and I have a script to run it automatically on deploy, but since migrations run one after the other, defining any kind of default would fail.
If I was to separate it into 2 migrations, run the first, run the seeder and then run the second one, then sure it would work. But I honestly find it a bit clumsy. I mean that forces me to commit the first part of the migrations to production, run the seeder, and only then commit the second. Feels wrong. Not a good practice at all.
Also, calling the seeder into the migration file is bad. Migrations are for structure, not data. That means also that in tests, that particular seeder shouldn't be called with all the others. Really don't want to do that.
Any idea?
Level 1
@Snapey well I HAVE added the user_type_id column :p Why not just an arbitrary string column on the users table? Simply because the user_types table may have more than just the name. Some specific data that could be updated for a part of the user base, depending on their type and not on their id. Also, I could have translations for the user's type using https://spatie.be/docs/laravel-translatable/ ... Many possibilities.
Maybe another example will make it better. A books table, existing for some time. And now we want them to have a category, and have a base price for all books in a category. So We would need to create a book_categories table with id, name and base_price, add a book_category_id to books as a foreign key, and we would be facing the exact same issue I've got here.
Level 122
@JerryBels but in your books example, a category might be shared by many books, so you would have a book_category pivot table linking books to categories and a belongsToMany relationship on both models.
But in your question, you appear to be not using a pivot?
Level 1
@Snapey I kindly disagree, in my example a category is shared by many books, but a book may have only one category - which means we're in a belongsTo case and not a belongsToMany - no pivot table needed. That's the same issue as in my question. I think there is no perfect solution though, digging through the framework's code. I think I don't have a choice but to make the column nullable and ensure programmatically that it's effectively always filled, and maybe at a later date add a new migration to change it so it's not nullable anymore.
Level 122
@JerryBels My solution to the original question would be as Lary says, default it to one particular type (even if it is 'unset' and then run the process that sets the correct value on the record.
1 like
Level 122
but a book may have only one category - which means we're in a belongsTo case and not a belongsToMany
There is an argument that you should plan for the belongsToMany case even if you initially only need single relationship. This came up in a tweet thread recently (can't remember where) but its an argument that has been around for years. Its a lot harder to reengineer later when someone comes along to ask "hey we need more than one category per item"
Level 1
"Premature optimization is the root of all evil", and all that :p
Please or to participate in this conversation.