我应该分开普通的SQL查询或CTE并将其放在变量中吗？

Question

I have some sql queries that have some common parts in them. The CTE used to get the data about houses look similar in both queries.

const GetUserListSQL = `
  WITH "HouseData" AS (
    SELECT
      "UserId",
      json_object_agg(
        "Id",
        (SELECT x FROM (SELECT
          "Price",
          "Area",
          "Address"
        ) x)
      ) AS "HouseMap"
    FROM "Houses"
    GROUP BY "UserId"
  )
  SELECT
    "Id",
    "Name",
  FROM "Users"
  LEFT JOIN "HouseData" ON "Users"."Id" = "HouseData"."UserId"
`


const GetUserSQL = `
  WITH "HouseData" AS (
    SELECT
      json_object_agg(
        "Id",
        (SELECT x FROM (SELECT
          "Price",
          "Area",
          "Address"
        ) x)
      ) AS "HouseMap"
    FROM "Houses"
    WHERE "UserId" = $1
  )
  SELECT
    "Id",
    "Name",
  FROM "Users"
  LEFT JOIN "HouseData" ON TRUE
  WHERE "Users"."Id" = $1
`

Should I make the CTE to get house data or user fields a separate const? Will it increase reusability (like you only need to add fields in one place)? Will it reduce readability (it looks kind of weird somehow). Someone suggested that I wrote those SQL like below:

const HouseDataCTE = `
  SELECT
    "UserId",
    json_object_agg(
      "Id",
      (SELECT x FROM (SELECT
        "Price" 
        "Area",
        "Address"
      ) x)
    ) AS "HouseMap"
  FROM "Houses"
`

const UserCommonSQL = `
  SELECT
    "Id",
    "Name"
  FROM "Users"
`

const GetUserListSQL = `
  WITH "HouseData" AS (
    ` + HouseDataCTE + `
    GROUP BY "UserId"
  )
  ` + UserCommonSQL + `
  LEFT JOIN "HouseData" ON "Users"."Id" = "HouseData"."UserId"
`

const GetUserSQL = `
  WITH "HouseData" AS (
    ` + HouseDataCTE + `
    WHERE "UserId" = $1
  )
  ` + UserCommonSQL + `
  LEFT JOIN "HouseData" ON TRUE
  WHERE "Users"."Id" = $1
`

If I write it like that I feel like I have to jump back and forth to inspect the SQL (the queries are not placed close together), which is kind of annoying in my opinion. Are there any convention or best practices for these kinds of situation ?

EDIT: The sql queries above are shorter version of an actual long SQL queries I'm using. The longer version has 4-5 CTEs from different tables and the common fields for "Users" are about 20 columns. What should be done in that situation then?

Answer 1

in my experience the CTEs tended to get jumbled in each SP. Is it possible to leverage a view on the CTE instead n your use cases? That would clean up your documentation and code maintenance.