Not able to use product filters with translated properties

[bricesanchez]

Previously posted on spree/spree#6837:

I'm using Spree and Spree_i18n on edge master branch.

I'm not able to use product filters with translated properties, this is my current working progress to fix the issue :

The filter always returns 0 products

module Spree
  module Core
      Spree::Product.add_search_scope :age_category_any do |*opts|
        byebug
        conds = opts.map {|o| ProductFilters.age_category_filter[:conds][o]}.reject { |c| c.nil? }
        scope = conds.shift
        conds.each do |new_scope|
          scope = scope.or(new_scope)
        end

        Spree::Product.with_translations.with_property(144).where(scope)  
      end

      def ProductFilters.age_category_filter
        age_category_property = Spree::Property.with_translations(I18n.locale).find_by(name: 'age_category')
        # age_category_property.id = 144
        age_categories = age_category_property ? Spree::ProductProperty.with_translations(I18n.locale).where(property_id: age_category_property.id).map(&:value).uniq.map(&:to_s) : []
        pp = Spree::ProductProperty.arel_table
        conds = Hash[*age_categories.map { |b| [b, pp[:value].eq(b)] }.flatten]
        {
          name:   age_category_property.presentation,
          scope:  :age_category_any,
          conds:  conds,
          labels: (age_categories.sort).map { |k| [k, k] }
        }
      end
  end
end

(byebug) Spree::Product.with_translations.with_property(144).count
  CACHE (0.0ms)  SELECT DISTINCT locale FROM "spree_product_translations"  ORDER BY "spree_product_translations"."locale" ASC
   (9.0ms)  SELECT COUNT(DISTINCT "spree_products"."id") FROM "spree_products" INNER JOIN "spree_variants" ON "spree_variants"."product_id" = "spree_products"."id" AND "spree_variants"."is_master" = $1 AND "spree_variants"."deleted_at" IS NULL INNER JOIN "spree_prices" ON "spree_prices"."variant_id" = "spree_variants"."id" AND "spree_prices"."deleted_at" IS NULL INNER JOIN "spree_product_translations" ON "spree_product_translations"."spree_product_id" = "spree_products"."id" INNER JOIN "spree_product_properties" ON "spree_product_properties"."product_id" = "spree_products"."id" INNER JOIN "spree_properties" ON "spree_properties"."id" = "spree_product_properties"."property_id" LEFT OUTER JOIN "spree_products_taxons" ON "spree_products_taxons"."product_id" = "spree_products"."id" WHERE "spree_products"."deleted_at" IS NULL AND ("spree_products".discontinue_on IS NULL or "spree_products".discontinue_on >= '2015-10-16 17:40:14.577192') AND ("spree_products".discontinue_on IS NULL or "spree_products".discontinue_on >= '2015-10-16 17:40:14.577582') AND ("spree_products".available_on <= '2015-10-16 17:40:14.577806') AND "spree_products_taxons"."taxon_id" = 28 AND "spree_product_translations"."locale" IN ('en', 'fr') AND "spree_properties"."id" = 144  [["is_master", "t"]]
81

(byebug) Spree::Product.with_translations.with_property(144).where(scope).count
  CACHE (0.0ms)  SELECT DISTINCT locale FROM "spree_product_translations"  ORDER BY "spree_product_translations"."locale" ASC
  CACHE (0.0ms)  SELECT COUNT(DISTINCT "spree_products"."id") FROM "spree_products" INNER JOIN "spree_variants" ON "spree_variants"."product_id" = "spree_products"."id" AND "spree_variants"."is_master" = $1 AND "spree_variants"."deleted_at" IS NULL INNER JOIN "spree_prices" ON "spree_prices"."variant_id" = "spree_variants"."id" AND "spree_prices"."deleted_at" IS NULL INNER JOIN "spree_product_translations" ON "spree_product_translations"."spree_product_id" = "spree_products"."id" INNER JOIN "spree_product_properties" ON "spree_product_properties"."product_id" = "spree_products"."id" INNER JOIN "spree_properties" ON "spree_properties"."id" = "spree_product_properties"."property_id" LEFT OUTER JOIN "spree_products_taxons" ON "spree_products_taxons"."product_id" = "spree_products"."id" WHERE "spree_products"."deleted_at" IS NULL AND ("spree_products".discontinue_on IS NULL or "spree_products".discontinue_on >= '2015-10-16 17:40:14.577192') AND ("spree_products".discontinue_on IS NULL or "spree_products".discontinue_on >= '2015-10-16 17:40:14.577582') AND ("spree_products".available_on <= '2015-10-16 17:40:14.577806') AND "spree_products_taxons"."taxon_id" = 28 AND "spree_product_translations"."locale" IN ('en', 'fr') AND "spree_properties"."id" = 144 AND "spree_product_properties"."value" = '18 mois à 12 ans'  [["is_master", true]]
