How to write raw query in Laravel

Question

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";

Qirel · Accepted Answer · 2020-07-29 07:22:16Z

12

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

edited Jul 29, 2020 at 7:22

Qirel

26.6k7 gold badges54 silver badges71 bronze badges

answered Feb 20, 2019 at 7:21

Abdul Rehman Sheikh

9399 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Abdul Rehman Sheikh Over a year ago

Even you don't need to use DB::raw() to select by raw query. DB:select() will do everything for you.

Hemant Maurya Over a year ago

Use DB; //top of the controller and it's perfect. Thank, It takes few min to make it green :D :D :D

Abdul Rehman Sheikh Over a year ago

Use DB; or you can use \DB::select($sql); as well.. Thanks its laravel's beauty brother

Paul Spiegel · Accepted Answer · 2019-02-20 10:09:34Z

6

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();

edited Feb 20, 2019 at 10:09

answered Feb 20, 2019 at 8:33

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

1 Comment

tabacitu Over a year ago

This 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 ;-)

Collectives™ on Stack Overflow

How to write raw query in Laravel

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related