lundi 26 juin 2017

laravel query with subquery

Hi I have following query with subquery in Laravel, my issue is that on the second query I use a Limit option to 10 but it always shows me one less (so 9 results ) when I use it, so I have to use Limit 11 do you know why?

$query = DB::table("categories_company")
    ->select("categories_company.*","region_company.*","company_info.latitude","company_info.longitude","company_info.comp_name","categories.*",
        DB::raw("(6371 * 2 * ASIN(SQRT(
                POWER(SIN(($lat - abs(company_info.latitude)) * pi()/180 / 2),
                2) + COS($lat * pi()/180 ) * COS(abs(company_info.latitude) *
                pi()/180) * POWER(SIN(($long - company_info.longitude) *
                pi()/180 / 2), 2) ))) AS distance ")
        )
    ->join(DB::raw("(SELECT 
      region_company.* FROM region_company WHERE region_company.region_id IN ($region) LIMIT 11 ) as region_company "),function($join){
                $join->on("region_company.company_id","=","categories_company.company_id");
    })
    ->join('company_info', 'categories_company.company_id', '=', 'company_info.company_id')
    ->join('categories', 'categories_company.category_id', '=', 'categories.id')
    ->whereIn('categories_company.category_id', $categories)
    ->groupBy('category_id', 'categories_company.company_id')
    ->orderBy('distance','asc')
    ->orderBy('category_id','asc')
    ->get();



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire