duandunzhuo3234 2017-10-13 15:01
浏览 75

Laravel 5.5 pivot join用于获取主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.

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.

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


and this is my pivot table


    Column  Type    Comment
    user_id int(10) unsigned     
    sub_category_id int(10) unsigned


This is my SQL query


$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();

And this is my result:


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

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.

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.

  • 写回答

1条回答 默认 最新

  • dspows0637 2017-10-13 15:16
    关注

    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');
        }
    }
    

    Group Model:

    class Group extends Model
    {
        protected $fillable = [
            'dn', 'cn', 'description',
        ];
    }
    

    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();
    

    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.

    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
        }
    ........
    ........
    
    评论

报告相同问题?

悬赏问题

  • ¥15 MATLAB动图问题
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题