0

I think my arel_table method doesn't support translated product_properties.

Is someone could help me ?

BTW, thanks for your awesome job on Spree :)

[bricesanchez]

Anyone could help me ?

[bricesanchez]

Close in favor of #19

[mvz]

@bricesanchez which ticket is replacing this? You've linked to this very same ticket instead of its replacement it seems?

[bricesanchez]

@mvz solidusio-contrib/solidus_globalize#3

But Spree is now maintained again so we can re-open this issue

[suung]

I don't know if I am going into the right direction, probably not, but this here seems to work:

.2.2 :128 > Spree::Product.joins({:properties => :translations}, :translations, {:product_properties => :translations}).where("spree_product_property_translations.value ILIKE '%13.0%'").uniq.count
   (4.4ms)  SELECT DISTINCT COUNT(DISTINCT "spree_products"."id") FROM "spree_products" INNER JOIN "spree_product_properties" ON "spree_product_properties"."product_id" = "spree_products"."id" INNER JOIN "spree_properties" ON "spree_properties"."id" = "spree_product_properties"."property_id" INNER JOIN "spree_property_translations" ON "spree_property_translations"."spree_property_id" = "spree_properties"."id" INNER JOIN "spree_product_translations" ON "spree_product_translations"."spree_product_id" = "spree_products"."id" INNER JOIN "spree_product_properties" "product_properties_spree_products" ON "product_properties_spree_products"."product_id" = "spree_products"."id" INNER JOIN "spree_product_property_translations" ON "spree_product_property_translations"."spree_product_property_id" = "product_properties_spree_products"."id" WHERE "spree_products"."deleted_at" IS NULL AND (spree_product_property_translations.value ILIKE '%13.0%')
 => 3 
2.2.2 :129 > Spree::Product.joins({:properties => :translations}, :translations, {:product_properties => :translations}).where("spree_product_property_translations.value ILIKE '%13.0%'").uniq.first.product_properties.first.value
  Spree::Product Load (7.2ms)  SELECT  DISTINCT "spree_products".* FROM "spree_products" INNER JOIN "spree_product_properties" ON "spree_product_properties"."product_id" = "spree_products"."id" INNER JOIN "spree_properties" ON "spree_properties"."id" = "spree_product_properties"."property_id" INNER JOIN "spree_property_translations" ON "spree_property_translations"."spree_property_id" = "spree_properties"."id" INNER JOIN "spree_product_translations" ON "spree_product_translations"."spree_product_id" = "spree_products"."id" INNER JOIN "spree_product_properties" "product_properties_spree_products" ON "product_properties_spree_products"."product_id" = "spree_products"."id" INNER JOIN "spree_product_property_translations" ON "spree_product_property_translations"."spree_product_property_id" = "product_properties_spree_products"."id" WHERE "spree_products"."deleted_at" IS NULL AND (spree_product_property_translations.value ILIKE '%13.0%')  ORDER BY "spree_products"."id" ASC LIMIT 1
  Spree::ProductProperty Load (0.3ms)  SELECT  "spree_product_properties".* FROM "spree_product_properties" WHERE "spree_product_properties"."product_id" = $1  ORDER BY "spree_product_properties"."position" ASC LIMIT 1  [["product_id", 1]]
  Spree::ProductProperty::Translation Load (0.2ms)  SELECT "spree_product_property_translations".* FROM "spree_product_property_translations" WHERE "spree_product_property_translations"."spree_product_property_id" = $1  [["spree_product_property_id", 1]]
 => 13.0 
