How to write raw query in Laravel
10,420
Solution 1
You can get records by using raw query in laravel like:
$sql = 'SELECT table_name "Name_of_the_table", table_rows "Rows Count", round(((data_length + index_length)/1024/1024),2)
"Table Size (MB)" FROM information_schema.TABLES WHERE table_schema = "Name_of_the_Database" AND table_name ="Name_of_the_table"';
$results = DB::select($sql);
Solution 2
You can use the query builder, since you can minimize the raw part to the table size:
$data = DB::table('information_schema.TABLES')
->where('table_schema', 'Name_of_the_Database')
->where('table_name', 'Name_of_the_table')
->select(
'table_name as "Name_of_the_table"',
'table_rows as "Rows Count"',
DB::raw('round(((data_length + index_length)/1024/1024),2) as "Table Size (MB)"')
)
->first();
Author by
Hemant Maurya
Updated on June 21, 2022Comments
-
Hemant Maurya almost 2 years
I need to write a raw query in Laravel Database: Query Builder, That outputs size of specific table
In core mysql query is as following
SELECT table_name "Name_of_the_table", table_rows "Rows Count", round(((data_length + index_length)/1024/1024),2) "Table Size (MB)" FROM information_schema.TABLES WHERE table_schema = "Name_of_the_Database" AND table_name ="Name_of_the_table";
-
Abdulrehman Sheikh about 5 yearsEven you don't need to use DB::raw() to select by raw query. DB:select() will do everything for you.
-
Hemant Maurya about 5 yearsUse DB; //top of the controller and it's perfect. Thank, It takes few min to make it green :D :D :D
-
Abdulrehman Sheikh about 5 yearsUse DB; or you can use \DB::select($sql); as well.. Thanks its laravel's beauty brother
-
tabacitu over 3 yearsThis helped a lot, thank you! I've used this to get the number of rows for a very large table, when count errors. I'm just rephrasing the question so that it shows up when you search for that too ;-)