Laravel: Mysql Query to Eloquent

Question

How can I write the query below in Eloquent form? This query will give me ads on the basis of search term and then sort that data to show the nearest location. The $latitude and $longitude will be the GET parameters. My tables relation is LIKE, where the tbl_people table is related to the tbl_ads table and location. The query is working fine and now I need this query in eloquent form.

SELECT
  tbl_ads.Title,
  tbl_ads.Description,
  tbl_ads.Sub_Category_Id,
  tbl_ads.Image,
  tbl_ads.Date_Time,
  tbl_ads.Main_Category_Id,
  tbl_ads.People_Id,
  tbl_location.Address,
  ROUND((
    6371 * ACOS(
      COS(RADIANS($latitude)) * COS(RADIANS(tbl_location.Latitude)) * COS(
        RADIANS(tbl_location.Longitude) - RADIANS($longitude)
      ) + SIN(RADIANS($latitude)) * SIN(RADIANS(tbl_location.Latitude))
    )
  ),2) AS DISTANCE
FROM
  tbl_ads
INNER JOIN
  tbl_people ON tbl_ads.People_Id = tbl_people.Id
INNER JOIN
  tbl_location ON tbl_people.Location_ID = tbl_location.Id
WHERE
  (
    tbl_ads.Title LIKE '%for%' OR tbl_ads.Description LIKE '%for%'
  )

So far i have written this query in raw format as follows but don't know how to include the DISTANCE column. By the way this query is working fine.

DB::table('users') ->join('posts', 'users.id', '=', 'posts.user_id') ->join('locations', 'users.id', '=', 'locations.id') ->select('posts.title', 'users.name', 'locations.address') ->get();

Do you need to use the model method or can you just use DB::select()? — Nigel Ren

MTA MTA · Accepted Answer · 2017-04-29T07:32:33

MySQL query in Laravel raw format:

DB::table('tbl_people')->join('tbl_ads', 'tbl_people.id', '=', 'tbl_ads.people_id')->join('tbl_location', 'tbl_people.id', '=', 'tbl_location.id')->select('tbl_ads.*
', 'tbl_people.*', 'tbl_location.address', DB::raw('ROUND((6371 * ACOS(COS(RADIANS(31.430443800000003)) * COS(RADIANS(tbl_location.lattitude)) * COS(RA
DIANS(tbl_location.longitude) - RADIANS(74.280726)) + SIN(RADIANS(31.430443800000003)) * SIN(RADIANS(tbl_location.lattitude)))),2) AS DISTANCE'))-
>where('tbl_ads.description','like','%for%')->orwhere('tbl_ads.title', 'like', '%for%')->get();

Laravel: Mysql Query to Eloquent

1 Answers