Warning: be careful with parameters, always validate them In my experience, it’s often used in database migrations, when some table structure changes and old data needs to be updated with a new structure.ĭB::statement('UPDATE users SET role_id = 1 WHERE role_id IS NULL AND YEAR(created_at) > 2020') Īlso, DB::statement() can perform any SQL query with schema, outside of columns or values.ĭB::statement('ALTER TABLE projects AUTO_INCREMENT=123') If you need to execute some SQL query, without processing any results, like INSERT or UPDATE without any parameters, you can use DB::statement(). $results = DB::select('select * from users where id = ?', ) ĥ. DB::select() is a perfectly fine statement. >selectRaw("(CASE WHEN (gender = 1) THEN 'M' ELSE 'F' END) as gender_text")Ī pretty typical example is when you have an SQL statement from some older project, and you need to convert it to Eloquent or Query Builder. >selectRaw('price - discount_price AS discount') $products = Product::select('id', 'name') If you want to return one specific column as a calculation from other columns, and you want that calculation to happen in SQL query, here’s how it can look: Calculating one field with sub-query: selectRaw() $results = User::selectRaw('YEAR(birth_date) as year, COUNT(id) as amount')ģ. Also, we can use additional “where” statement after grouping, by “having” SQL statement with havingRaw().įor example, how to group by a YEAR of a certain date/time field? We have methods like groupByRaw() and orderByRaw() for this. What if you want to add some SQL calculations inside of “group by” or “order by”? Filtering YEARS: groupByRaw, orderByRaw and havingRaw >selectRaw('companies.name as company_name, avg(salary) as avg_salary, count(*) as people_count') >selectRaw('COUNT(*) as nbr', 'products.*')Īnother example – we can even perform avg() and count() in the same statement. >leftjoin('category','category.product_id','=','products.id') >selectRaw('count(*) as user_count, status') If you need to perform groupBy() and then use some aggregation function from MySQL, like AVG() or COUNT(), it’s useful to perform a Raw Query for that specific section. Most Typical: selectRaw() with Avg/Sum/Count Calculations In this article, I will show you the most common examples of this approach.ġ. Laravel has a great database mechanism called Eloquent, also a powerful Query Builder, but sometimes it makes sense to just use plain SQL, in the form of Raw Queries.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |