Laravel 9（Pest）集成测试失败：视图表CONCAT函数不存在报错

问题背景 #

在Laravel 9（Pest测试框架）中编写集成测试时，创建关联view_customers视图中客户的Address记录时失败。视图通过迁移创建，Address模型验证规则要求customers_id存在于该视图的id字段中。

视图迁移代码 #

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up(): void
    {
        DB::statement($this->createCustomersView());
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement($this->dropCustomersView());
    }

    private function createCustomersView() : string
    {
        return "CREATE VIEW view_customers AS
                    SELECT * FROM (
                        SELECT id, 'individuals' AS customer_type,
                        identification, identification_type,
                        CONCAT(first_name, ' ', IFNULL(middle_name,''), ' ',
                        last_name, ' ', IFNULL(second_last_name,'')) AS name,
                        primary_phone_number, created_at
                        FROM individuals
                        UNION
                        SELECT id, 'businesses' AS customer_type,
                        identification, identification_type, name,
                        primary_phone_number, created_at
                        FROM businesses
                    ) AS customers
                ORDER BY created_at DESC";
    }

    private function dropCustomersView() : string
    {
        return "DROP VIEW IF EXISTS view_customers";
    }
};

Address验证规则 #

'customers_id' => [
                'required',
                'string',
                Rule::exists('view_customers', 'id'),
            ],

测试代码 #

function baseTestAddress(string $customerType="") : array
{
    if (empty($customerType)) {
        // randomly select one type
        $types = ['business', 'individual'];
        $customerType = $types[array_rand($types)];
    }

    $customer = $customerType == 'individual' ? Individual::factory()->create() : Business::factory()->create();

    return [
        "customers_id" => $customer->id,
        "address_line_1" => "1600 Pennsylvania Avenue NW",
        "address_line_2" => "",
        "city" => "Washington",
        "state" => "DC",
        "zipcode" => "20500",
        "country" => "US",
        "notes" => "",
        "phone_number_1" => "6464698383",
        "phone_number_1_type" => "cel",
        "phone_number_1_extension" => "",
        "phone_number_2" => "",
        "phone_number_2_type" => "",
        "phone_number_2_extension" => "",
        "active" => true
    ];
}

it('should create a new address record when all provided values are valid', function () {
    $address = baseTestAddress();
    $response = $this->postJson(ADDRESSES_URI, $address);
    
    expect($response->status())->toEqual(Response::HTTP_CREATED)
        ->and($response->json('data'))
        ->id->not->toBeNull()
        ->type->toBe(ADDRESSES_RESOURCE_TYPE)
        ->attributes->toBeArray()
        ->attributes->customers_id->toBe($address['customers_id'])
        ->attributes->address_line_1->toBe($address['address_line_1'])
        ->attributes->address_line_2->toBe($address['address_line_2'])
        ->attributes->zipcode->toBe($address['zipcode'])
        ->attributes->state->toBe($address['state'])
        ->attributes->country->toBe($address['country'])
        ->attributes->notes->toBe($address['notes'])
        ->attributes->phone_number_1->toBe($address['phone_number_1'])
        ->attributes->phone_number_1_type->toBe($address['phone_number_1_type'])
        ->attributes->phone_number_1_extension->toBe($address['phone_number_1_extension'])
        ->attributes->phone_number_2->toBe($address['phone_number_2'])
        ->attributes->phone_number_2_type->toBe($address['phone_number_2_type'])
        ->attributes->phone_number_2_extension->toBe($address['phone_number_2_extension'])
        ->attributes->active->toBeTrue();
})->only();

错误信息 #

"message": "SQLSTATE[HY000]: General error: 1 no such function: CONCAT (SQL: select count(*) as aggregate from "view_customers" where "id" = 3339d86c-088d-4c3a-8484-79e4821657f6)",
"exception": "Illuminate\Database\QueryException",

问题原因 #

Laravel默认测试环境使用SQLite内存数据库，而SQLite不支持MySQL的CONCAT()字符串拼接函数；生产环境使用的是支持CONCAT()的数据库（如MySQL），所以生产端调用正常，测试端触发函数不存在错误。

解决方案 #

方案1：兼容多数据库的视图创建逻辑 #

修改迁移文件中的createCustomersView方法，根据当前数据库驱动选择对应的字符串拼接语法：

private function createCustomersView() : string
{
    $driver = DB::connection()->getDriverName();
    
    $nameExpression = match($driver) {
        'sqlite' => "first_name || ' ' || IFNULL(middle_name,'') || ' ' || last_name || ' ' || IFNULL(second_last_name,'')",
        default => "CONCAT(first_name, ' ', IFNULL(middle_name,''), ' ', last_name, ' ', IFNULL(second_last_name,''))"
    };

    return "CREATE VIEW view_customers AS
                SELECT * FROM (
                    SELECT id, 'individuals' AS customer_type,
                    identification, identification_type,
                    {$nameExpression} AS name,
                    primary_phone_number, created_at
                    FROM individuals
                    UNION
                    SELECT id, 'businesses' AS customer_type,
                    identification, identification_type, name,
                    primary_phone_number, created_at
                    FROM businesses
                ) AS customers
            ORDER BY created_at DESC";
}

• SQLite使用||作为字符串拼接运算符
• MySQL、PostgreSQL等数据库保留原CONCAT()函数逻辑

方案2：测试环境使用与生产相同的数据库 #

修改phpunit.xml文件，将测试数据库切换为生产使用的数据库类型（如MySQL）：

<phpunit>
    <!-- 其他配置 -->
    <php>
        <server name="DB_CONNECTION" value="mysql"/>
        <server name="DB_HOST" value="127.0.0.1"/>
        <server name="DB_PORT" value="3306"/>
        <server name="DB_DATABASE" value="your_test_database"/>
        <server name="DB_USERNAME" value="root"/>
        <server name="DB_PASSWORD" value="your_password"/>
    </php>
</phpunit>

注意：需要提前创建测试专用数据库，并确保.env.testing中的数据库配置与上述一致。

总结 #

两种方案各有优劣：

• 方案1保留了SQLite测试的轻量和快速特性，同时兼容多数据库
• 方案2让测试环境更贴近生产，避免跨数据库语法差异问题

内容的提问来源于stack exchange，提问作者MrCujo