README-en.md

Laqu

[ Japanese | English ]

Laqu is Laravel Db Query Helper.

Features

• Can check the executed db query
  • Assertions in PHPUnit, sort by execution time, check queries after build, etc

Attention

This library is intended to be used during development.

See Also

【Laravel】実行されたDBクエリの確認ができるやつを書いた - Qiita

Requirement

• PHP 7.4+ or newer(7.4, 8.0)
• Laravel 6.x, 7.x, 8.x

Installation

Via composer.

$ composer require --dev shimabox/laqu

Develop.

$ git clone https://github.com/shimabox/laqu.git
$ cd laqu
$ composer install

Usage

QueryAssertion

To assert with PHPUnit whether the expected query is flowing.

<?php

use App\Repositories\ExampleRepository; // example.
use Laqu\QueryAssertion;
use Tests\TestCase;

class QueryAssertionTest extends TestCase
{
    use QueryAssertion;

    private $exampleRepository;

    protected function setUp(): void
    {
        parent::setUp();

        $this->exampleRepository = new ExampleRepository();
    }

    public function queryTest()
    {
        // Basic usage.
        $this->assertQuery(
            // Pass the process in which the query will be executed in the closure.
            fn () => $this->exampleRepository->findById('a123'),
            // Write the expected query.
            'select from user where id = ? and is_active = ?',
            // Define the expected bind values as an array.
            // (If there is nothing to bind, pass an empty array or do not pass the argument)
            [
                'a123',
                1,
            ]
        );

        // Assert multiple queries.
        // Basically, it's a good idea to look at one query in one method,
        // but there are cases where one method executes multiple queries.
        // In that case, define the query and bind value as an array pair as shown below.
        $this->assertQuery(
            // For example, if multiple queries are executed in this process
            fn () => $this->exampleRepository->findAll(),
            // Define an array for each expected query.
            [
                'select from user where is_active = ?', // ※1
                'select from admin_user where id = ? and is_active = ?', // ※2
                'select from something', // ※3
            ],
            // Define the bind values ​​as a two-dimensional array (pass empty array if there is nothing to bind).
            [
                [ // ※1.
                    1,
                ],
                [ // ※2.
                    'b123',
                    1,
                ],
                // ※3 is no bind.
                [],
            ]
        );
    }
}

QueryAnalyzer

You can check what queries were flowed by passing the method by which the queries were flowed.
You can get the result (LaqueryAnalyzer\QueryList) of the query executed by QueryAnalyzer::analyze().
The QueryList has a LaquAnalyzerQuery.

<?php

use Laqu\Facades\QueryAnalyzer;

/** @var Laqu\Analyzer\QueryList **/
$analyzed = QueryAnalyzer::analyze(function () {
    $author = Author::find(1);
    $author->delete();
});

/*
Laqu\Analyzer\QueryList {#345
  -queries: array:2 [
    0 => Laqu\Analyzer\Query {#344
      -query: "select * from "authors" where "authors"."id" = ? limit 1"
      -bindings: array:1 [
        0 => 1
      ]
      -time: 0.08
      -buildedQuery: "select * from "authors" where "authors"."id" = 1 limit 1"
    }
    1 => Laqu\Analyzer\Query {#337
      -query: "delete from "authors" where "id" = ?"
      -bindings: array:1 [
        0 => "1"
      ]
      -time: 0.03
      -buildedQuery: "delete from "authors" where "id" = '1'"
    }
  ]
}
*/
dump($analyzed);

// select * from "authors" where "authors"."id" = 1 limit 1
echo $analyzed[0]->getBuildedQuery();
// delete from "authors" where "id" = '1'
echo $analyzed[1]->getBuildedQuery();

/*
Laqu\Analyzer\Query {#337
  -query: "delete from "authors" where "id" = ?"
  -bindings: array:1 [
    0 => "1"
  ]
  -time: 0.03
  -buildedQuery: "delete from "authors" where "id" = '1'"
}
*/
dump($analyzed->extractFastestQuery());

/*
Laqu\Analyzer\Query {#344
  -query: "select * from "authors" where "authors"."id" = ? limit 1"
  -bindings: array:1 [
    0 => 1
  ]
  -time: 0.08
  -buildedQuery: "select * from "authors" where "authors"."id" = 1 limit 1"
}
*/
dump($analyzed->extractSlowestQuery());

/*
array:2 [
  0 => Laqu\Analyzer\Query {#337
    -query: "delete from "authors" where "id" = ?"
    -bindings: array:1 [
      0 => "1"
    ]
    -time: 0.03
    -buildedQuery: "delete from "authors" where "id" = '1'"
  }
  1 => Laqu\Analyzer\Query {#344
    -query: "select * from "authors" where "authors"."id" = ? limit 1"
    -bindings: array:1 [
      0 => 1
    ]
    -time: 0.08
    -buildedQuery: "select * from "authors" where "authors"."id" = 1 limit 1"
  }
]
*/
dump($analyzed->sortByFast());

