Combining two results
I want to combine the results from my postgres query, by product properties. It is currently giving me the following results:
name id value sku item_count
Item # 1 3 Item IT-EM1 3
Item # 1 2 006058465456 IT-EM1 3
Item # 2 3 Item IT-EM2 1
Item # 2 2 055045004505 IT-EM2 1
I would like it to return the following:
name id#1 value#1 id#2 value#2 sku item_count
Item # 1 3 Item 2 006058465456 IT-EM1 3
Item # 2 3 Item 2 055045004505 IT-EM2 1
Product id (2 - GTIN and 3 - Brand), value is the value of this product property. My request is below:
SELECT
p.name,
l.property_id AS id,
l.value AS value,
v.sku,
s.count_on_hand AS item_count,
FROM
spree_variants v INNER JOIN
spree_products p ON v.product_id = p.id LEFT OUTER JOIN
spree_stock_items s ON v.id = s.variant_id INNER JOIN
spree_product_properties l ON l.product_id = p.id
WHERE
s.count_on_hand > 0
Any ideas?
+3
source to share
1 answer
I answered myself. Maybe not the most elegant soltuion, but:
SELECT
p.name,
l.property_id AS id,
l.value AS value,
li.property_id AS id_two,
li.value AS value_two,
v.sku,
s.count_on_hand AS item_count,
FROM
spree_variants v INNER JOIN
spree_products p ON v.product_id = p.id LEFT OUTER JOIN
spree_stock_items s ON v.id = s.variant_id INNER JOIN
spree_product_properties l ON l.product_id = p.id INNER JOIN
spree_product_properties li ON li.product_id = p.id
WHERE
s.count_on_hand > 0 AND
l.property_id = 2 AND
li.property_id = 3
Add the product properties column again as another variable (l and li), then define in WHERE that l.property_id = 2 and li.property_id = 3
0
source to share