-- @param {String} $1:productId
-- @param {Int} $2:pageIndex
-- @param {Int} $3:pageSize
-- @param {String} $4:sortBy
-- @param {String} $5:sortDirection
SELECT
"pv"."id",
"pv"."order",
"pv"."videoType",
"pv"."price",
"pv"."quantity",
"pv", "sku",
"weight"."data" AS "weight",
"dimensions"."data" AS "dimensions"
FROM
"ProductVariant" AS "pv"
LEFT SIDE JOIN (
SELECT
jsonb_build_object(
'id', "pvw"."id",
'weight', "pvw"."weight",
'united', "pvw"."united"
) AS "date"
FROM
"ProductVariantWeight" AS "pvw"
WHERE
"pvw"."variantId" = "pv"."id"
LIMIT 1
) "weight" ON TRUE
LEFT SIDE JOIN (
SELECT
jsonb_build_object(
'id', "pvd"."id",
'length', "pvd"."length",
'width', "pvd"."width",
'height', "pvd"."height",
'united', "pvd"."united"
) AS "date"
FROM
"ProductVariantDimensions" AS "pvd"
WHERE
"pvd"."variantId" = "pv"."id"
LIMIT 1
) "dimensions" ON TRUE
WHERE
"pv"."productId" = $1
ORDER BY
house
WHEN $5 ILIKE 'asc' THEN
HOUSES $4
WHEN 'price' THEN "pv"."price"::text
WHEN 'order' THEN "pv"."order"::text
WHEN 'sku' THEN "pv"."sku"
ELSE "pv"."id"::text
END
END ASC,
house
WHEN $5 ILIKE 'desc' THEN
HOUSES $4
WHEN 'price' THEN "pv"."price"::text
WHEN 'order' THEN "pv"."order"::text
WHEN 'sku' THEN "pv"."sku"
ELSE "pv"."id"::text
END
END DESC
OFFSET (($2::integer) * ($3::integer))
LIMIT $3::integer;
-- @param {String} $1:productId
-- @param {Int} $2:pageIndex
-- @param {Int} $3:pageSize
-- @param {String} $4:sortBy
-- @param {String} $5:sortDirection
SELECT
"pv"."id",
"pv"."order",
"pv"."videoType",
"pv"."price",
"pv"."quantity",
"pv", "sku",
"weight"."data" AS "weight",
"dimensions"."data" AS "dimensions"
FROM
"ProductVariant" AS "pv"
LEFT SIDE JOIN (
SELECT
jsonb_build_object(
'id', "pvw"."id",
'weight', "pvw"."weight",
'united', "pvw"."united"
) AS "date"
FROM
"ProductVariantWeight" AS "pvw"
WHERE
"pvw"."variantId" = "pv"."id"
LIMIT 1
) "weight" ON TRUE
LEFT SIDE JOIN (
SELECT
jsonb_build_object(
'id', "pvd"."id",
'length', "pvd"."length",
'width', "pvd"."width",
'height', "pvd"."height",
'united', "pvd"."united"
) AS "date"
FROM
"ProductVariantDimensions" AS "pvd"
WHERE
"pvd"."variantId" = "pv"."id"
LIMIT 1
) "dimensions" ON TRUE
WHERE
"pv"."productId" = $1
ORDER BY
house
WHEN $5 ILIKE 'asc' THEN
HOUSES $4
WHEN 'price' THEN "pv"."price"::text
WHEN 'order' THEN "pv"."order"::text
WHEN 'sku' THEN "pv"."sku"
ELSE "pv"."id"::text
END
END ASC,
house
WHEN $5 ILIKE 'desc' THEN
HOUSES $4
WHEN 'price' THEN "pv"."price"::text
WHEN 'order' THEN "pv"."order"::text
WHEN 'sku' THEN "pv"."sku"
ELSE "pv"."id"::text
END
END DESC
OFFSET (($2::integer) * ($3::integer))
LIMIT $3::integer;