我做了一个查询,但我不满意,因为我想用 Eloquent 来做!
下面是查询:
Tournament::leftJoin('category_tournament', 'category_tournament.tournament_id', '=', 'tournament.id')
->leftJoin('category_tournament_user', 'category_tournament_user.category_tournament_id', '=', 'category_tournament.id' )
->where('category_tournament_user.user_id', '=',$this->id )
->select('tournament.*')
->distinct()
->get();
迁移:
Schema::create('tournaments', function(Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->timestamps();
});
Schema::create('categories', function(Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->timestamps();
});
Schema::create('category_tournament', function(Blueprint $table) {
$table->increments('id');
$table->integer('category_id');
$table->integer('tournament_id');
$table->timestamps();
$table->foreign('tournament_id')
->references('id')
->on('tournament')
->onDelete('cascade');
$table->foreign('category_id')
->references('id')
->on('category')
->onDelete('cascade');
});
Schema::create('category_tournament_user', function (Blueprint $table) {
$table->increments('id');
$table->integer('category_tournament_id')->unsigned()->index();
$table->foreign('category_tournament_id')
->references('id')
->on('category_tournament')
->onDelete('cascade');
$table->integer('user_id')->unsigned()->index();
$table->foreign('user_id')
->references('id')
->on('users')
->onDelete('cascade');
$table->unique(array('category_tournament_id', 'user_id'));
$table->boolean('confirmed');
$table->timestamps();
$table->softDeletes();
$table->engine = 'InnoDB';
});
模型
class Tournament extends Model
{
public function categories()
{
return $this->belongsToMany(Category::class);
}
public function categoryTournaments()
{
return $this->hasMany(CategoryTournament::class);
}
}
class Category extends Model
{
public function tournaments()
{
return $this->belongsToMany(Tournament::class);
}
public function categoryTournament()
{
return $this->hasMany(CategoryTournament::class);
}
}
class CategoryTournament extends Model
{
protected $table = 'category_tournament';
public function category()
{
return $this->belongsTo(Category::class);
}
public function tournament()
{
return $this->belongsTo(Tournament::class);
}
public function users()
{
return $this->belongsToMany(User::class, 'category_tournament_user');
}
}
class User extends Authenticatable
{
public function categoryTournaments()
{
return $this->belongsToMany(CategoryTournament::class, 'category_tournament_user');
}
}
查询有效,我只想知道我应该如何使用 Eloquent 进行操作,因为我无法自己完成:(
知道怎么做吗???
首先,您必须将外键添加到迁移表中 + 使它们无符号很有用,因为您可能永远不会有任何负 id:
$table->integer('tournament_id')->unsigned();
$table->foreign('tournament_id')->references('id')->on('tournaments');
由于您已经拥有模型的关系,因此您应该能够使用 Eloquent 来获取内容。
您可以坚持此页面以获取有关Eloquent的更多信息:https://laravel.com/docs/5.0/eloquent
应该是这样的:
$tournaments = Tournament::with('category_tournament')
->with('category_tournament_user')
->where('category_tournament_user.user_id', '=',$this->id )
->distinct()
->get();
之后,您可以使用$tournaments的内容:
$tournaments->category_tournament->name;
希望我没有错过任何关键步骤,但我认为它应该通过进行这些更改来工作。
编辑:
https://laravel.com/docs/5.1/eloquent-relationships#eager-loading
当将 Eloquent 关系作为属性访问时,关系数据是"延迟加载"的。这意味着在您首次访问属性之前,不会实际加载关系数据。但是,Eloquent 可以在查询父模型时"预先加载"关系。预先加载缓解了 N + 1 查询问题。为了说明 N + 1 查询问题,请考虑与作者相关的 Book 模型: