Complex query (migrate from gorm raw to the bun)

[tinkerbaj]

I spend whole day to try fix this, I try with chatgpt, deepseek no luck. I really like bun I migrated easy the small queries but this one is the most complex.

WITH matched_products AS (
    SELECT 
        p.id AS product_id,
        p.name AS product_name,
        p.price,
        p.category_id,
        p.city_id AS product_city_id,  -- Explicitly named
        -- Other product fields you need...
        c.name AS category_name,
        c.slug AS category_slug,
        c.parent_id,
        c.image AS category_image,
        c.featured AS category_featured,
        ct.name AS city_name,
        ct.id AS city_table_id,  -- Explicitly named
        s.id AS state_id,
        s.name AS state_name
    FROM products p
    JOIN categories c ON p.category_id = c.id
    LEFT JOIN cities ct ON p.city_id = ct.id
    LEFT JOIN states s ON ct.state_id = s.id
    WHERE p.name ILIKE '%us%'
),

-- Category counts
category_counts AS (
    SELECT 
        mp.category_id,
        COUNT(*) AS product_count
    FROM matched_products mp
    GROUP BY mp.category_id
),

-- Parent category counts
parent_category_counts AS (
    SELECT 
        c.parent_id,
        SUM(cc.product_count) AS total_product_count
    FROM categories c
    JOIN category_counts cc ON c.id = cc.category_id
    WHERE c.parent_id IS NOT NULL
    GROUP BY c.parent_id
),

-- City counts (using original products table to avoid ambiguity)
city_product_counts AS (
    SELECT 
        p.city_id,
        COUNT(*) AS city_count
    FROM products p
    WHERE p.name ILIKE '%us%' AND p.city_id IS NOT NULL
    GROUP BY p.city_id
),

-- State counts (using original tables)
state_product_counts AS (
    SELECT 
        c.state_id,
        COUNT(*) AS state_count
    FROM products p
    JOIN cities c ON p.city_id = c.id
    WHERE p.name ILIKE '%us%'
    GROUP BY c.state_id
)

SELECT 
    mp.*,  -- All columns from matched_products
    
    -- Category information
    CASE 
        WHEN mp.parent_id IS NULL THEN NULL
        ELSE mp.category_id
    END AS subcategory_id,
    CASE 
        WHEN mp.parent_id IS NULL THEN NULL
        ELSE mp.category_name
    END AS subcategory_name,
    CASE 
        WHEN mp.parent_id IS NULL THEN NULL
        ELSE cc.product_count
    END AS subcategory_count,
    CASE 
        WHEN mp.parent_id IS NULL THEN mp.category_id
        ELSE mp.parent_id
    END AS parent_category_id,
    CASE 
        WHEN mp.parent_id IS NULL THEN mp.category_name
        ELSE parent.name
    END AS parent_category_name,
    CASE 
        WHEN mp.parent_id IS NULL THEN cc.product_count
        ELSE pcc.total_product_count
    END AS parent_category_count,
    
    -- City information
    cp.city_count,
    
    -- State information
    sp.state_count

FROM matched_products mp
-- Category joins
LEFT JOIN categories parent ON mp.parent_id = parent.id
LEFT JOIN category_counts cc ON mp.category_id = cc.category_id
LEFT JOIN parent_category_counts pcc ON 
    (mp.parent_id IS NOT NULL AND mp.parent_id = pcc.parent_id) OR
    (mp.parent_id IS NULL AND mp.category_id = pcc.parent_id)
    
-- City join (using original city_id from products)
LEFT JOIN city_product_counts cp ON mp.product_city_id = cp.city_id

-- State join
LEFT JOIN state_product_counts sp ON mp.state_id = sp.state_id

ORDER BY 
    CASE WHEN mp.parent_id IS NULL THEN mp.category_name ELSE parent.name END,
    mp.city_name,
    mp.state_name,
    mp.product_name;
    ```
    
    If someone can at least guide how to combine all this left joins and this cte statements, btw I'm really bad with sql and really noob with Bun :) 

[j2gg0s]

Where exactly is the issue? This is indeed a complex SQL query, and it’s not recommended to use it in production code.