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

 imageable_type="App\\\\property"