首页 文章

Laravel 5.5 pivot join用于获取主MySQL结果的透视值

提问于
浏览 次
0

我正在尝试在MySQL查询上加入数据透视表 . 基本上我选择的是用户，其中一个用户有多个子类别 .

所以本质上我的“sub_categories”关系，一个用户有很多子类别 . 但是因为我使用RAW选择，我不能选择/使用关系 . 相反，我必须使用连接 .

这是我的sub_categories表

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
        }
    ]

所以基本上问题是通过使用数据透视表设置的值来加入users表上的sub_categories表，而不依赖于eloquent关系表，而是使用SQL .

从 one user has many sub_categories, 开始，将sub_categories作为在主SQL查询上连接的数组值返回会很棒 .

php mysql laravel join pivot

1 回答

  • 1

    我有类似的情况，我和我的数据透视表一对多关系 . 在我的情况下，用户有多个组，我需要获取这些数据以及用户对象，无需额外的查询或没有JOIN . 见 Query scope 和一对多和 many to many with pivot on Laravel Doc.

    如果你想使用枢轴表获取数据，这是一个例子
    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');
    }
}

    Group Model:

    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 是pivotote表中的字段 .

    现在使用上面的架构获取数据（在控制器上）：

    User::where(.....)->detail()->first();

    其中 detail() 是我在用户模型中定义的范围 scopeDetail . 注意：必须附加 scope 前缀 . 这将为用户提供数组中用户所属的所有组，因此每当您使用JSON查看数据时，您都可以以正确的方式查看结构 .

    使用上述方法，我的 user 对象具有用户所属的所有组 .

    Extra
    如果您的用户模型（用户）也与其他模型相关，那么您可以通过将模型类的范围定义为包含所有这些模型

    ............
//..............
    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
    }
........
........
    回复于

相关问题