README

[ 日语 | 英语 ]

Laqu 是 Laravel Db Query Helper.

特性

可以查看已执行的数据库查询
- PHPUnit 中的断言、按执行时间排序、构建后查询的检查等

注意

此库旨在用于开发中。

另请参阅

【Laravel】写了可以查看执行DB查询的东东 - Qiita

需求

PHP 7.4+ 或更高版本(7.4, 8.0)
Laravel 6.x, 7.x, 8.x

安装

通过 composer。

$ composer require --dev shimabox/laqu

开发。

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

使用

QueryAssertion

用于通过 PHPUnit 断言期望的查询流。
trait，使用 assertQuery()。

<?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()
    {
        // 基本的な使い方
        $this->assertQuery(
            // クエリが実行される処理をクロージャに渡します
            fn () => $this->exampleRepository->findById('a123'),
            // 期待するクエリを書きます
            'select from user where id = ? and is_active = ?',
            // バインドされる値を配列で定義します
            // (bindするものがない場合は空配列を渡すか、引数は渡さないでOK)
            [
                'a123',
                1,
            ]
        );

        // 複数のクエリを確認
        // 基本的には 1メソッド1クエリの確認 を推奨しますが、中には1メソッドで複数クエリが流れる場合もあると思います。
        // その場合は下記のようにクエリとバインド値を配列で対になるように定義してください。
        $this->assertQuery(
            // 例えばこの処理で複数のクエリが流れるとします
            fn () => $this->exampleRepository->findAll(),
            // 期待するクエリをそれぞれ配列で定義します
            [
                'select from user where is_active = ?', // ※1
                'select from admin_user where id = ? and is_active = ?', // ※2
                'select from something', // ※3
            ],
            // バインドされる値を二次元配列で定義(bindするものがない場合は空配列を渡してください)
            [
                [ // ※1.
                    1,
                ],
                [ // ※2.
                    'b123',
                    1,
                ],
                // ※3 はバインド無しの想定なので空配列を渡します
                [],
            ]
        );
    }
}

QueryAnalyzer

将方法传递给查询流，以确认发生了何种查询。
使用 QueryAnalyzer::analyze() 获取已执行的查询结果 (Laqu\Analyzer\QueryList)。
※ QueryList 包含 Laqu\Analyzer\Query

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

助手

QueryLog

QueryLog 是对 Basic Database Usage - Laravel - The PHP Framework For Web Artisans 的封装。
※ 执行时间相关不太精确

<?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

传递查询和绑定参数，可以检查将要执行的查询。
参考了 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 是 Doctrine\SqlFormatter\SqlFormatter 的封装。
@see doctrine/sql-formatter: A lightweight php class for formatting sql statements. Handles automatic indentation and syntax highlighting.

默认使用 NullHighlighter，但也可以在 CLI、HTML 中格式化。

format()

默认的 Highlighter 是 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);

如果要使用 Doctrine\SqlFormatter\CliHighlighter，请按照以下方式注入 CliHighlighter。

<?php

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

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

echo $formatter->format($query);

输出结果

如果要使用 Doctrine\SqlFormatter\HtmlHighlighter，请按照以下方式注入 HtmlHighlighter。

<?php

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

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

echo $formatter->format($query);

输出结果

highlight()

用法与 format() 类似。
请参考 https://github.com/doctrine/sql-formatter#syntax-highlighting-only。

<?php

use Laqu\Facades\QueryFormatter;

$query = '...';

echo QueryFormatter::highlight($query);

compress()

compress() 返回移除了所有注释和多余空白的查询。
请参考 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);

开发

运行 php-cs-fixer。

检查。

$ composer phpcs

修复。

$ composer phpcs:fix

运行 phpstan(larastan)。

检查。

$ composer phpstan

运行测试。

$ composer test

运行 ci。

一次性运行上述所有命令。

$ composer ci

TODO

测试中确认的查询模式仍然很少。

shimabox / laqu

维护者

详细信息