存储过程未返回关联表数据：多租户房产图片关联查询异常排查

问题背景 #

我需要排查一个存储过程的问题：该存储过程遍历所有租户数据库，获取房产及其关联图片。房产与图片通过imageables表实现多态关联，简化表结构如下：

properties
----------
id
title
location


images
--------
id
url


imageables
----------
image_id -- references images table
imageable_id -- references id on properties table
imageable_type -- e.g. App\Property or App\Room

对应的存储过程代码：

CREATE PROCEDURE `getAllPropertiesWithImages`()
    DETERMINISTIC
    COMMENT 'test'
BEGIN
    DECLARE queryString TEXT DEFAULT '';
    DECLARE tenant_db_id VARCHAR(255) DEFAULT '';
    DECLARE done INTEGER DEFAULT 0;

    -- Cursor to fetch distinct tenant database IDs
    DECLARE tenant_cursor CURSOR FOR
        SELECT DISTINCT(table_schema)
        FROM information_schema.TABLES
        WHERE table_schema LIKE 'tenant%';

    -- Continue handler for cursor not found
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET done = 1;

    OPEN tenant_cursor;

    read_loop: LOOP
        FETCH tenant_cursor INTO tenant_db_id;

        IF done = 1 THEN
            LEAVE read_loop;
        END IF;

        IF queryString != '' THEN
            SET queryString = CONCAT(queryString, ' UNION ALL ');
        END IF;

        -- Append the SELECT query for each tenant's properties with image URLs as JSON
        SET queryString = CONCAT(
            queryString,
            ' SELECT
                p.id,
                p.title,
                p.published as is_live,
                JSON_ARRAYAGG(i.url) AS images
            FROM `', tenant_db_id, '`.properties AS p
            LEFT JOIN `', tenant_db_id, '`.imageables AS ia ON p.id = ia.imageable_id AND ia.imageable_type="App\\Property"
            LEFT JOIN `', tenant_db_id, '`.images AS i ON ia.image_id = i.id AND i.deleted_at IS NULL
        
            WHERE p.deleted_at IS NULL
            GROUP BY p.id'
        );
    END LOOP;

    CLOSE tenant_cursor;

    SET @qStr = CONCAT('CREATE OR REPLACE VIEW allPropertiesWithImagesView AS ', queryString);

    PREPARE stmt FROM @qStr;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @qStr = NULL;

END

执行该存储过程后，查询视图时images列显示[null]，但直接在单个租户数据库中执行对应的SELECT查询却能得到正确的图片数据，请问我哪里出错了？

问题原因及修复方案 #

1. 核心问题：imageable_type的字符串匹配错误 #

存储过程拼接SQL时，imageable_type的转义逻辑出错，导致关联imageables表时没有匹配到数据，最终i.url全为NULL，JSON_ARRAYAGG(NULL)生成[null]：

• 原代码中用"App\\Property"拼接，最终生成的SQL会变成ia.imageable_type="App\Property"，但MySQL中字符串应该用单引号包裹，同时反斜杠在字符串拼接时需要双重转义才能正确传递到最终SQL中。

2. 修复步骤 #

修改存储过程中LEFT JOIN imageables的条件部分，将ia.imageable_type="App\\Property"替换为正确的单引号转义写法：

' LEFT JOIN `', tenant_db_id, '`.imageables AS ia ON p.id = ia.imageable_id AND ia.imageable_type=''App\\Property'''

这里用两个单引号表示SQL中的一个单引号，同时\\确保最终SQL里的App\Property能正确匹配数据库中存储的多态类型值。

3. 优化：处理无图片时的空数组问题 #

如果希望无图片的房产显示空数组[]而非[null]，可以修改JSON_ARRAYAGG的逻辑：

JSON_ARRAYAGG(CASE WHEN i.url IS NOT NULL THEN i.url END) AS images

验证方法 #

执行存储过程后，查看生成的视图定义，确认imageable_type的条件是否正确：

SHOW CREATE VIEW allPropertiesWithImagesView;

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