/*
array:2 [
  0 => Laqu\Analyzer\Query {#344
    -query: "select * from "authors" where "authors"."id" = ? limit 1"
    -bindings: array:1 [
      0 => 1
    ]
    -time: 0.08
    -buildedQuery: "select * from "authors" where "authors"."id" = 1 limit 1"
  }
  1 => Laqu\Analyzer\Query {#337
    -query: "delete from "authors" where "id" = ?"
    -bindings: array:1 [
      0 => "1"
    ]
    -time: 0.02
    -buildedQuery: "delete from "authors" where "id" = '1'"
  }
]
*/
dump($analyzed->sortBySlow());

Helper

QueryLog

QueryLog is a wrap on Basic Database Usage - Laravel - The PHP Framework For Web Artisans process.
※ Execution time is not that precise.

<?php

use Laqu\Facades\QueryLog;

$queryLog = QueryLog::getQueryLog(fn () => Author::find(1));

/*
array:1 [
  0 => array:3 [
    "query" => "select * from "authors" where "authors"."id" = ? limit 1"
    "bindings" => array:1 [
      0 => 1
    ]
    "time" => 0.12
  ]
]
*/
dump($queryLog);

QueryHelper

You can pass a query and bind parameters to see the query to be executed.
Referenced from pdo-debug/pdo-debug.php at master · panique/pdo-debug.

<?php

use Laqu\Facades\QueryHelper;

$now  = Carbon::now();
$from = $now->copy()->subDay();
$to   = $now->copy()->addDay();

$query = 'select * from authors where id in (?, ?) and name like :name and updated_at between ? and ?';

$bindings = [
    1,
    2,
    '%Shakespeare',
    $from,
    $to,
];

$buildedQuery = QueryHelper::buildedQuery($query, $bindings);

// select * from authors where id in (1, 2) and name like '%Shakespeare' and updated_at between '2020-07-07 00:37:55' and '2020-07-09 00:37:55'
echo $buildedQuery;

QueryFormatter

QueryFormatter is a wrapper for Doctrine\SqlFormatter\SqlFormatter.
@see doctrine/sql-formatter: A lightweight php class for formatting sql statements. Handles automatic indentation and syntax highlighting.

The default is to use NullHighlighter, but it can also be formatted in CLI or HTML.

format()

The default Highlighter is Doctrine\SqlFormatter\NullHighlighter.

<?php

use Laqu\Facades\QueryFormatter;

$query = "SELECT count(*),`Column1`,`Testing`, `Testing Three` FROM `Table1`
    WHERE Column1 = 'testing' AND ( (`Column2` = `Column3` OR Column4 >= NOW()) )
    GROUP BY Column1 ORDER BY Column3 DESC LIMIT 5,10";

/*
SELECT
  count(*),
  `Column1`,
  `Testing`,
  `Testing Three`
FROM
  `Table1`
WHERE
  Column1 = 'testing'
  AND (
    (
      `Column2` = `Column3`
      OR Column4 >= NOW()
    )
  )
GROUP BY
  Column1
ORDER BY
  Column3 DESC
LIMIT
  5, 10
*/
echo QueryFormatter::format($query);

If you use Doctrine\SqlFormatter\CliHighlighter, please inject CliHighlighter as follows.

<?php

use Doctrine\SqlFormatter\CliHighlighter;
use Laqu\Formatter\QueryFormatter;

/** @var QueryFormatter */
$formatter = app()->make(QueryFormatter::class/* or 'queryFormatter' */, [new CliHighlighter()]);

echo $formatter->format($query);

Output is

If you use Doctrine\SqlFormatter\HtmlHighlighter, please inject HtmlHighlighter as follows.

<?php

use Doctrine\SqlFormatter\HtmlHighlighter;
use Laqu\Formatter\QueryFormatter;

/** @var QueryFormatter */
$formatter = app()->make(QueryFormatter::class/* or 'queryFormatter' */, [new HtmlHighlighter()]);

echo $formatter->format($query);

Output is

highlight()

The usage is almost the same as the format().
Please refer to https://github.com/doctrine/sql-formatter#syntax-highlighting-only.

<?php

use Laqu\Facades\QueryFormatter;

$query = '...';

echo QueryFormatter::highlight($query);

compress()

compress() returns a query with all comments and superfluous whitespace stripped out.
Please refer to https://github.com/doctrine/sql-formatter#compress-query.

<?php

use Laqu\Facades\QueryFormatter;

$query = <<<SQL
-- This is a comment
SELECT
    /* This is another comment
    On more than one line */
    Id #This is one final comment
    as temp, DateCreated as Created FROM MyTable;
SQL;

// SELECT Id as temp, DateCreated as Created FROM MyTable;
echo QueryFormatter::compress($query);

Develop

Run php-cs-fixer.

check.

$ composer phpcs

fix.

$ composer phpcs:fix

Run phpstan(larastan).

check.

$ composer phpstan

Run test.

$ composer test

Run ci.

Run all the above commands at once.

$ composer ci

TODO

There are not yet enough query patterns.