laravel-query-builder – Laravel Questions

Category : laravel-query-builder

Laravel Spatie Query Builder – create OR condition for filters

29th July 2021 laravel, laravel-query-builder, php, spatie

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 ..

How to index table column of specific database with code in laravel

29th July 2021 eloquent, laravel, laravel-8, laravel-query-builder

I want to write code to index the column of a specific database table column. Iam trying in this way DB::connection(‘mysql2’)->raw("ALTER TABLE `consignments` DROP INDEX customer_reference"); What is correct method for this process Sourc..

How to merge two same queries from two different tables in laravel?

29th July 2021 eloquent, laravel, laravel-query-builder, mysql

I am trying to apply the same query from two different tables. Actually, data is the same in both tables So, If accroding to query data is present in both the tables it should get and merge. I know there is a function merge which merge two tables result but problem is here I am ..

Php Mysql find in set query /// i want to convert this query in laravel query builder

28th July 2021 find-in-set, laravel, laravel-query-builder, mysql, php

select * from settings where setting_type=’admin_privilege’ and FIND_IN_SET (‘001’,`setting_value`) and br_id=4 Sourc..

How can I convert MYSQL query into Laravel Eloquent ORM [closed]

27th July 2021 laravel, laravel-query-builder, mysql, php

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 ..

How can We convert raw query to laravel query

25th July 2021 laravel, laravel-query-builder, left-join, mysql, subquery

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 ..

Laravel Explode Ajax Request Containing Name and Numbers

21st July 2021 laravel, laravel-query-builder

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’: ..

query laravel 5.6 whereNull condition, return empty array

20th July 2021 eloquent, laravel, laravel-5, laravel-query-builder

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 ..

Laravel query with difference of sum of two columns

19th July 2021 eloquent, laravel, laravel-8, laravel-query-builder

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") ..

i found some syntax error using where function in query (laravel)

17th July 2021 eloquent, laravel, laravel-5, laravel-query-builder

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′) ..

Laravel: Query builder

16th July 2021 laravel, laravel-query-builder, php, sql

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 ..

take sum of all members of groupBy in laravel

15th July 2021 eloquent, laravel, laravel-8, laravel-query-builder

I using groupBy week to get result from database before that i am usin WithSum() to take sum of relation table column . it is working fine but it is only taking sum of first member of groupBy for example if there are 20 member it will only show the sum of first member Consignment::Select(DB::raw(‘week(created_at) ..

How to get Result from database groupBy week in laravel

15th July 2021 eloquent, laravel, laravel-8, laravel-query-builder

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 ..

Laravel select not all related model

13th July 2021 laravel, laravel-query-builder, relation

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 ..

laravel when query with array requests

10th July 2021 eloquent, laravel, laravel-query-builder

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) ..

Problems in laravel query(groupBy)

10th July 2021 eloquent, laravel, laravel-query-builder, mysql

This is my database structure: I want to check if the thread already exist so I can decide to insert a message or create a thread. This is my code so far: $thread_exists = Participant::whereIn(‘user_id’, [$student->id, auth()->id()]) ->groupBy(‘thread_id’) ->get(); if(count($thread_exists) === 2) { get thread and insert a message } else { create thread and ..

How to conver query with many joins and inner select to Laravel ORM

9th July 2021 laravel, laravel-query-builder, orm

Hi guys please help solve this problem. Need to convert this query to Laravel ORM. $user = Auth::user(); $datas = DB::select(‘select * from kvits k left join (select o.kvit_id, sum(o.amount * o.price) as sum from operations o group by o.kvit_id) s on k.id = s.kvit_id left join users u on k.user_id = u.id left join ..

How to convert query with many joins and inner select to Laravel ORM

9th July 2021 laravel, laravel-query-builder, orm

Hi guys please help solve this problem. Need to convert this query to Laravel ORM. $user = Auth::user(); $datas = DB::select(‘select * from kvits k left join (select o.kvit_id, sum(o.amount * o.price) as sum from operations o group by o.kvit_id) s on k.id = s.kvit_id left join users u on k.user_id = u.id left join ..

Can I build this query using Laravel’s Query Builder and if so, what would that look like? (DB::raw())

8th July 2021 eloquent, laravel, laravel-query-builder, sql

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 / ..

What’s wrong with it?

8th July 2021 laravel, laravel-7, laravel-query-builder

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 ..

Laravel search query doesn’t get results in all cases even though it should

8th July 2021 laravel, laravel-7, laravel-query-builder, php

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 ..

Find a sentence that contains a word in text from database

7th July 2021 laravel, laravel-query-builder, mysql, php

I want to explain what I am trying to accomplish with an example. Let’s assume I have two rows in my posts table. It snows a lot in winter in Norway. While it barely snows where I live. I run four miles every morning. I am trying to get fit. When I search in winter ..

Convert SQL leftjoin subquery to querybuilder laravel

6th July 2021 laravel, laravel-query-builder, query-builder

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 ..

How do i join 2 substring indexes in Query Builder?

5th July 2021 laravel, laravel-query-builder, php, query-builder

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 ..

connecting a relationship between siblings in Php / Laravel / msql

1st July 2021 laravel, laravel-collection, laravel-query-builder, php

I am currently connecting a relationship between siblings. Issue: If Im on the main person with list of siblings and have record to database, but whenever I go to the one of the sibling, I can only view the main person, but not the other siblings. letters represents siblings first scenario – (a : b,c,d,e,f) ..