存储过程未返回相关表格数据。

huangapple go评论60阅读模式
英文:

Stored procedure isn't returning the related table data

问题

希望有人能帮我编写一个存储过程,该过程循环遍历所有租户并获取属性以及相关图像。属性和图像表通过imageables表具有多态关系。以下是简化的表格:

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


images
--------
id
url


imageables
----------
image_id -- 引用images表
imageable_id -- 引用properties表中的id
imageable_type -- 例如 App\Property 或 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;

    -- 游标以获取不同的租户数据库ID
    DECLARE tenant_cursor CURSOR FOR
        SELECT DISTINCT(table_schema)
        FROM information_schema.TABLES
        WHERE table_schema LIKE 'tenant%';

    -- 游标未找到的继续处理程序
    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;

        -- 为每个租户的属性附加SELECT查询,其中包括图像URL的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

问题在于当我运行该过程时,属性的图像列会显示[null]。然而,在租户数据库上运行查询会给出正确的结果。有人能帮助识别问题,找出我做错了什么吗?

英文:

I hope some one can help me with a stored procedure which loops through all tenants and get properties along with images. The property and images table have a polymorphic relationship through imageables table. Here are the simplified tables;

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

Following is the stored procedure

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

The problem is that when i run the procedure, it gives me [null] in images column for the properties. However, running the query on the tenant database gives me correct results. Can some one help to identify the issue as what am i doing wrong?

答案1

得分: 0

留下答案供将来遇到相同问题的任何人使用。由于我正在检查 imageable_type 是否为 App\Property,我需要按照以下方式进行操作:

 imageable_type="App\\\\Property"

这是为什么的解释,

> 在 MySQL 中,反斜杠是转义字符,当您想要在字符串中包含一个字面上的反斜杠时,您需要使用双反斜杠 (\) 进行转义。

由于 imageable_type 是一个字符串值,我希望它被存储为 App\Property,所以我需要在查询中使用 App\\\\Property

原因如下:

第一个反斜杠 () 用于转义第二个反斜杠,结果在字符串中产生一个单独的反斜杠。
但是,在与 MySQL 一起工作时,反斜杠也是字符串中的转义字符,所以我需要再次使用两个反斜杠 (\) 来转义反斜杠。
因此,为了将值 App\Property 作为字符串存储在 MySQL 中,我需要在查询中使用 App\\Property。
查询中的四个反斜杠 (\\) 将被 MySQL 解释为一个单独的反斜杠 (\),并且存储在数据库中的结果值将为 App\Property。

英文:

Leaving an answer for any body that would come across the same problem in the future. As I am checking imageable_type to be App\Property. I needed to do as follow;

 imageable_type="App\\\\property"

Here is the explanation as why,

> In MySQL, backslashes are escape characters, and when you want to
> include a literal backslash in a string, you need to escape it by
> using double backslashes (\)

. Since imageable_type is a string value, and I wanted it to be stored as App\Property, I needed to use App\\\\Property in the query.

Here's why:

The first backslash () is used to escape the second backslash, resulting in a single backslash in the string.
However, when working with MySQL, backslashes are also escape characters in strings, so I needed to escape the backslash again by using two backslashes (\).
Therefore, to store the value App\Property as a string in MySQL, I needed to use App\\Property in the query.
The quadruple backslashes (\\) in the query will be interpreted by MySQL as a single backslash (\), and the resulting value stored in the database will be App\Property

huangapple
  • 本文由 发表于 2023年5月17日 07:03:45
  • 转载请务必保留本文链接:https://go.coder-hub.com/76267599.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定