Laravel 5.5 Pivot Join获得MySQL主结果的Pivot值 [英] Laravel 5.5 pivot join to get pivot values with main MySQL result
问题描述
我正在尝试在MySQL查询上联接数据透视表.基本上，我选择的是用户，其中一个用户具有多个子类别.
I'm trying to join a pivot table on a MySQL query. Basically I'm selecting the users, where one user has multiple sub categories.
从本质上讲，在我的"sub_categories"关系中，一个用户有许多子类别.但是由于我使用的是RAW select，因此我无法选择/使用这些关系.相反，我必须使用联接.
So in essence with my "sub_categories relationship, one user has many sub categories. But because I am using RAW select, I cannot select / use the relationships. Instead I have to use a join.
这是我的sub_categories表
This is my sub_categories table
Column Type Comment
id int(10) unsigned Auto Increment
main_category_id int(10) unsigned [0]
category_name varchar(100)
created_at timestamp NULL
updated_at timestamp NULL
这是我的数据透视表
Column Type Comment
user_id int(10) unsigned
sub_category_id int(10) unsigned
这是我的SQL查询
$users= DB::table('users')
->select('users.*', 'user_options.*', DB::raw('
branches.*,
professional_profiles.tags,
' . $lat . ' AS latpoint,
' . $lng . ' AS longpoint,
' . $radius . ' AS radius,
' . $measurement_number . ' AS distance_unit,
(
' . $measurement_number . ' * DEGREES(
ACOS(
COS(RADIANS(' . $lat . '))
* COS(RADIANS(branches.lat))
* COS(RADIANS(' . $lng . ' - branches.lng))
+ SIN(RADIANS(' . $lat . '))
* SIN(RADIANS(branches.lat))
)
)
) AS distance
'), 'users.id AS id')
->leftJoin('branches', 'users.branch_id', '=', 'branches.id')
->leftJoin('user_options', 'user_options.user_id', '=', 'users.id')
->leftJoin('professional_profiles', 'professional_profiles.user_id', '=', 'users.id')
->where('user_options.professional', '>', 0)
->where('users.branch_id', '>', 0)
->where(function ($x) use ($term) {
$x->where('branches.branch_name', 'like', '%' . $term . '%')
->orWhere('branches.branch_city', 'like', '%' . $term . '%')
->orWhere('users.firstname', 'like', '%' . $term . '%')
->orWhere('users.lastname', 'like', '%' . $term . '%')
->orWhere('professional_profiles.tags', 'like', '%' . $term . '%');
})
->having('distance', 'orderBy('distance', 'asc')
->limit(50)
->get();
这是我的结果:
[
{
id: 4,
profile_id: 2,
branch_id: 3,
prefix: "dr",
firstname: "SWK1",
lastname: "Doe",
email: "swk1@gmail.com",
mobile_no: "811692244",
password: "$2y$10$LzkPwc2TZu/.UzB.0mYJ",
avatar: "123.jpg",
remember_token: "wF33ShLirtvS3mIYJpmg5skVVoohGJCS7v",
created_at: "2017-10-12 09:32:05",
updated_at: "2017-10-12 09:32:05",
provider: null,
provider_id: null,
user_id: 4,
profile_administrator: 0,
branch_administrator: 0,
professional: 1,
branch_name: "Swakopmund 1",
branch_address_1: "14 Backer St",
branch_address_2: null,
branch_city: "Swakopmund",
branch_state: null,
branch_zip: "9000",
branch_country: "NA",
branch_phone: "77777",
main_image: null,
lat: -22.67,
lng: 14.53,
description: "Swakopmund 1",
tags: "Doctors,Dietician,General Practitioner",
latpoint: "-22.5608807",
longpoint: "17.0657549",
radius: 500,
distance_unit: "111.045",
distance: 260.210154298872
}
]
因此，本质上，问题是要通过使用数据透视表设置的值，而不是雄辩的关系表，而是通过使用SQL，来将users_subgcate表连接到用户表.
So essentially the question would be to join the sub_categories table on the users table, by making use of the values set by the pivot table, without relying on the eloquent relationships table but rather by making use of a SQL.
由于一个用户有许多子类别，最好将子类别作为连接到主SQL查询的数组值返回.
Since one user has many sub_categories, it would be great to return the sub_categories as an array value joined on the main SQL query.
推荐答案
I had similar situation and I Query Scope along with my pivot table for one to many relation. In my situation, User has multiple groups and I need to fetch those data along with user object without extra query or without JOINs.
See Query scope and one to many and many to many with pivot on Laravel Doc.
如果要使用数据透视表获取数据，请参见以下示例
用户模型:
If you want to fetch data using pivote table, here is the example
User Model:
class User extends Authenticatable
{
use Notifiable;
protected $fillable = [
'name', 'email', 'username', 'password',
];
protected $hidden = [
'password', 'remember_token',
];
public function groups()
{
return $this->belongsToMany('App\Group', 'user_groups',
'user_id', 'group_id');
}
public function scopeDetail($query)
{
return $query->with('groups');
}
}
组模型:
class Group extends Model
{
protected $fillable = [
'dn', 'cn', 'description',
];
}
在上述用户模型中，请参见return $this->belongsToMany('App\Group','user_groups', 'user_id', 'group_id');，其中user_groups是我的数据透视表，用于定义用户和组之间的关系. group_id和user_id是数据透视表中的字段.
In above user model, see return $this->belongsToMany('App\Group','user_groups', 'user_id', 'group_id');, where user_groups is my pivot table which defines the relationship between users and group. group_id and user_id are the fields in pivote table.
现在使用上述架构获取数据(在控制器上):
Now Fetching data (on controller) using above architechture:
User::where(.....)->detail()->first();
其中detail()是我在用户模型中定义为scopeDetail的范围.注意:必须附加scope前缀.这将为您提供该用户所属数组中的所有组，因此，每当您使用JSON查看数据时，都可以以正确的方式查看该结构.
where detail() is my scope defined in User model as scopeDetail. Note: scope prefix must be attached. This will give you the user with all the groups that user belongs to in array, so whenever you view your data in JSON you can see the structure in proper way.
使用上述方法，我的 user 对象具有该用户所属的所有组.
Using above method, my user object has all the groups that user belongs to.
额外
如果您的用户模型也与其他模型相关，那么您可以通过将模型类的范围定义为
Extra
If your user model(users) related to other models too then you can include all those by defining scope on model class as
............
//..............
public function profile()
{
return $this->belongsToMany('App\Profile', 'user_id');
}
public function data1()
{
return $this->belongsToMany('App\Data1', 'user_id');
}
public function groups()
{
return $this->belongsToMany('App\Group', 'user_groups',
'user_id', 'group_id');
}
//Defining query scope................
public function scopeDetail($query)
{
return $query->with('groups','profile','data1');
//to fetch user with this scope use User::where(.....)->detail()->get(); notice there is not scope prefix while using the scope
}
........
........
这篇关于Laravel 5.5 Pivot Join获得MySQL主结果的Pivot值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
