Category : laravel-query-builder

I am using laravel spatie for filtering the model i have following filters in allowed filter array $this->allowedFilters = [ ‘name’, AllowedFilter::exact(‘company_id’), ‘location’, ‘summary’, ‘client_name’, ]; when I make request like past-projects?filter[company_id]=${companyId}&filter[name]=${search}&filter[summary]={company_id} it searches for the data which exactly contains all the passed search params with AND condition and returns no results if any of ..

Read more

This is my query SELECT * FROM posts LEFT JOIN users ON posts.user_id=users.user_id LEFT JOIN myorders ON (myorders.from_user_id=posts.user_id OR myorders.to_user_id=posts.user_id AND myorders.to_user_id=:user_id) LEFT JOIN friends ON (posts.user_id=friends.receiver OR posts.user_id=friends.sender AND posts.pageid=”) LEFT JOIN pages ON posts.pageid=pages.pageid LEFT JOIN followers ON pages.pageid=followers.pageid AND followers.followerid=:user_id WHERE posts.user_id=:user_id OR (sender IN (SELECT receiver FROM friends WHERE sender=:user_id AND ..

Read more

I created a SQL script with subquery and join. I need to convert this raw query in laravel. Raw query is : select users.name, users.user_type, count(complaints.id) as total_case, sum(if(complaints.action = ‘Resolved’, 1, 0)) as total_resolved_case, sum(if(complaints.action = ‘Pending’, 1, 0)) as total_pending_case, sum(if(complaints.action = ‘Unrelated’, 1, 0)) as total_unrelated_case, sum(if(complaints.action = ‘Rejected’, 1, 0)) as ..

Read more

I’m using laravel 8.4 . My route : Route::post(‘test’,[AppHttpControllersDataController::class, ‘store’])->name(‘pos-test’); My Controller : public function store(Request $request) { // DB::table(‘data’)->insert(‘product_code’,$request->id); $badge = explode(‘ ‘, $request); $employee_id = $badge[0]; DB::table(‘data’)->insert([‘product_code’=> $employee_id]); return response()->json([‘success’=>’Product saved successfully.’]); } Ajax code : function handleBarcode(scanned_barcode) { //handle your code here…. console.log(scanned_barcode); let _token = $(‘meta[name="csrf-token"]’).attr(‘content’); event.preventDefault(); $.ajaxSetup({ headers: { ‘X-CSRF-TOKEN’: ..

Read more

I´m traying to create a query with whereNull() condition but, return this result: IlluminateSupportCollection Object ( [items:protected] => Array ( ) ) in my db table i have data whith empty column data: i need create my query with DB::table() and my query is: $result = DB::table(‘listado’) ->join(‘llamada AS ll’,’ll.id_listado’,’=’,’listado.id’) ->join(‘users AS tele’,’ll.id_teleoperadora’,’=’,’tele.id’) ->join(‘llamada_estado AS ..

Read more

I have a Laravel query that seems to have some issues. It is picking up all right things but does not calculate the difference of the sum of two fields. JournalLine::join(‘accounts’,’accounts.id’,’account_id’) ->select(‘accounts.name’,’accounts.code’,’accounts.id’,’accounts.type’) ->whereHas(‘account_fk’,function ($query) { $query->where(‘type’,’Liability’); }) ->whereHas(‘journal_fk’,function ($query) { $query->where(‘date’, ‘<=’, $this->as_of_date); }) ->selectRaw("SUM(credit) as credit") ->selectRaw("SUM(debit) as debit") ->selectRaw("SUM(credit) – SUM(debit) as amount") ..

Read more

i have two condition which i want to implement in a query but when i used i found some systax error but i did not how to solved it. error in the below: syntax error, unexpected ‘}’ and my controller code is : public function paymentHubList(Request $request, $id){ $Abc = new Abc(); $Abc = $Abc->where(‘status_id’,’1′)->where(‘payment’,’1′) ..

Read more

Might be stupid question, how to correctly state the ID in select statement? public function generatefinalconfirmation($id) { $booking = Booking::findOrFail($id); $flightin = DB::select(‘SELECT b.bookingname, f.origin FROM Bookings B JOIN Flights F ON B.Flightin = F.FlightId WHERE B.ID = id’); } That code give me all the data while where b.id = $id give me an ..

Read more

I am trying to get records from the database groupBy Week. I have two data start date and end data. Between these two dates, I want to get all the data groupBy weeks I know my below query is totally wrong . That is just for an idea Consignment::(‘created_at’,$request->start_date)->where(‘end_date’,$request->end_Date)->groupBy(‘Week’)->get() All the result should be groupBy ..

Read more

How to filter relationships? I found models from related data. How do I select only those related models that match the search term? Basic query $toDolists = ToDoList::select([‘*’]); foreach ($tags as $tag) { $toDolists->whereHas( ‘item.tag’, function ($query) use ($tag) { $query->where(‘tags.name’, $tag); } ); } I want to select related models (item) that match the ..

Read more

I have colors request in array $colors = collect($request->colors); i am trying when query builder for make request when its not empty $products = Product::when($colors, function ($query, $colors) { return $query->whereHas(‘colors’, function (Builder $query) use ($colors) { $query->whereIn(‘slug’, $colors->toArray()); }); }) it should not be trigger color query when i try when(!$colors->isEmpty(), function ($query, $colors) ..

Read more

I’ve put together this resulting query using Eloquent and Laravel’s Query Builder (mixed) and I’d like to remove that "mixed" and build the entire query using Laravel’s Query Builder. How would the following query look like with Laravel’s Query Builder? SELECT id, name, clicks_count, conversions_count, Round(((100 / clicks_count) * conversions_count), 2) AS conversion_rate, Cast((cost_integral / ..

Read more

What’s wrong with it? public function find(Request $request) { $validatedData = $request->validate([ ‘search’ => ‘required|max:255’, ]); $item = $request->search; $data = DB::table(‘products’) ->join(‘categories’,’products.category_id’,’categories.id’) ->join(‘subcategories’,’products.sub_category_id’,’subcategories.id’) ->join(‘sub_of_subcategories’,’products.sos_category_id’,’sub_of_subcategories.id’) ->select(‘products.*’,’categories.slug’,’categories.category_name’,’subcategories.sub_category_name’,’sub_of_subcategories.sub_of_sub_category_name’) ->where(‘products.product_name’,’LIKE’,"%{$item}%") ->orWhere(‘products.product_code’,$item) ->orWhere(‘categories.category_name’,’LIKE’,"%{$item}%") ->orWhere(‘subcategories.sub_category_name’,’LIKE’,"%{$item}%") ->orWhere(‘sub_of_subcategories.sub_of_sub_category_name’,’LIKE’,"%{$item}%") ->orWhere(‘products.product_description’,’LIKE’,"%{$item}%") ->paginate(40); return response()->json($data); } When I search by product name or product code or product description, it’s showing me the result. but, if ..

Read more

public function find(Request $request) { $validatedData = $request->validate([ ‘search’ => ‘required|max:255’, ]); $item = $request->search; $data = DB::table(‘products’) ->join(‘categories’,’products.category_id’,’categories.id’) ->join(‘subcategories’,’products.sub_category_id’,’subcategories.id’) ->join(‘sub_of_subcategories’,’products.sos_category_id’,’sub_of_subcategories.id’) ->select(‘products.*’,’categories.slug’,’categories.category_name’,’subcategories.sub_category_name’,’sub_of_subcategories.sub_of_sub_category_name’) ->where(‘products.product_name’,’LIKE’,"%{$item}%") ->orWhere(‘products.product_code’,$item) ->orWhere(‘categories.category_name’,’LIKE’,"%{$item}%") ->orWhere(‘subcategories.sub_category_name’,’LIKE’,"%{$item}%") ->orWhere(‘sub_of_subcategories.sub_of_sub_category_name’,’LIKE’,"%{$item}%") ->orWhere(‘products.product_description’,’LIKE’,"%{$item}%") ->paginate(40); return response()->json($data); } When I search by product name or product code or product description, it’s showing me the result. but, if I search by category ..

Read more

I have sql syntax like below. how to change this syntax to laravel querybuilder ? SELECT p.nama AS nama_provider, i.nama as nama_item, ifnull(pds.kuantitas_pembelian,0) as kuantitas_pembelian, ifnull(sds.kuantitas_retail,0) as kuantitas_retail, ifnull(sds.kuantitas_grosir,0) as kuantitas_grosir FROM items AS i JOIN providers AS p ON i.provider_id = p.id LEFT JOIN ( SELECT item_id, SUM(CASE WHEN purchases.tanggal = "2021-07-04" then purchase_details.kuantitas ..

Read more

So i have 2 RAW Substring indexes in my query builder which i need to join together. These values are used for exporting to excel. So this is the code i have. public function collection() { ‘bookings.user_fullname’, DB::raw("SUBSTRING_INDEX(user_fullname, ‘ ‘, 1) AS Firstname"), DB::raw("SUBSTRING_INDEX(user_fullname, ‘ ‘, 2) AS MiddleName"), DB::raw("SUBSTRING_INDEX(SUBSTRING_INDEX(user_fullname, ‘ ‘, 3),’ ‘,-1) AS ..

Read more