2.2.2 :130 > Spree::Product.joins({:properties => :translations}, :translations, {:product_properties => :translations}).where("spree_product_property_translations.value ILIKE '%13.0%'").uniq.second.product_properties.first.value
  Spree::Product Load (5.8ms)  SELECT  DISTINCT "spree_products".* FROM "spree_products" INNER JOIN "spree_product_properties" ON "spree_product_properties"."product_id" = "spree_products"."id" INNER JOIN "spree_properties" ON "spree_properties"."id" = "spree_product_properties"."property_id" INNER JOIN "spree_property_translations" ON "spree_property_translations"."spree_property_id" = "spree_properties"."id" INNER JOIN "spree_product_translations" ON "spree_product_translations"."spree_product_id" = "spree_products"."id" INNER JOIN "spree_product_properties" "product_properties_spree_products" ON "product_properties_spree_products"."product_id" = "spree_products"."id" INNER JOIN "spree_product_property_translations" ON "spree_product_property_translations"."spree_product_property_id" = "product_properties_spree_products"."id" WHERE "spree_products"."deleted_at" IS NULL AND (spree_product_property_translations.value ILIKE '%13.0%')  ORDER BY "spree_products"."id" ASC LIMIT 1 OFFSET 1
  Spree::ProductProperty Load (0.3ms)  SELECT  "spree_product_properties".* FROM "spree_product_properties" WHERE "spree_product_properties"."product_id" = $1  ORDER BY "spree_product_properties"."position" ASC LIMIT 1  [["product_id", 2]]
  Spree::ProductProperty::Translation Load (0.3ms)  SELECT "spree_product_property_translations".* FROM "spree_product_property_translations" WHERE "spree_product_property_translations"."spree_product_property_id" = $1  [["spree_product_property_id", 18]]
 => 13.0 
2.2.2 :131 > 

the only problem is, i don't know, why i need the like on the value of the properties.

here is a query that matches on name AND value

 Spree::Property::Translation(id: integer, spree_property_id: integer, locale: string, created_at: datetime, updated_at: datetime, name: string, presentation: string) 
2.2.2 :133 > Spree::Product.joins({:properties => :translations}, :translations, {:product_properties => :translations}).where("spree_property_translations.name" => "alcohol").where("spree_product_property_translations.value ILIKE '%13.0%'").uniq.second.product_properties.first.value
  Spree::Product Load (5.1ms)  SELECT  DISTINCT "spree_products".* FROM "spree_products" INNER JOIN "spree_product_properties" ON "spree_product_properties"."product_id" = "spree_products"."id" INNER JOIN "spree_properties" ON "spree_properties"."id" = "spree_product_properties"."property_id" INNER JOIN "spree_property_translations" ON "spree_property_translations"."spree_property_id" = "spree_properties"."id" INNER JOIN "spree_product_translations" ON "spree_product_translations"."spree_product_id" = "spree_products"."id" INNER JOIN "spree_product_properties" "product_properties_spree_products" ON "product_properties_spree_products"."product_id" = "spree_products"."id" INNER JOIN "spree_product_property_translations" ON "spree_product_property_translations"."spree_product_property_id" = "product_properties_spree_products"."id" WHERE "spree_products"."deleted_at" IS NULL AND "spree_property_translations"."name" = 'alcohol' AND (spree_product_property_translations.value ILIKE '%13.0%')  ORDER BY "spree_products"."id" ASC LIMIT 1 OFFSET 1
  Spree::ProductProperty Load (0.2ms)  SELECT  "spree_product_properties".* FROM "spree_product_properties" WHERE "spree_product_properties"."product_id" = $1  ORDER BY "spree_product_properties"."position" ASC LIMIT 1  [["product_id", 2]]
  Spree::ProductProperty::Translation Load (0.3ms)  SELECT "spree_product_property_translations".* FROM "spree_product_property_translations" WHERE "spree_product_property_translations"."spree_product_property_id" = $1  [["spree_product_property_id", 18]]
 => 13.0