{"id":18657,"date":"2020-09-10T20:49:58","date_gmt":"2020-09-10T20:49:58","guid":{"rendered":"http:\/\/www.devopsschool.com\/blog\/?p=18657"},"modified":"2021-10-29T06:28:49","modified_gmt":"2021-10-29T06:28:49","slug":"query-builder-for-beginner","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/query-builder-for-beginner\/","title":{"rendered":"Query Builder for Beginner In Laravel"},"content":{"rendered":"\n

1.Find Unique column and avoid duplicate column<\/strong><\/p>\n\n\n\n

3 method using distinct, groupby, pluck command<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

OR<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

OR<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

In above table triptitle field land of rising sun and windy city r unique land of rising 3 times repeated<\/p>\n\n\n\n

2 Find unique record<\/strong><\/p>\n\n\n\n

<\/p>\n\n\n\n

$categories = Rvsp::distinct()->get();<\/p>\n\n\n\n

3.count total no of rows<\/strong><\/p>\n\n\n\n

<\/p>\n\n\n\n

here total 4 rows are there<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

$Trips = DB::table(‘rvsps’)
->select(‘TripTitle’)
->distinct()->count();
return $Trips;<\/p>\n\n\n\n

count{{$Trips}} in blade file<\/p>\n\n\n\n

<\/p>\n\n\n\n

4.display unique field and their total no of entries<\/strong><\/p>\n\n\n\n

here trip title Land of the Rising Sun 3 times entry in table triptitle Windy City one times see in below table mentioned in screenshot image<\/p>\n\n\n\n

<\/p>\n\n\n\n

$touroperator = Rvsp::where(‘TripTitle’,$TripTitle)->latest()->paginate(20);
->select(‘TripTitle’, DB::raw(‘COUNT(TripTitle) as total_member’))
->groupBy(‘TripTitle’)
->havingRaw(‘COUNT(TripTitle) > 0’)
->get();<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

5.display particular colunm that is requested in blade file<\/strong><\/p>\n\n\n\n

first check requested value in blade file<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

$tour->TripTitle is requested<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

requested value is land of rising sun<\/p>\n\n\n\n

now find total record of requested value using this query
$touroperator = Rvsp::where(‘TripTitle’,$TripTitle)->latest()->paginate(20);<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

6.display particular colunm that is present in another table based on condition<\/strong><\/p>\n\n\n\n

display all city of india <\/p>\n\n\n\n

here three table city,state,country is present<\/p>\n\n\n\n

Firstly retrive all state from state table that is under india where india id is 101 using pluck command<\/p>\n\n\n\n

  $states = State::where(‘states.country_id’,’101′)->get();<\/strong><\/p>\n\n\n\n

then pass this variable to where condition to match all cities that is under india state<\/p>\n\n\n\n

 $city = DB::table(‘cities’)->whereIn(‘cities.state_id’, $states)->get();<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n