laravel 如何使用 Eloquent 过滤数据透视表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18538527/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to filter a pivot table using Eloquent?
提问by Arda
I'm using a pivot table on the project I'm working with to get works of users.
我在我正在处理的项目中使用数据透视表来获取用户的作品。
E.g: User::find(1)->worksgives me the works of user with ID of 1.
例如：User::find(1)->works给我 ID 为 1 的用户的作品。
The thing is that I want to filter this results with extra Pivot data.
问题是我想用额外的 Pivot 数据过滤这个结果。
Something like:
就像是：
User::find(1)->works->pivot->where('active',1)->get();
In which the active is the column I've set in my user_works pivot table.
其中 active 是我在 user_works 数据透视表中设置的列。
This is my related part of my User.php model:
这是我的 User.php 模型的相关部分：
<?php
class User extends Cartalyst\Sentry\Users\Eloquent\User {
public function works() {
return $this->belongsToMany('Work','user_works')->withPivot('active')->withTimestamps();
}
}
This is my related part of my Work.php model:
这是我的 Work.php 模型的相关部分：
<?php
class Work extends Eloquent {
public function users() {
return $this->belongsToMany('User','user_works')->withPivot('active')->withTimestamps();
}
}
This is my pivot table schema:
这是我的数据透视表架构：
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
class CreateUserWorksTable extends Migration {
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('user_works', function(Blueprint $table) {
$table->increments('id');
$table->integer('user_id')->unsigned()->default(0);
$table->integer('work_id')->unsigned()->default(0);
$table->enum('active',array('0','1'))->default(1);
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->foreign('work_id')->references('id')->on('works')->onDelete('cascade');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('user_works');
}
}
Is there any way to gain the data without making a new model for the pivot table?
有没有办法在不为数据透视表制作新模型的情况下获取数据？
Thanks in advance,
提前致谢，
Edit:I can filter this way:
编辑：我可以这样过滤：
return User::find(1)->works()->where('user_works.active','=','1')->get();
I had to type table name raw. But is there a better way to gain this without using it?
我不得不输入表名 raw。但是有没有更好的方法来获得它而不使用它？
回答by Arda
回答by vFragosop
Whenever you call withPivot('foo'), Laravel you do:
每当您调用withPivot('foo')Laravel 时，您都会执行以下操作：
SELECT ... `table`.`foo` AS `pivot_foo` FROM `table` ...
Fixed Answer:
固定答案：
MySQL in particular allows the usage of column aliases on HAVING, GROUP BYand ORDER BYclauses, but not on WHEREclauses.
MySQL 特别允许在HAVING,GROUP BY和ORDER BY子句上使用列别名，但不允许在WHERE子句上使用。
Both HAVINGand WHEREare used for filtering queries, but they behave slightly different: HAVINGis applied after GROUP BYand WHEREis before.
双方HAVING并WHERE用于过滤的查询，但他们的行为稍有不同：HAVING后应用GROUP BY，并WHERE为之前。
As a general SQL rule, you shouldn't use column aliases (pivot_fooin that case) for grouping, filtering or anything like that, since it may not work with other SQL databases.
作为一般 SQL 规则，您不应使用列别名（pivot_foo在这种情况下）进行分组、过滤或类似操作，因为它可能不适用于其他 SQL 数据库。
Although not recommended, it's possible to use:
虽然不推荐，但可以使用：
return User::find(1)->works()->having('pivot_active','=','1')->get();
回答by omar j
I try to setup all relationships in both directions as this allows for use of dynamic properties, eg $user->works().
我尝试在两个方向上设置所有关系，因为这允许使用动态属性，例如$user->works().
class Collection extends Eloquent {
public function contents()
{
return $this->belongsToMany('Content', 'collection_content', 'collection_id', 'content_id')->withPivot('collection_id', 'group_id', 'field_identifier');
}
}
class Content extends Eloquent {
public function collections()
{
return $this->belongsToMany('Collection', 'collection_content', 'collection_id', 'content_id')->withPivot('collection_id', 'group_id', 'field_identifier');
}
}
class CollectionContent extends Eloquent {
public function content()
{
return $this->belongsTo('Content');
}
public function collection()
{
return $this->belongsTo('Collection');
}
}
Then query:
然后查询：
$works = User::find(1)->works()->where('active', 1)->get();
Eloquent's documentation is awful when it comes to the use of pivot tables. This is a great tutorial: http://www.developed.be/2013/08/30/laravel-4-pivot-table-example-attach-and-detach/
当涉及到数据透视表的使用时，Eloquent 的文档很糟糕。这是一个很棒的教程：http: //www.develop.be/2013/08/30/laravel-4-pivot-table-example-attach-and-detach/
