Suppose if I have a table of ID Col1 Col2 Col3 Col4 Col5 1 Hello "" Hi "" "" 2 two "" Hi here "" I want to get the count of Col(X) without including the empty column. So for record 1 I must have a count of 2 and for record 2 I have ..
Category : laravel-query-builder
I want to Filter my Query by the Result of the Subquery in the initial Query when i run the code above i get an error says that column printed not found $data = Order:: join(‘clients’, ‘orders.client_id’, ‘=’, ‘clients.id’) ->join(‘addresses’, ‘orders.address_id’, ‘=’, ‘addresses.id’) ->join(‘br_codes’, ‘br_codes.id’, ‘=’, ‘orders.br_code_id’) ->join(‘users’, ‘orders.user_id’, ‘=’, ‘users.id’) ->join(‘cities’, ‘addresses.city_id’, ‘=’, ‘cities.id’) ..
I’m going to convert this Eloquent query to DB query in Laravel. $users = User::withCount("addresses", "cars")->get(); The above one is Eloquent and it’s working well. But I’d like to do this with DB query. I tried to do as following but I couldn’t get expected result. $users = DB::table("users")->join("addresses", "users.id", "=", "addresses.user_id") ->join("cars", "users.id", "=", ..
After I executed the query below Laravel display an error saying the enterpriseDB.employee_detail does not exist. Both enadopodDB and enterpriseDB are two different databases. $query = DB::connection(‘enadopodDB’,’enterpriseDB’) ->table(‘affected_employee’) ->join(‘enterpriseDB.employee_detail’, ‘enterpriseDB.employee_detail.employee_detail_id’, ‘=’, ‘affected_employee.employee_id’) ->select(‘affected_employee.*’, ‘enterpriseDB.employee_detail.full_name’) ->get(); var_dump($query); If the query builder is wrong, please suggest a solution for it. Sourc..
I’m trying to get last month order items report. Here is my table structure orders id – integer amonut- double order_items (pivot table) id – integer order_id – foreign item_id – foreign quantity price I want to get last month’s order items quantity and total price respectively. Like this Item A – 20 qty – ..
I have three tables called countries, states & cities. countries table schema: id country_name states table schema: id state_name country_id cities table schema: id city_name state_id country_id Now I need to output like : { country_name: United States, states counts of all states in the country, cities: counts of all cities in the country } ..
Im trying to create a laravel project using laravel new proj_name but it shows always showing that composer is not recognized as an internal or external command operable program or batch file i have set my environmental variable as C:UsersadminAppDataRoamingComposervendorbin; and my pathext as .PHAR; dont where i made the mistake, it works perfectly with ..
I have two models with relations as defined below Order public function owner() { return $this->belongsTo(User::class, ‘owner_id’); } User public function company(){ return $this->belongsTo(Company::class, ‘company_id’); } company table have ‘title’ field. what I want is to get all the orders sorted/order by company title. I’ve tried different solution but nothing seems to work. Any help ..
SELECT DISTINCT tasks.id, tasks.category_id, (CASE WHEN (SELECT task_id FROM completed_user_tasks WHERE user_id = 1 AND task_id = tasks.id) THEN true ELSE false END) AS completed FROM tasks WHERE tasks.id NOT IN ( SELECT task_id FROM skipped_user_tasks WHERE user_id = 1 UNION SELECT task_id FROM shown_user_tasks WHERE user_id = 1 UNION SELECT task_id FROM current_user_tasks WHERE ..
I have three tables: super_consumers , balance_ins, recharges super_consumers table: id name ———- —- 1 A 2 B balance_ins table: id super_consumer_id amount type(1,2) — —————– —— —- 1 1 10 1 2 1 20 1 3 2 10 2 recharges table: id super_consumer_id amount status(0,1) — —————– —— —— 1 1 5 1 2 ..
I´m traying to create one query for get all dates to commercial. i need this return: commercial 1 – 3 commercial 2 – 4 commercial 5 – 2 … but i need get thid dates from current date. I´m traying this: select count(id) from cita group by id_comercial having date(‘created_at’) = CURDATE() this return error ..
I have created a search system for my admin comment section. there I have created a tab for each approved and unapproved, each tab has pagination for itself and there is a search box at top of the tabs. so when I want to return the result of searching,here is my query for searching comments ..
I have a model Survey which is related to hasOne another model Installation which is related to hasMany another model Assignment. So i defined a hasManyThrough relationship like this public function assignments() { return $this->hasManyThrough(Assignment::class,Installation::class); } I want to write a query to fetch any Survey where the Assignments associated with the survey does not ..
I have three tables as shown in the picture. I would like to run a query that returns: Total_Parcel_Value, Total_Parcel_Delivery of parcels from all orders where customer = A. Sourc..
Good day devs! I need your opinion on what is the best possible (MySQL or Laravel Query Builder) query on the sample data provided below. **tbl_transactions** id, item_code, quantity 1, 0001, 6 2, 0001, 4 3, 0002, 7 4, 0002, 3 How do I get all the sum of all the distinct items? e.g. 0001 ..
Good Day, I do have 2 tables loan_account_installment and loan_account_installment_repayment related to each other. If repayment was made row will be created at loan_account_installment_repayment. What I want to happen is to deduct the payment made on loan_account_installment_repayment on loan_account_installment table. Unfortunately Im having an error. Call to undefined method stdClass::update(). By the way this is ..
I need to get Records so that an array of ids come at top of collection. $ids = [15, 20]; I tried : $list = $list->orderByRaw("field(id,".implode(‘,’,$id).")"); But this is working only with whereIn : $list = $list->whereIn(‘id’,$ids)->orderByRaw("field(id,".implode(‘,’,$id).")"); But i need to fetch all records but Ids 15 and 20 at the top. How to achieve ..
I just want to know, can I create ‘eloquent relationship’ using query builder on Laravel. Is there a way to do that? Example: I have two table users ======== user_id user_name events ======== event_id event_name user_id I know that if I want to select event and want to show the user who create the event ..
how can I get raw update query from code below: (I just need a query. I do not want to run it) TestTable::query()->where(‘id’,’=’,1)->update([‘name’=>’test name’]); to this : UPDATE `test_table` SET `name` = ‘test name’ WHERE `test_table`.`id` = 1; Sourc..
How can I write this query with Laravel Eloquent "SELECT * FROM links WHERE (user_id = 1 OR user_ip = ‘127.0.0.1’) AND id = 53 LIMIT 1" I have written this one: ` $link = Link::query() ->where(‘user_id’, Auth::id()) ->orWhere(‘user_ip’, request()->ip()) ->with(‘clicks’) ->findOrFail($id); And the SQL version of this one is :"SELECT * FROM links WHERE ..
I can select country and groupBy the result as following with an alias total : Data::select(‘country’, DB::raw(‘count(*) as total’)) ->groupBy(‘country’) ->get(); The above code works well. Here I can select only country filed, But I need to select aslo name, positions field, How can I select multiple field here? Sourc..
I am trying to fetch data using slug between 2 table, When I am using Eloquent then everything is fine but How to convert that query in Query Builder. Eloquent Query : $results = Product::orderBy(‘id’,’desc’)->with(‘categories’)->whereHas(‘categories’, function ($query){ $query->where(‘slug’, request()->sub_category); })->paginate(24); Here I am using two tables product and categories. Query Builder : $results = DB::table(‘products’) ..
Like I have records in database like : id name page keywords 1 abc x key1 2 xyz x key2 3 aaa y key3 4 bbb y key4 5 ccc p key5 I need output like [ x: { key1 => abc, key2 => xyz, }, y: { key3 => aaa, key4 => bbb, }, ..
I’m making an audit system that has to pull random records from an orders table based on certain criteria. I’m working with a table called orders and a table called transactions. Orders has a one-to-many relationship with Transactions (one order can have many transactions). I need to join the two tables, but I only want ..
I am quite new to laravel especially when it comes to retrieving things from a database. After reading their documentation about query builder I found a section that shows example code to do exactly what I need: If you don’t need an entire row, you may extract a single value from a record using the ..
Web Development Company
Free Hosting Company
