lundi 5 octobre 2015

Not able to convert SQL query into laravel eloquent

I have been trying since a couple of hours to convert a sql query (which fetches all the events along with the businesses which are hosted within 10 kilometer radius of provided coordinates) into laravel eloquent query

SELECT businesses.id AS business_id,
       businesses.name AS business_name,
       businesses.area AS business_area,
       businesses.address AS business_address,
       businesses.city AS business_city,
       businesses.country AS business_country,
       businesses.longitude AS business_longitude,
       businesses.latitude AS latitude,
       businesses.postcode AS business_postcode,
       businesses.category AS business_category,
       businesses.phone AS business_phone,
       businesses.timing AS business_timing,
       businesses.owner_id AS business_owner_id,
       businesses.latitude AS business_latitude,
       businesses.longitude AS business_longitude,
       events.id AS event_id,
       events.title AS event_title,
       events.type AS event_type,
       events.event_type AS event_event_type,
       events.time AS event_time,
       events.description AS event_description,
       events.venue AS event_venue,
       events.visibility AS event_visibility,
       events.picture AS event_picture,
       events.max_people AS event_max_people,
       events.admin_id AS event_admin_id,
       6371 * (acos(cos(radians (" . $latitude . ")) * cos(radians(`latitude`)) * cos(radians(`longitude`) - radians(" . $longitude . ")) + sin( radians(" . $latitude . ")) * sin(radians(`latitude`)))) AS distance
FROM businesses
JOIN events ON businesses.id = events.business_id
WHERE events.created_at > '" . $date . "'
  OR events.updated_at > '" . $date . "'
  OR businesses.created_at > '" . $date . "'
  OR businesses.updated_at > '" . $date . "'
HAVING distance <= 10 

what I have done so far is

$locations = DB::table('businesses')
        ->select('id as business_id', 'name as business_name','area as business_area', 'address as business_address',
            'city as business_city', 'country as business_country','longitude as business_longitude',
            'latitude as latitude','postcode as business_postcode', 'category as business_category',
            'phone as business_phone','timing as business_timing', 'owner_id as business_owner_id',
            'latitude as business_latitude', 'longitude as business_longitude',('6371 * (acos( cos(radians
                    (' . $latitude . ') ) * cos( radians( `latitude` ) ) * cos( radians( `longitude` ) - radians(' . $longitude . ') ) + sin(
                        radians(' . $latitude . ') ) * sin( radians( `latitude` )) ) ) AS distance'))
        ->join('events', 'events.business_id', '=', 'businesses.id')
        ->select('events.id as event_id', 'events.title as event_title', 'type as event_type', 'event_type as event_event_type',
            'time as event_time', 'description as event_description', 'venue as event_venue','visibility as event_visibility',
            'picture as event_picture', 'max_people as event_max_people', 'admin_id as event_admin_id')
    ->get();

but the above mentioned query is throwing error that column 6371 not found



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire