PostgreSQL Queries - Spree product filtering methods and associations

I am trying to add some filtering to products, but my queries are pretty ugly and heavy..

How would you improve this query? (want to return products that don't have a translation or have but field is blank)

Spree::Product.all.map { |p| p if !p.translations.find_by(locale: 'en') || (p.translations.find_by(locale: 'en') && p.translations.find_by(locale: 'en').name.blank?) }.reject(&:blank?)
# takes about 1 second

Another query is about Taxons associated with Products:

Spree::Product.all.map { |p| p unless p.taxons.any? }.reject(&:blank?)

I know there is a way to return Records that don't have any associated has_many records, like: Spree::Product.all.includes(:taggings).where(taggings: { taggable_id: nil }). But for some reason it doesn't want to work with Taxons.

I wonder also if there is a way to improve queries on model methods. For example, I have a query that returns all the products that don't have images:

Spree::Product.all.map { |p| p unless p.all_images.any? }.reject(&:blank?)
# takes over 2 seconds

# product_decorator.rb
def all_images
  (images + variant_images).uniq
end

Thanks!

Answers


When you use the .all it will load all the records and perform those operations you set inside the blocks({}). Try to mount a query using pure SQL or even better, using the .where clause, it will mount your query and will perform better. You can use several .where, one after the other chaining the methods.

You can find more about conditions on the Rails guide


Need Your Help

SQL Server database backup: Network Service file access

sql-server backup

When trying to run the following database backup command from my code I get an "Operating system error 5(Access is denied.)" error. This is because the log on account for the SQL Server Windows Ser...

How to increase the Fields of input in Firebase Leaderboard

firebase leaderboard hitcounter

Go to http://goo.gl/I4XLKF (Link to my jsfiddle workout)