spree v4.4.0 - 500 internal server error - with V2 API calls from Next.js commerce

[vbujas]

steps to reproduce.

install spree 4.4.0 install spree mobility run next.js commerce storefront.

tested with pg and mysql on new installs.

there is a internal server error (500) when executing the following query which is due to the fact that permalink column is not prefixed with its table.

started GET "/api/v2/storefront/products?include=primary_variant%2Cvariants%2Cimages%2Coption_types%2Cvariants.option_values&per_page=6&image_transformation%5Bquality%5D=100&image_transformation%5Bsize%5D=1000x1000" for 127.0.0.1 at 2022-10-12 22:15:21 +0200
Started GET "/api/v2/storefront/taxons?filter%5Bparent_permalink%5D=meni" for 127.0.0.1 at 2022-10-12 22:15:21 +0200
Started GET "/api/v2/storefront/cms_pages?per_page=500&filter%5Blocale_eq%5D=en-US" for 127.0.0.1 at 2022-10-12 22:15:21 +0200
Processing by Spree::Api::V2::Storefront::TaxonsController#index as JSON
Processing by Spree::Api::V2::Storefront::CmsPagesController#index as JSON
Processing by Spree::Api::V2::Storefront::ProductsController#index as JSON
  Parameters: {"filter"=>{"parent_permalink"=>"meni"}, "taxon"=>{}}
  Parameters: {"per_page"=>"500", "filter"=>{"locale_eq"=>"en-US"}, "cms_page"=>{}}
  Parameters: {"include"=>"primary_variant,variants,images,option_types,variants.option_values", "per_page"=>"6", "image_transformation"=>{"quality"=>"100", "size"=>"1000x1000"}, "product"=>{}}
.........
pree::Taxon Load (4.6ms)  SELECT `spree_taxons`.* FROM `spree_taxons` LEFT OUTER JOIN `spree_taxon_translations` `spree_taxon_translations_en` ON `spree_taxon_translations_en`.`spree_taxon_id` = `spree_taxons`.`id` AND `spree_taxon_translations_en`.`locale` = 'en' WHERE (`spree_taxon_translations_en`.`name` IS NULL OR `spree_taxons`.`id` IS NULL OR permalink LIKE '%//' OR permalink = '/') ORDER BY `spree_taxons`.`id` ASC LIMIT 1
.........
ActiveRecord::StatementInvalid (Mysql2::Error: Column 'permalink' in where clause is ambiguous):

mysql2 (0.5.3) lib/mysql2/client.rb:131:in `_query'
mysql2 (0.5.3) lib/mysql2/client.rb:131:in `block in query'
mysql2 (0.5.3) lib/mysql2/client.rb:130:in `handle_interrupt'
mysql2 (0.5.3) lib/mysql2/client.rb:130:in `query'
activerecord (6.1.7) lib/active_record/connection_adapters/abstract_mysql_adapter.rb:206:in `block (2 levels) in execute

I also get a bunch of unpermitted parameters errors all over the rails console like those below:

Unpermitted parameters: :image_transformation, :product Unpermitted parameter: :taxon Unpermitted parameters: :image_transformation, :product

Can you help on this one ?

[mrbrdo]

Currently I am not using the API / vue / nextjs functionality, only the legacy frontend. I'm a little short on time right now to check this myself, although I will be happy to review and merge a pull request with a fix.

I think the problem is coming from the get_taxons method here: https://github.com/spree/spree/blob/main/core/app/models/concerns/spree/product_scopes.rb#L342

It's the only place I can find that generates this query. That is in main branch, not necessarily the same as in 4.4. In the 4.3. version the query was written differently (and I think would work in this case, I'm not sure about the new Arel version, might work or might not):

Taxon.find_by(name: t) ||
              Taxon.where("#{taxons}.permalink LIKE ? OR #{taxons}.permalink = ?", "%/#{t}/", "#{t}/").first

So fixing this would probably require a decorator for Spree::Product which overrides the get_taxons method with a fixed version.

[vbujas]

Thanks a lot for the answer!

I came to the same conclusion the other day.

In my version of spree (4.4.0 , downloaded with spree starter kit.) that query is written like this:

Taxon.where(name: t).or(Taxon.where(id: t)).or(Taxon.where("permalink LIKE ? OR permalink = ?", "%/#{t}/", "#{t}/")).first

my workarrond was to rewrite the query like this:

Taxon.where(name: t).or(Taxon.where(id: t)).or(Taxon.where("spree_taxons.permalink LIKE ? OR spree_taxons.permalink = ?", "%/#{t}/", "#{t}/")).first

and it worked, but It is certainly better to write it like you suggested above.

I will test it and let you know.

[mrbrdo]

@vbujas cool, let me know what you find. I suggest to try the version from main branch first: https://github.com/spree/spree/blob/main/core/app/models/concerns/spree/product_scopes.rb#L342 I think it might work, then you only need to backport that into your 4.4.x.