return data from whereHas query in Laravel

Question

How would I go about returning the select data from the company whereHas sub-query?

$data = Job::where('active','=','1')
    ->whereHas('company', function($q) use ($distance_select) {
        $q->where('active','=','1')
        ->select(DB::raw($distance_select))
            ->whereHas('user', function($q) {
                $q->whereHas('group', function ($q) {
                    $q->whereIn('group.name_short', array('admin', 'moderator', 'subscriber'));
                });
            });
    });

I get all the data from the Job model. My raw query returns a distance value from the company sub-query.

Here is the sub-query for those interested.

if ( $units == 'miles' ) {
    $gr_circle_radius = 3959;
} elseif ( $units == 'kilometers' ) {
    $gr_circle_radius = 6371;
}

$distance_select = sprintf(
    "
      ROUND(( %d * acos( cos( radians(%s) ) " .
    " * cos( radians( lat ) ) " .
    " * cos( radians( lng ) - radians(%s) ) " .
    " + sin( radians(%s) ) * sin( radians( lat ) ) " .
    " ) " .
    ")
                        , 2 ) " .
    "AS distance
                        ",
    $gr_circle_radius,
    $lat,
    $lng,
    $lat
);

Everything works fine, I just want to be able to access the distance returned in the sub-query.

------------added------------

here is output of raw query

select * from `job` where `active` = '1' and (select ROUND(( 3959 * acos( cos( radians(38.15499960) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-85.66808610) ) + sin( radians(38.15499960) ) * sin( radians( lat ) ) ) ) , 2 ) AS distance from `company` where `job`.`company_id` = `company`.`id` and `active` = '1' and (select count(*) from `users` where `company`.`user_id` = `users`.`id` and (select count(*) from `group` inner join `group_user` on `group`.`id` = `group_user`.`group_id` where `group_user`.`user_id` = `users`.`id` and `group`.`name_short` in ('admin', 'moderator', 'subscriber')) >= 1 and `users`.`deleted_at` is null) >= 1) >= 1 limit 10

Jesse Szypulski · Accepted Answer · 2015-02-13 03:35:15Z

Figured it out after hours and hours of research.

Here is the new, working code

$data = Job::where('job.active','=',1)
    ->select(DB::raw("`job`.*, `company`.`lat` as `lat`, `company`.`lng` as `lng`, ROUND(( ? * acos( cos( radians(?) )  * cos( radians( `lat` ) )  * cos( radians( `lng` ) - radians(?) )  + sin( radians(?) ) * sin( radians( `lat` ) )  ) ) , 2 ) AS `distance`"))
    ->leftJoin('company', function($join) {
      $join->on('job.company_id', '=', 'company.id');
    })
    ->setBindings([$radius, $lat, $lng, $lat], 'select')
    ->whereHas('company', function($q) {
        $q->where('active','=',1)
        ->whereHas('user', function($q) {
            $q->whereHas('group', function ($q) {
                $q->whereIn('group.name_short', array('admin', 'moderator', 'subscriber'));
            });
        });
    });

And here is the raw query for those interested

select `job`.*, `company`.`lat` as `lat`, `company`.`lng` as `lng`, ROUND(( '3959' * acos( cos( radians('38.15499960') )  * cos( radians( `lat` ) )  * cos( radians( `lng` ) - radians('-85.66808610') )  + sin( radians('38.15499960') ) * sin( radians( `lat` ) )  ) ) , 2 ) AS `distance` from `job` left join `company` on `job`.`company_id` = `company`.`id` where `job`.`active` = '1' and (select count(*) from `company` where `job`.`company_id` = `company`.`id` and `active` = '1' and (select count(*) from `users` where `company`.`user_id` = `users`.`id` and (select count(*) from `group` inner join `group_user` on `group`.`id` = `group_user`.`group_id` where `group_user`.`user_id` = `users`.`id` and `group`.`name_short` in ('admin', 'moderator', 'subscriber')) >= 1 and `users`.`deleted_at` is null) >= 1) >= 1 limit 10

Collectives™ on Stack Overflow

return data from whereHas query in Laravel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related