Invalid reference to FROM-clause entry for table “mc”

I’m trying to get an item “manufacturer” with an array of contacts and each contact contains an array of translations using this query

SELECT m.* FROM manufacturers m
LEFT JOIN (
  SELECT ARRAY (
      SELECT mc.* FROM manufacturers_contacts mc
        LEFT JOIN ( SELECT ARRAY (
              SELECT ct.* FROM contact_trans ct
              WHERE ct.manufacturers_contacts_id = mc.id
              ) AS translations
          ) ct ON mc.id = ct.manufacturers_contacts_id
    WHERE mc.manufacturers_id = m.id
    ) AS contacts
  ) mc ON m.id = mc.manufacturers_id
WHERE id = $1

but I get this error

invalid reference to FROM-clause entry for table "mc"

this is the ER diagram

dummy data example:

{
    "name": "Manufacturer 1 DE",
    "address":  "Address 1 DE",
    "zipCode": "12345",
    "country": "DE",
    "hotline": "12345678",
    "serviceTime": "ser time",
    "contacts":    [{
            "name": "contact 1",
            "phoneNumber":  "phone Number 1",
            "email": "[e-mail]",
            "translations":  [{
                            "language": "de",
                            "content": "function 1 DE",
                        }, {
                            "language": "en",
                            "content": "function 1 EN",
                        }]
        }]
}

I tried to change the structure of the query to solve it but I wasn’t able to fix it.

Since I don’t have much experience with complex SQL queries, I won’t know what is the right way to do this?

Answer

nested queries inside joins can’t access the tables outside, you must use LATERAL joins for it.

as for your dummy data example: to select non-table-like result the JSONB functions as useful

here, the code that selects the structure you need (result is json):

SELECT to_jsonb(data.*) FROM (
  SELECT m.*, c.contacts
   FROM manufacturers AS m
   LEFT JOIN LATERAL (
     SELECT jsonb_agg(x.*) AS contacts FROM (
        SELECT mc.*, tr.function
          FROM manufacturers_contacts AS mc
          LEFT JOIN LATERAL (
            SELECT jsonb_agg(z.*) AS function FROM (
              SELECT ct.*
               FROM contact_trans AS ct
              WHERE ct.manufacturers_contacts_id = mc.id
            ) AS z
          ) AS tr ON TRUE
         WHERE mc.manufacturers_id = m.id
      ) AS x
   ) AS c ON TRUE
  WHERE m.id = $1
) AS data;