DB Structure for A category hasMany attachments

Question

So, I have been struggling with this problem. in a hasMany relationship, Laravel is expecting me to make the foreign key in the attachment table which is wrong for my DB structure, I should have it in the category table instead.

When I do

/**
 * Display a listing of the resource.
 *
 * @return \Illuminate\Http\Response
 */
public function index()
{
    $categories = Category::with('attachments')->get();

    return $this->success(ApiResponseMessage::SUCCESS, $categories);
}

I get the following error:

Illuminate\Database\QueryException: SQLSTATE[42S22]: Column not found: 
1054 Unknown column 'attachments.category_id' in 'where clause'
(SQL: select * from `attachments` where `attachments`.`category_id` = 1
and `attachments`.`category_id` is not null) in file 
F:\xampp\htdocs\svysh\vendor\laravel\framework\src\Illuminate\Database\Connection.php on line 671

Category

class Category extends Model
{
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name', 'slug'
    ];

    public function attachments()
    {
        return $this->hasMany('App\Attachment');
    }
}


public function up()
{
    Schema::create('attachments', function (Blueprint $table) {
        $table->id();
        $table->string('url');
        $table->string('width');
        $table->string('height');
        $table->timestamps();
    });
}

Attachment

class Attachment extends Model
{
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'url', 'width', 'height', 'attachment_id'
    ];

    public function category()
    {
        return $this->belongsTo('App\Category');
    }
}

public function up()
{
    Schema::create('categories', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('slug');
        $table->integer('attachment_id')->nullable()->references('id')->on('attachment');
        $table->timestamps();
    });
}

user_5808894 · Answer

Your database design is wrong as per your requirements, since category hasMany attachments / attachments belongsTo category, the attachments table should contain a category_id column.

Your attachments table schema should be like below:

public function up()
{
    Schema::create('attachments', function (Blueprint $table) {
        $table->id();
        $table->string('url');
        $table->string('width');
        $table->string('height');
        $table->foreignId('category_id')->constrained()->onUpdate('cascade');
        $table->timestamps();
    });
}

and there is no need of attachment_id in the categories table schema

public function up()
{
    Schema::create('categories', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('slug');
        $table->timestamps();
    });
}

UPDATE: As per OP's comment

Attachment does not only belong to categories they also belong to products, users and attributes

In that case, adjust the attachments table schema to be in accordance with one-to-many polymorphic relationships

public function up()
{
    Schema::create('attachments', function (Blueprint $table) {
        $table->id();
        $table->string('url');
        $table->string('width');
        $table->string('height');
        $table->integer('attachable_id');
        $table->string('attachable_type');
        $table->timestamps();
    });
}

and add the respective method definitions in the model.

class Attachment extends Model
{

    /**
     * Get the owning attachable model.
     */
    public function attachable()
    {
        return $this->morphTo();
    }
}

class Category extends Model
{
  
    /**
     * Get all the Category's attachments.
     */
    public function attachments()
    {
        return $this->morphMany('App\Attachment', 'attachable');
    }
}

DB Structure for A category hasMany attachments

1 Answers