使用Eloquent将记录插入到具有多个外键的表中

Question

I've just started to learn Laravel 4 and I've run in to a small problem and I'm very curious to if there is a solution. I have a multilingual recipe database and I have, in short, modeled my database like this:

Schema::create('language', function(Blueprint $table) {
    $table->string('id', 2)->primary();
    $table->string('display_name');
    $table->timestamps();
});

Schema::create('recipe', function(Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->string('slug')->unique();
    $table->timestamps();
});

Schema::create('translated_recipe', function(Blueprint $table) {
    $table->increments('id');
    $table->integer('recipe_id')->unsigned();
    $table->foreign('recipe_id')->references('id')->on('recipe')->onDelete('cascade');
    $table->string('language_id', 2);
    $table->foreign('language_id')->references('id')->on('language');
    $table->string('translated_name')
    $table->timestamps();
});

My models look like this:

class Language extends Eloquent {
    protected $table = 'language';
    public function translatedRecipes() {
        return $this->hasMany('TranslatedRecipe');
    }
}

class Recipe extends Eloquent { 
    protected $table = 'recipe';
    public function translations() {
        return $this->hasMany('TranslatedRecipe');
    }
}

class TranslatedRecipe extends Eloquent {
    protected $table = 'translated_recipe';
    public function recipe() {
        return $this->belongsTo('Recipe');
    }
}

Then I have some basic seeding:

Language::create(array('id' => 'en', 'display_name' => 'English'));
Recipe::create(array('name' => 'First Recipe', 'slug' => 'first_recipe'));

Here come's the tricky part. How do I insert a translated recipe with Eloquent? I've come this far:

$recipe = Recipe::all()->first();
$lang = Language::all()->first();
$translatedRecipe = new TranslatedRecipe;
$translatedRecipe->translated_name = $lang->display_name.' '.$recipe->name;

I can easily do an INSERT with the Query Builder:

DB::table('translated_recipe')->insert(
    array(
        'recipe_id' => $recipe->id, 
        'language_id' => $lang->id,
        'translated_name' => $translatedRecipe->translated_name
    )
);

It doesn't set the timestamps though because I'm guessing that's a feature of Eloquent. Is there a way to keep it 100% Eloquent? I can't do this:

$translatedRecipe = $recipe->translations()->save($translatedRecipe);

...because SQL throws an "Integrity constraint violation: 1452..." error because of the missing second foreign key.

Answer 1

Ok, I haven't tested this but there are a few things I would change:

• I would change the 'language' table to have the following fields: 'id', 'initials' and 'displayname'.
• I would then change the 'language_id' field in 'translated_recipe' table to be an unsigned integer.

(It's really brave to have your primary key set to 'id' in the 'language' table - and it's not even an id by the loose meaning of the word! This is possibly the cause of the problem. Eloquent might be expecting your primary key to be the auto-incremented field which you don't have.)

Also, it's nice to make full use of the 'free' features provided in the framework at a relatively small 'fee' of following conventions such as:

1. naming your tables in plural form (languages, recipes, translations)
2. setting your primary key to be the auto-incremented field (obviously an integer)

You can scroll to the One to Many section of Dayle Rees Codehappy tutorial which was meant for Laravel 3 but is fit for our purpose. Or spare a buck or two for his CodeBright book for Laravel 4 if you want to learn other features of the framework. (I haven't yet)

Lastly, you don't have 'short_description' and 'description' fields in your 'translated_recipe' table! It's a miracle it's working at all!

        Schema::create('languages', function($table) 
    {
        $table->engine = 'InnoDB';

        $table->increments('id');
        $table->string('initials', 2)->unique();
        $table->string('display_name');
        $table->timestamps();
    });

    Schema::create('recipes', function($table) 
    {
        $table->engine = 'InnoDB';

        $table->increments('id');
        $table->string('name');
        $table->string('slug')->unique();
        $table->timestamps();
    });

    Schema::create('translations', function($table) 
    {
        $table->engine = 'InnoDB';

        $table->increments('id');
        $table->integer('recipe_id')->unsigned();
        $table->integer('language_id')->unsigned();
        $table->string('translated_name');
        $table->timestamps();

        $table->foreign('recipe_id')->references('id')->on('recipe')->onDelete('cascade');
        $table->foreign('language_id')->references('id')->on('language')->onDelete('cascade');          
    });

Models

class Language extends Eloquent {
  public function translations() 
  {
    return $this->hasMany('Translation');
  }
}

class Recipe extends Eloquent { 
  public function translations() 
  {
    return $this->hasMany('Translation');
  }
}

class Translation extends Eloquent {
  public function recipe() 
  {
     return $this->belongsTo('Recipe');
  }

  public function language() 
  {
     return $this->belongsTo('Language');
  }
}