laravel为帖子制作类别（规范化表）

Question

Laravel normalizing relationship in DB.

So I've jobs table that contains job. And categories table that contains category.

job can have multiple categories.

Is there a laravely way of normalizing the relationship?

Schema::create('jobs', function($table)
        {
            $table->increments('id');
            $table->integer('user_id')->unsigned();
            $table->string('slug');
            $table->string('title');
            $table->string('excerpt')->nullable();
            $table->text('content');
            $table->integer('delivery');
            $table->integer('price');
            $table->unique(array('user_id', 'slug'));
            $table->timestamps();
        });

Schema::create('categories', function(Blueprint $table)
    {
      // These columns are needed for Baum's Nested Set implementation to work.
      // Column names may be changed, but they *must* all exist and be modified
      // in the model.
      // Take a look at the model scaffold comments for details.
      // We add indexes on parent_id, lft, rgt columns by default.
      $table->increments('id');
      $table->integer('parent_id')->nullable()->index();
      $table->integer('lft')->nullable()->index();
      $table->integer('rgt')->nullable()->index();
      $table->integer('depth')->nullable();

      // Add additional columns here (f.ex: name, slug, path, etc.)
      $table->string('name')->unique();
      $table->string('slug')->unique();
      $table->string('description')->nullable();
    });

My first instinct is to create an intermediary table that holds relationship:

Schema::create('jobs_categories', function($table)
        {
            $table->increments('id');
            $table->integer('job_id')->unsigned();
            $table->integer('category_id')->unsigned();
            $table->unique(array('job_id', 'category_id'));
        });

But I'm not sure how to proceede, what would I do if I want to get categories along with all $jobs?

What do I do if I want to get $job category?

Is hasOne, hasMany a better suited for this?

Answer 1

What you're describing is a many-to-many relationship. And yes, a pivot table like jobs_categories is needed. Here's how you do it following Laravels naming convention and making use of relationships:

Pivot table

jobs_categories is fine but Laravel likes category_job (singular and alphabetical order) (This way you don't have to specify the table name in your relation)

Schema::create('category_job', function($table){
    $table->increments('id');
    $table->integer('job_id')->unsigned();
    $table->integer('category_id')->unsigned();
    $table->unique(array('job_id', 'category_id'));

    // foreign key constraints are optional (but pretty useful, especially with cascade delete
    $table->foreign('job_id')->references('id')->on('jobs')->onDelete('cascade');
    $table->foreign('category_id')->references('id')->on('categories')->onDelete('cascade');
});

Relationships

Job model

public function categories(){
    return $this->belongsToMany('Category');
}

Category model

public function jobs(){
    return $this->belongsToMany('Job');
}

Usage

Jobs with categories eager loaded

$jobs = Job::with('categories')->get();

Access categories of a job

$job = Job::find(1);
$categories = $job->categories;

Visit the Laravel docs for more information on Eloquent relationships