mysql - 在 MySQL 的一个字段中创建一个包含数据透视表中所有值的 View

Question

我有一个非常大的模型，其中包含许多“多对多”关系。我使用多个表创建了一个 View 以获得更好的搜索结果，但仍然无法在一个字段中加载数据透视表中的所有值。

我有什么:

    CREATE OR REPLACE VIEW westates AS
        SELECT
            concat(estates.locale,estates.id) as code,
            cities.name as city,
            councils.name as council,
            states.name as state,
            countries.name as country,
            concat(cities.name,' (',councils.name,')') as city_council,
            estates.*

        FROM estates
            JOIN countries  ON (estates.country_id = countries.id)
            JOIN states     ON (estates.state_id = states.id)
            JOIN councils   ON (estates.city_council_id = councils.id)
            JOIN cities     ON (estates.city_id = cities.id)

我想要的(伪代码)

    CREATE OR REPLACE VIEW westates AS
        SELECT
            concat(estates.locale,estates.id) as code,
            cities.name as city,
            councils.name as council,
            states.name as state,
            countries.name as country,
            concat(cities.name,' (',councils.name,')') as city_council,
            estates.*
            (SELECT all istallations from installations) as installations //and get all values from pivot table

        FROM estates
            JOIN countries  ON (estates.country_id = countries.id)
            JOIN states     ON (estates.state_id = states.id)
            JOIN councils   ON (estates.city_council_id = councils.id)
            JOIN cities     ON (estates.city_id = cities.id)
            JOIN estates_intallations ON (estates.id = estates_installations.estate_id) // this is the pivot table

并在“installations”字段中获取任何格式的所有 ID:

安装内容可以是:

instatallations => "1 4 7 15"或 "[1,4,7,15]"等

我可以用 PHP 做到这一点，但我需要使用一个查询来避免使用数千个查询来创建此 View 。

(最近问过，但可能让人难以理解)

Answer 1

将子查询替换为

( SELECT GROUP_CONCAT(inst_num)
      FROM installations AS i
      WHERE i... = ... ) AS installations

对于 inst_num 使用任何列给你 1,4 等。
对于 i...=...，使用将“安装与其他表”联系起来的任何条件。

(你少了一个逗号。)