By removing the .includes(:translations).references(:translations) from the spree_base_scopes method in SpreeGlobalize::Translatable the query succeeds. You can see this in action on our fork. https://github.com/ECHOInternational/spree_globalize
The generated SQL becomes much simpler and works properly:
SELECT DISTINCT spree_products.*, MIN(spree_products_taxons.position) AS min_position
FROM "spree_products"
INNER JOIN "spree_products_stores" ON "spree_products"."id" = "spree_products_stores"."product_id"
INNER JOIN "spree_variants" ON "spree_variants"."deleted_at" IS NULL AND "spree_variants"."product_id" = "spree_products"."id"
INNER JOIN "spree_prices" ON "spree_prices"."deleted_at" IS NULL AND "spree_prices"."variant_id" = "spree_variants"."id"
INNER JOIN "spree_products_taxons" ON "spree_products_taxons"."product_id" = "spree_products"."id" WHERE "spree_products"."deleted_at" IS NULL AND "spree_products_stores"."store_id" = 4 AND "spree_prices"."currency" = 'USD' AND "spree_prices"."amount" IS NOT NULL AND "spree_products_taxons"."taxon_id" = 55 AND ("spree_products".deleted_at IS NULL or "spree_products".deleted_at >= '2021-08-25 19:02:05.891093') AND ("spree_products".discontinue_on IS NULL or "spree_products".discontinue_on >= '2021-08-25 19:02:05.891439') AND ("spree_products".available_on <= '2021-08-25 19:02:05.891420') AND "spree_prices"."currency" = 'USD' AND "spree_prices"."amount" IS NOT NULL AND "spree_products_taxons"."taxon_id" = 55
GROUP BY "spree_products"."id"
ORDER BY "min_position" ASC
LIMIT 12
OFFSET 0
Compare this to the generated query below that is currently created and fails:
SQL Query that fails
SELECT DISTINCT spree_products.*, MIN(spree_products_taxons.position) AS min_position,
"spree_products"."id" AS t0_r0,
"spree_product_translations"."id" AS t1_r0,
"spree_product_translations"."spree_product_id" AS t1_r1,
"spree_product_translations"."locale" AS t1_r2,
"spree_product_translations"."created_at" AS t1_r3,
"spree_product_translations"."updated_at" AS t1_r4,
"spree_product_translations"."name" AS t1_r5,
"spree_product_translations"."description" AS t1_r6,
"spree_product_translations"."meta_description" AS t1_r7,
"spree_product_translations"."meta_keywords" AS t1_r8,
"spree_product_translations"."slug" AS t1_r9,
"spree_product_translations"."deleted_at" AS t1_r10,
"spree_product_translations"."meta_title" AS t1_r11,
"spree_tax_categories"."id" AS t2_r0,
"spree_tax_categories"."name" AS t2_r1,
"spree_tax_categories"."description" AS t2_r2,
"spree_tax_categories"."is_default" AS t2_r3,
"spree_tax_categories"."deleted_at" AS t2_r4,
"spree_tax_categories"."created_at" AS t2_r5,
"spree_tax_categories"."updated_at" AS t2_r6,
"spree_tax_categories"."tax_code" AS t2_r7,
"spree_variants"."id" AS t3_r0,
"spree_variants"."sku" AS t3_r1,
"spree_variants"."weight" AS t3_r2,
"spree_variants"."height" AS t3_r3,
"spree_variants"."width" AS t3_r4,
"spree_variants"."depth" AS t3_r5,
"spree_variants"."deleted_at" AS t3_r6,
"spree_variants"."is_master" AS t3_r7,
"spree_variants"."product_id" AS t3_r8,
"spree_variants"."cost_price" AS t3_r9,
"spree_variants"."position" AS t3_r10,
"spree_variants"."cost_currency" AS t3_r11,
"spree_variants"."track_inventory" AS t3_r12,
"spree_variants"."tax_category_id" AS t3_r13,
"spree_variants"."updated_at" AS t3_r14,
"spree_variants"."discontinue_on" AS t3_r15,
"spree_variants"."created_at" AS t3_r16,
"spree_assets"."id" AS t4_r0,
"spree_assets"."viewable_type" AS t4_r1,
"spree_assets"."viewable_id" AS t4_r2,
"spree_assets"."attachment_width" AS t4_r3,
"spree_assets"."attachment_height" AS t4_r4,
"spree_assets"."attachment_file_size" AS t4_r5,
"spree_assets"."position" AS t4_r6,
"spree_assets"."attachment_content_type" AS t4_r7,
"spree_assets"."attachment_file_name" AS t4_r8,
"spree_assets"."type" AS t4_r9,
"spree_assets"."attachment_updated_at" AS t4_r10,
"spree_assets"."alt" AS t4_r11,
"spree_assets"."created_at" AS t4_r12,
"spree_assets"."updated_at" AS t4_r13,
"active_storage_attachments"."id" AS t5_r0,
"active_storage_attachments"."name" AS t5_r1,
"active_storage_attachments"."record_type" AS t5_r2,
"active_storage_attachments"."record_id" AS t5_r3,
"active_storage_attachments"."blob_id" AS t5_r4,
"active_storage_attachments"."created_at" AS t5_r5,
"active_storage_blobs"."id" AS t6_r0,
"active_storage_blobs"."key" AS t6_r1,
"active_storage_blobs"."filename" AS t6_r2,
"active_storage_blobs"."content_type" AS t6_r3,
"active_storage_blobs"."metadata" AS t6_r4,
"active_storage_blobs"."byte_size" AS t6_r5,
"active_storage_blobs"."checksum" AS t6_r6,
"active_storage_blobs"."created_at" AS t6_r7,
"active_storage_blobs"."service_name" AS t6_r8,
"spree_variants"."id" AS t7_r0,
"spree_variants"."sku" AS t7_r1,
"spree_variants"."weight" AS t7_r2,
"spree_variants"."height" AS t7_r3,
"spree_variants"."width" AS t7_r4,
"spree_variants"."depth" AS t7_r5,
"spree_variants"."deleted_at" AS t7_r6,
"spree_variants"."is_master" AS t7_r7,
"spree_variants"."product_id" AS t7_r8,
"spree_variants"."cost_price" AS t7_r9,
"spree_variants"."position" AS t7_r10,
"spree_variants"."cost_currency" AS t7_r11,
"spree_variants"."track_inventory" AS t7_r12,
"spree_variants"."tax_category_id" AS t7_r13,
"spree_variants"."updated_at" AS t7_r14,
"spree_variants"."discontinue_on" AS t7_r15,
"spree_variants"."created_at" AS t7_r16,
"spree_prices"."id" AS t8_r0,
"spree_prices"."variant_id" AS t8_r1,
"spree_prices"."amount" AS t8_r2,
"spree_prices"."currency" AS t8_r3,
"spree_prices"."deleted_at" AS t8_r4,
"spree_prices"."created_at" AS t8_r5,
"spree_prices"."updated_at" AS t8_r6,
"spree_prices"."compare_at_amount" AS t8_r7,
"images_spree_variants"."id" AS t9_r0,
"images_spree_variants"."viewable_type" AS t9_r1,
"images_spree_variants"."viewable_id" AS t9_r2,
"images_spree_variants"."attachment_width" AS t9_r3,
"images_spree_variants"."attachment_height" AS t9_r4,
"images_spree_variants"."attachment_file_size" AS t9_r5,
"images_spree_variants"."position" AS t9_r6,
"images_spree_variants"."attachment_content_type" AS t9_r7,
"images_spree_variants"."attachment_file_name" AS t9_r8,
"images_spree_variants"."type" AS t9_r9,
"images_spree_variants"."attachment_updated_at" AS t9_r10,
"images_spree_variants"."alt" AS t9_r11,
"images_spree_variants"."created_at" AS t9_r12,
"images_spree_variants"."updated_at" AS t9_r13,
"attachment_attachments_spree_assets"."id" AS t10_r0,
"attachment_attachments_spree_assets"."name" AS t10_r1,
"attachment_attachments_spree_assets"."record_type" AS t10_r2,
"attachment_attachments_spree_assets"."record_id" AS t10_r3,
"attachment_attachments_spree_assets"."blob_id" AS t10_r4,
"attachment_attachments_spree_assets"."created_at" AS t10_r5,
"blobs_active_storage_attachments"."id" AS t11_r0,
"blobs_active_storage_attachments"."key" AS t11_r1,
"blobs_active_storage_attachments"."filename" AS t11_r2,
"blobs_active_storage_attachments"."content_type" AS t11_r3,
"blobs_active_storage_attachments"."metadata" AS t11_r4,
"blobs_active_storage_attachments"."byte_size" AS t11_r5,
"blobs_active_storage_attachments"."checksum" AS t11_r6,
"blobs_active_storage_attachments"."created_at" AS t11_r7,
"blobs_active_storage_attachments"."service_name" AS t11_r8
FROM "spree_products"
INNER JOIN "spree_products_stores" ON "spree_products"."id" = "spree_products_stores"."product_id"
INNER JOIN "spree_variants" ON "spree_variants"."deleted_at" IS NULL AND "spree_variants"."product_id" = "spree_products"."id"
INNER JOIN "spree_prices" ON "spree_prices"."deleted_at" IS NULL AND "spree_prices"."variant_id" = "spree_variants"."id"
INNER JOIN "spree_products_taxons" ON "spree_products_taxons"."product_id" = "spree_products"."id"
LEFT OUTER JOIN "spree_assets" ON "spree_assets"."viewable_type" = 'Spree::Variant' AND "spree_assets"."viewable_id" = "spree_variants"."id"
LEFT OUTER JOIN "active_storage_attachments" ON "active_storage_attachments"."record_type" = 'Spree::Asset' AND "active_storage_attachments"."name" = 'attachment' AND "active_storage_attachments"."record_id" = "spree_assets"."id"
LEFT OUTER JOIN "active_storage_blobs" ON "active_storage_blobs"."id" = "active_storage_attachments"."blob_id"
LEFT OUTER JOIN "spree_assets" "images_spree_variants" ON "images_spree_variants"."viewable_type" = 'Spree::Variant' AND "images_spree_variants"."viewable_id" = "spree_variants"."id"
LEFT OUTER JOIN "active_storage_attachments" "attachment_attachments_spree_assets" ON "attachment_attachments_spree_assets"."record_type" = 'Spree::Asset' AND "attachment_attachments_spree_assets"."name" = 'attachment' AND "attachment_attachments_spree_assets"."record_id" = "images_spree_variants"."id"
LEFT OUTER JOIN "active_storage_blobs" "blobs_active_storage_attachments" ON "blobs_active_storage_attachments"."id" = "attachment_attachments_spree_assets"."blob_id"
LEFT OUTER JOIN "spree_product_translations" ON "spree_product_translations"."spree_product_id" = "spree_products"."id"
LEFT OUTER JOIN "spree_tax_categories" ON "spree_tax_categories"."deleted_at" IS NULL AND "spree_tax_categories"."id" = "spree_products"."tax_category_id"
WHERE "spree_products"."deleted_at" IS NULL
AND "spree_products_stores"."store_id" = 4
AND "spree_prices"."currency" = 'USD'
AND "spree_prices"."amount" IS NOT NULL
AND "spree_products_taxons"."taxon_id" = 37
AND ("spree_products".deleted_at IS NULL or "spree_products".deleted_at >= '2021-08-24 22:55:19.312492')
AND ("spree_products".discontinue_on IS NULL or "spree_products".discontinue_on >= '2021-08-24 22:55:19.312792')
AND ("spree_products".available_on <= '2021-08-24 22:55:19.312777')
AND "spree_prices"."currency" = 'USD'
AND "spree_prices"."amount" IS NOT NULL
AND "spree_products_taxons"."taxon_id" = 37
GROUP BY "spree_products"."id"
ORDER BY "min_position"
ASC
LIMIT 12
OFFSET 0
Steps to Replicate:
Expected Behaviour
The page will load with a list of products that belong to that taxon
Actual Behaviour
PG::GroupingError
PG::GroupingError: ERROR: column "spree_product_translations.id" must appear in the GROUP BY clause or be used in an aggregate functionEnvironment
Diagnosis
The Spree_Globalize gem preloads translations by adding to the
spree_base_scopemethod.The addition of this preloaded scope causes the generated SQL query to be invalid. The SQL query that is failing is listed below.
Workaround (Not necessarily a fix)
By removing the
.includes(:translations).references(:translations)from thespree_base_scopesmethod inSpreeGlobalize::Translatablethe query succeeds. You can see this in action on our fork. https://github.com/ECHOInternational/spree_globalizeThe generated SQL becomes much simpler and works properly:
Compare this to the generated query below that is currently created and fails:
SQL Query that fails
Stack Trace