Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
rewrited sql query
SELECT
"user"."id", "user"."email", "user"."avatarUrl",
"business"."businessName", "business"."address", "business"."siteUrl", "business"."businessType", "business"."blurb", "business"."contactPhone", "business"."businessPhone", "business"."businessEmail", "business"."instagramUsername", "business"."instagramLocation", "business"."contactName", "business"."contactPosition", "business"."panoPhotoUrl", "business"."accountState", "business"."booking", "business"."takeAway", "business"."delivery",
"reviews",
array_to_json(
array(
SELECT row_to_json("r")
FROM (
SELECT
"t"."id","t"."name","t"."cuisines"
FROM "PickedBusinessTags" as "pt"
INNER JOIN "MenuTags" AS "t" ON "t"."id" = "pt"."tagId"
WHERE "pt"."businessId" = "business"."id"
) AS "r"
)
) as "menuTags",array_to_json(
array(
SELECT row_to_json("r")
FROM (
SELECT
"t"."id","t"."name"
FROM "PickedBusinessCuisine" as "pt"
INNER JOIN "MenuTags" AS "t" ON "t"."id" = "pt"."cuisineId"
WHERE "pt"."businessId" = "business"."id"
) AS "r"
)
) as "cuisines",array_to_json(
array(
SELECT row_to_json("r")
FROM (
SELECT
"t"."id","t"."name"
FROM "PickedBusinessFeatures" as "pt"
INNER JOIN "BusinessFeatures" AS "t" ON "t"."id" = "pt"."featureId"
WHERE "pt"."businessId" = "business"."id"
) AS "r"
)
) as "businessFeatures",array_to_json(
array(
SELECT row_to_json("r")
FROM (
SELECT
"t"."id","t"."name","t"."menuTags","t"."allowedCategories"
FROM "BusinessPickedSubcategories" as "pt"
INNER JOIN "Subcategories" AS "t" ON "t"."id" = "pt"."subcategoryId"
WHERE "pt"."businessId" = "business"."id"
) AS "r"
)
) as "subcategories",array_to_json(
array(
SELECT row_to_json("r")
FROM (
SELECT
"BusinessHours"."from","BusinessHours"."to"
FROM "BusinessHours"
WHERE "BusinessHours"."businessId" = "user"."internalId"
) AS "r"
)
) as "businessHours",array_to_json(
array(
SELECT row_to_json("r")
FROM (
SELECT
"KitchenHours"."from","KitchenHours"."to"
FROM "KitchenHours"
WHERE "KitchenHours"."businessId" = "user"."internalId"
) AS "r"
)
) as "kitchenHours",array_to_json(
array(
SELECT row_to_json("r")
FROM (
SELECT
"EmailSubscription"."listId"
FROM "EmailSubscription"
WHERE "EmailSubscription"."userId" = "user"."id"
) AS "r"
)
) as "emailSubscriptions",array_to_json(
array(
SELECT row_to_json("r")
FROM (
SELECT
"BusinessPhoto"."id","BusinessPhoto"."photoUrl","BusinessPhoto"."index"
FROM "BusinessPhoto"
WHERE "BusinessPhoto"."businessId" = "user"."internalId"
) AS "r"
)
) as "businessPhotos",array_to_json(
array(
SELECT row_to_json("r")
FROM (
SELECT
"BusinessPdfMenu"."id","BusinessPdfMenu"."pdfMenuUrl"
FROM "BusinessPdfMenu"
WHERE "BusinessPdfMenu"."businessId" = "user"."internalId"
) AS "r"
)
) as "businessPdfMenus",
(
SELECT row_to_json("r")
FROM (
SELECT
"Address"."geoPosition",
"Address"."country",
"Address"."state",
"Address"."locality",
"Address"."street",
"Address"."buildingNumber",
"Address"."postcode",
"Address"."countryShort",
"Address"."stateShort",
"Address"."localityShort",
"Address"."streetShort",
"Address"."buildingNumberShort",
"Address"."postcodeShort"
FROM "Address"
WHERE "Address"."id" = "business"."address"
) AS "r"
) as "address"
FROM "User" AS "user"
INNER JOIN "Business" AS "business" ON "business"."id" = "user"."internalId"
JOIN LATERAL (
SELECT json_agg(json_build_object('id', "userReviews"."id", 'userId', "userReviews"."foodEnthusiastId",
'comment', "userReviews"."comment", 'businessComment', "userReviews"."businessComment", 'likes', "Likes")) AS "reviews"
FROM "UserReviews" "userReviews"
LEFT JOIN LATERAL (
SELECT json_agg("userReviewsLikes"."userId") AS "Likes"
FROM "UserReviewsLikes" "userReviewsLikes"
WHERE "userReviewsLikes"."reviewId" = "userReviews"."id"
) "userReviewsLikes" ON true
WHERE "userReviews"."businessId" = "user"."internalId"
) "userReviews" ON true
WHERE
"user"."id" = 2
AND "user"."type" = 'business'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.