SQLSTATE[42S22]: Column not found: 1054 Unknown column 'type_article_id' in 'field list'

Question

I have three columns: article, type_article and theme. The article table has two foreign keys, which are type and theme.

Article migration

public function up()
{
    Schema::create('article', function (Blueprint $table) {
        $table->bigIncrements('id_article');
        $table->unsignedBigInteger('type');
        $table->unsignedBigInteger('theme');
        $table->string('titre');
        $table->string('contenu');
        $table->date('date_creation');
        $table->foreign('type')->references('id')
            ->on('type_article')->onDelete('cascade');
        $table->foreign('theme')->references('id')->on('theme')
            ->onDelete('cascade');
    });
}

Article model

class Article extends Model
{
    use HasFactory;

    protected $table = 'article';
    protected $primaryKey = 'id_article';
    public $timestamps = false;

    public function type_article()
    {
        return $this->belongsTo(Type_Article::class);
    }

    public function theme()
    {
        return $this->belongsTo(Theme::class);
    }
}

Type_article and Theme has this (except the end 'type' for 'type_article' and 'theme' for 'theme' :

public function article()
{
    return $this->hasMany('App\Article', 'type');
}

They both have a primary key id.

What I'm trying to do here is to fill a simple form with a title, an article type (with foreach loop inside my view (it's working), a theme, and content.

As you can see, inside the error message (SQL: insert into article (type_article_id, theme, titre, contenu, date_creation). There are only five elements; the id of the article is not there. II do not have a type_article_id inside my table, I don't really know where it comes from, but I do create an article_id

The issue is coming because you define "type (type_article)" as a foreign key. $table->foreign('type')->references('id')->on('type_article')->onDelete('cascade'); it is inbuilt functionality of the Laravel to search for the column refer as it's foreign+table+name_id. the best way to remove foreign fey from migration and handle it at the code level. — Ankit
So I should remove unsignedBigInteger and put int + remove the lines with foreign ? — Leon
else you can rename your column from "type" to "type_article_id". — Ankit
I personally believe that the user should not implement a foreign-key relationship at Schema/Migration level. because it not only slows the query operation but also sometimes generate DB-Error. although these are not very difficult to handle, you should avoid designing foreign-key relation in migration. you should handle such relationship at code level not at DB level. So it not only improve DB operation but also you can feel safe at the time of delete/update operation in DB. — Ankit
what do you mean by code level ? Where should I do that then ? — Leon

lagbox lagbox · Accepted Answer · 2021-01-14T05:38:15

You are not specifying the key to be used for the belongsTo relationship. When you do not define this, Eloquent has to assume the name; this naming is done by the relationship method name, type_article, + _id = type_article_id.

You need to tell the relationship to be using a different foreign key for the relationship as it does not fit with the automatic naming via convention:

$this->belongsTo(Type_Article::class, 'type');

This field should probably be named type_id to fit in with conventions better.

"Eloquent determines the default foreign key name by examining the name of the relationship method and suffixing the method name with a _ followed by the name of the primary key column. However, if the foreign key on the Comment model is not post_id, you may pass a custom key name as the second argument to the belongsTo method"

Laravel 8.x Docs - Eloquent - Relationships - One to Many (Inverse)

You are not showing how you are inserting a record, which is an important thing.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'type_article_id' in 'field list'

1 Answers