1

I have a scenario with Articles and Categories where each article can have only one category through a CategoryGroup.

This means the article can have more than one category but only one unique category from each category_group. Or, to put it another way, the article can have only one category from each category_group.

Do I model this scenario at the level of relationships? Or is this a matter of using custom validation?

This seems like a hasOneThrough scenario but this doesn't exist. So what's the workaround?

I have tried a many-to-many relationship between articles and categories but how do I constrain the article from having more than one of each category type while attaching categories to single articles?

   Schema::create('articles', function (Blueprint $table) {
        $table->increments('id');
        $table->string('title');
        $table->text('body');
        $table->string('slug');
        $table->timestamps();
    });

    Schema::create('categories', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->text('description');
        $table->string('slug');
        $table->string('handle');
        $table->integer('category_group_id')->index()->nullable();
        $table->timestamps();
    });

    Schema::create('article_category', function (Blueprint $table) {
        $table->integer('article_id')->unsigned();
        $table->integer('category_id')->unsigned();

        $table->primary(['article_id', 'category_id']);
        $table->timestamps();
    });
Amit Erandole
  • 11,995
  • 23
  • 65
  • 103
  • 1
    Could you post your table structure? – Marco Aurélio Deleu Aug 11 '16 at 12:05
  • Updated the question with table structures used – Amit Erandole Aug 11 '16 at 12:20
  • 1
    I think now I get it. Seems a tough one. Maybe try to add the `category_group_id` to the `article_category` table and make it `[article_id, category_group_id]` as primary key and make the `category_id` as FK. Technically, the database won't prevent you from adding into this table a record that doesn't integrally respect the link between `category_group` and `category_id`, so I think you'd have to make validations for that prior to the create / update operations. – Marco Aurélio Deleu Aug 11 '16 at 12:25
  • So its a combination of database constraints and validations I should use? – Amit Erandole Aug 11 '16 at 12:30
  • 1
    From my POV, there's no database rule that will allow you to have one `category_group` per `article` without a `category_group`'s table. But you want the article to have many `categories`, limited to only `categories` that don't belong to the same group. You can't have a many-to-many between `articles` and `category_group` because then you wouldn't know which `category` the article have. Triple Primary Key also won't help. Here is something that might help you: http://stackoverflow.com/questions/953035/multiple-column-foreign-key-in-mysql – Marco Aurélio Deleu Aug 11 '16 at 12:37
  • 1
    Following my previous advice, you'd have a many-to-many with `article_id`, `category_id` and `category_group_id`. Then you'd set as primary the `article` and the `group` (so an article can only have 1 group). Further, you'd set the `group` and the `category` as double FK, meaning if you try to insert a category that doesn't belong to the group you're inserting, the database might throw an Invalid FK. I never did this, so I don't know the actual outcome, but seems to achieve what you expect. – Marco Aurélio Deleu Aug 11 '16 at 12:39
  • Or I can just create custom validations while attaching categories to articles by overriding the save and update methods - just use guard conditions - don't you think that will work? – Amit Erandole Aug 11 '16 at 12:40
  • 1
    Yes, you may be able to use the `exists` rule to make sure you achieve what you want. But I believe that's another step to help improve your software. After all, you don't want to throw to an end-user an error message like "Foreign Key Violation". The problem with only code-validation is that the software can grow and at some point someone might write a separate code that doesn't make usage of your rules and then your database is dirty. So it's up to you on what you want to achieve. The software validation is a MUST regardless of integrity security from the database. – Marco Aurélio Deleu Aug 11 '16 at 12:43
  • I understand what you are saying but the arrangement with foreign keys which you are suggesting is really beyond my understanding at the moment. But I will study it – Amit Erandole Aug 11 '16 at 12:46

0 Answers0