$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.ġ. There are multiple ways how to get the raw SQL query for an Eloquent call – we are using Tinkerwell daily and so this is our natural way to debug queries.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. This is super useful if you debug server crashes or want to know how much memory your application server needs. You see all executed queries, the time that the query run, the memory consumption and even the peak for your memory. If you write your query in Tinkerwell anyway, you can highlight the Eloquent query and press Cmd+Shift+R (or Ctrl+Shift+R if you are on Windows) and Tinkerwell profiles the query directly in the editor. While you can get the raw SQL query with the methods above, there are situations where you debug the runtime or memory allocation of a query and want to know why it takes longer than expected. If the query has data bindings, the query logs lists them and makes it easy to check your data, Digging deeper This gives you detailed information about the executed query and their execution time. "query" => "select `courses`.*, `user_courses`.`user_id` as `pivot_user_id`, `user_courses`.`course_id` as `pivot_course_id`, `user_courses`.`created_at` as `pivot_created_ ▶" "query" => "select `apps`.*, `user_apps`.`user_id` as `pivot_user_id`, `user_apps`.`app_id` as `pivot_app_id`, `user_apps`.`created_at` as `pivot_created_at`, `user_apps`.` ▶" "query" => "select * from `users` where `created_at` array:1 [▼Ġ => Illuminate\Support\Carbon This code leads to the output: array:3 [▼ Output: select * from `users` where `created_at` where('created_at', 'subYear()) This method returns the query without running it – good if you don't want to alter data and only get the query – but this method doesn't show the whole query if your query is more complex or if there are sub-queries. The first method to get the query of an Eloquent call is by using the toSql() method. Luckily, there are multiple ways how to get this raw query. Sometimes, you ask yourself how you can get the Laravel query builder to output its raw SQL query as a string. How to get the raw SQL query from the Laravel Query Builder
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |