This is my database schema:

I am trying to query products to display on my website's front page. Although, I need every product row to come with information about it's availability. Also, I need to ability to order these rows so that products that are not in stock come last.

This is what I am looking to recieve as json in my front end website, if the product is available, list the sizes in the "inventory" property, if not, inventory should be null:

[
    {
        id: 1,
        name: "product 1",
        sku: "dslfgsd",
        brand_slug: "some-brand",
        inventory: [
            { "price": 400, "size": "41½" },
            { "price": 500, "size": "42" }
        ]
    },
    {
        id: 2,
        name: "product 2",
        sku: "djsfkj",
        brand_slug: "some-other-brand",
        inventory: null
    }
]

My first attempt was to run two different queries then matching the available sizes to the products. So, I run the first query to get products, get the product ids, then search for them in the junction table. Then I match them to the products, sort them so that the products that are available rank first. I don't like the following attempt because it uses two queries and won't work well with pagination since I am only ordering the 12 products received from the first query.

const productsQuery = `
    SELECT products.id,
        products.name,
        products.sku,
        brands.slug as brand_slug
    FROM products
    INNER JOIN brands
        ON products.brand = brands.id
        AND products.public IS true
    LIMIT 12
`
 
const productsResult = await database.query(productsQuery)

let products = productsResult.rows.map(p => ({ ...p, inventory: [] }))

if (products.length === 0) return { props: { initialProducts: products } }

const productIds = products.map(r => r.id)

const inventoryQuery = `
    SELECT products_sizes_suppliers.product_id,
        products_sizes_suppliers.price,
        sizes.display_text as size
    FROM products_sizes_suppliers
    INNER JOIN sizes
        ON products_sizes_suppliers.size_id = sizes.id
        AND products_sizes_suppliers.product_id IN (${productIds.join(",")})
        AND cost IS NOT NULL
        AND price IS NOT NULL
    ORDER BY sizes.numerical_value
`

const inventoryResult = await database.query(inventoryQuery)

for (let inventory of inventoryResult.rows) {
    for (let index = 0; index < products.length; index  ) {
            if (products[index].id === inventory.product_id) products[index].inventory = [...products[index].inventory, inventory]
    }
}

products = products
    .sort((a, b) => {
        if (a.inventory.length === 0 && b.inventory.length > 0) return 1
        if (a.inventory.length > 0 && b.inventory.length === 0) return -1
        if ((a.inventory.length === 0 && b.inventory.length === 0)) return 0
    })
    .map(product => {
        const newProduct = { ...product }
        if (newProduct.inventory.length === 0) newProduct.inventory = null
        return newProduct
    })

My second attempt was to do everything in one query. I used array_to_json, array_agg and json_build_object. But I am unable to order this query.

SELECT products_a.id,
    products_a.name,
    products_a.sku,
    brands_a.slug as brand_slug,
    (
        SELECT array_to_json(array_agg(json_build_object('price', products_sizes_suppliers.price, 'size', sizes.display_text))) AS inventory
        FROM products_sizes_suppliers
        INNER JOIN sizes
        ON products_sizes_suppliers.size_id = sizes.id
            AND products_sizes_suppliers.product_id = products_a.id
            AND products_sizes_suppliers.cost IS NOT NULL
            AND products_sizes_suppliers.price IS NOT NULL
    )
FROM products as products_a
INNER JOIN brands as brands_a
    ON brands_a.id = products_a.brand
    AND products_a.public IS true

I am unable to order this query using ORDER BY inventory DESC NULLS LAST which results in the error: "could not identify an ordering operator for type json". Other attempts I have made result in the error: "column "inventory" does not exist".

I feel as though I am missing a core concept here, and that there is a proper way of doing this as I know SQL is a very powerful language.

Thanks in advance.

CodePudding user response：

To determine the order in which rows are aggreated, use

jsonb_agg(element ORDER BY expression)

CodePudding user response：

@Lauren Albe Pointed me in the right direction, his suggestion was to use jsonb_agg(element ORDER BY expression). I had to use the ORDER BY clause outside of the function and I had to find something to order it with do I just chose the price and the date the product was added. Also this article came in very handy.

EDIT: The first query didn't really solve the problem because it returned duplicates. Here is the one that worked:

SELECT products.id,
    products.activity,
    products.colors,
    products.gender,
    products.images,
    products.name,
    products.sku,
    brands.slug as brand_slug,
    jsonb_agg(to_jsonb(variants) - 'product_id' - 'numerical_value' ORDER BY numerical_value) AS inventory
FROM products
INNER JOIN brands
    ON products.brand = brands.id
LEFT JOIN (
    SELECT products_sizes_suppliers.product_id,
        products_sizes_suppliers.price,
        sizes.display_text as size,
        sizes.numerical_value
    FROM products_sizes_suppliers
    INNER JOIN sizes
        ON products_sizes_suppliers.size_id = sizes.id
) AS variants
    ON products.id = variants.product_id
GROUP BY products.id, brands.slug, variants.product_id
HAVING products.public IS true
ORDER BY variants.product_id, products.added_on DESC NULLS LAST
LIMIT 12

The old query:

SELECT products.id,
    products.sku,
    jsonb_agg(
        to_jsonb(something) - 'product_id'
    ) AS inventory
FROM products
LEFT JOIN (
    SELECT products_sizes_suppliers.product_id,
        products_sizes_suppliers.price,
        sizes.display_text
    FROM products_sizes_suppliers
    INNER JOIN sizes
        ON products_sizes_suppliers.size_id = sizes.id
) AS something ON products.id = something.product_id
GROUP BY products.id, something.price
ORDER BY price, products.added_on DESC NULLS LAST