Laravel soar – an easy extension package for optimizing SQL statements in laravel

Time:2021-7-8

laravel-soar-In the laravel application, it is easy to optimize the expansion package of SQL statement.

Laravel soar - an easy extension package for optimizing SQL statements in laravel

function

  • Support sentence optimization based on heuristic algorithm
  • Multi column index optimization (update, insert, delete, select) for complex queries
  • Support explain information rich interpretation
  • Support SQL fingerprint, compression and beautification
  • Support eloquest query builder method to generate SQL optimization report

Source code link

Related projects

Environmental requirements

  • laravel >= 5.5

install

$ composer require guanguans/laravel-soar --dev -vvv

Publishing services

$ php artisan vendor:publish --provider="Guanguans\\LaravelSoar\\SoarServiceProvider"

use

Example of generating SQL scoring Report

use App\Models\Member;
    
Member::query()
    ->select([
        'id',
        'nickname',
    ])
    ->where('id', 100)
    // ->toSoarScore()
    // ->dumpSoarScore()
    ->ddSoarScore()
;

Laravel soar - an easy extension package for optimizing SQL statements in laravel

//Example of using query builder
DB::table('yb_member')
    ->select('*')
    ->join('yb_member_account as yb_member_account', 'yb_member_account.member_id', '=', 'yb_member.id')
    ->whereRaw('1 <> 1')
    ->where('yb_member.nickname', 'like', 'admin')
    ->where('yb_member.username', 'like', '%admin%')
    ->whereRaw("substring(yb_member.username, 1, 5) = 'admin'")
    ->whereIn('yb_member.id', [110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120])
    ->orWhereNotNull('yb_member.realname')
    ->groupByRaw("yb_member.status, '100'")
    ->having('yb_member.id', '>', '100')
    ->inRandomOrder()
    //- > tosoarscore() // generate SQL score report
    //- > dumpsoarscore() // print SQL score report
    ->Ddsoarscore() // print the SQL score report and exit the application.
;

Laravel soar - an easy extension package for optimizing SQL statements in laravel

Example of generating explain information interpretation report

//Example of using query builder
DB::table('yb_member')
    ->select('*')
    ->join('yb_member_account as yb_member_account', 'yb_member_account.member_id', '=', 'yb_member.id')
    ->whereRaw('1 <> 1')
    ->where('yb_member.nickname', 'like', 'admin')
    ->where('yb_member.username', 'like', '%admin%')
    ->whereRaw("substring(yb_member.username, 1, 5) = 'admin'")
    ->whereIn('yb_member.id', [110, 120])
    ->orWhereNotNull('yb_member.realname')
    ->groupByRaw("yb_member.status, '100'")
    ->having('yb_member.id', '>', '100')
    ->inRandomOrder()
    //- > tosoarhtmlexplain() // generate explain information interpretation report
    //- > dumpsoartmlexplain() // print the explain information interpretation report
    ->Ddsoartmlexplain() // print the explain information interpretation report and exit the application.
;

Laravel soar - an easy extension package for optimizing SQL statements in laravel

Beautify SQL statement

//Example of using query builder
DB::table('yb_member')
    ->select('*')
    ->join('yb_member_account as yb_member_account', 'yb_member_account.member_id', '=', 'yb_member.id')
    ->whereRaw('1 <> 1')
    ->where('yb_member.nickname', 'like', 'admin')
    ->where('yb_member.username', 'like', '%admin%')
    ->whereRaw("substring(yb_member.username, 1, 5) = 'admin'")
    ->whereIn('yb_member.id', [110, 120])
    ->orWhereNotNull('yb_member.realname')
    ->groupByRaw("yb_member.status, '100'")
    ->having('yb_member.id', '>', '100')
    ->inRandomOrder()
    //- > tosoarpretty() // generate beautified SQL
    //- > dumpsoarpretty() // print the beautified SQL
    ->Dumpsoarpretty() // print the beautified SQL and exit the application.
;

Laravel soar - an easy extension package for optimizing SQL statements in laravel

Other examples

\Soar::score($sql);        //  Generate SQL scoring Report
\Soar::mdExplain($sql);    //  Generate the explain information interpretation report in markdown format
\Soar::htmlExplain($sql);  //  Generate HTML format of explain information interpretation report
\Soar::syntaxCheck($sql);  //  SQL syntax checking
\Soar::fingerPrint($sql);  //  Generate SQL fingerprint
\Soar::pretty($sql);       //  Beautify SQL
\Soar::md2html($sql);      //  Converting markdown format content into HTML format content
\Soar::help($sql);         //  Output the content of the ear help command
\Soar::exec($command);     //  Execute any ear command