狂欢：分类群之间的交集 [英] Spree: intersection between taxons

问题描述

是否可以查询属于多个分类单元的产品？就像数学上的交集。

Is it possible to query for products that belongs to multiple taxons? Like a math intersection.

例如：我卖的书籍属于分类单元大学> ASU，属于分类单元课程>工程

For example: I sell books that belongs to taxon Universities > ASU, and belongs to taxon Course > Engineering

我希望能够查询属于ASU工程路径的所有书籍。
之类的东西

I would like to be able to query for all books that belongs to the ASU Engineering path. Something like

Spree::Product.in_taxon(asu_taxon).in_taxon(eng_taxon)

推荐答案

有几种方法可以做到这一点。我将使用狂欢沙盒数据，因此，如果您有兴趣，可以尝试使用结果。

There's a few ways to do this. I'm going to use the spree sandbox data, so you can try the results if you're interested.

首先，我们可以迭代所有产品，并检查其产品使用纯Ruby的分类单元：

First, we can iterate through all products, and check their taxons using pure Ruby:

Spree::Product.all.select do |product|
  taxon_names = product.taxons.map(&:name)  
  taxon_names.include?("Rails") && taxon_names.include?("Bags")  
end

缺点是它必须从数据库中检索所有产品，然后在单独的查询中检索其所有分类。如果您有相当数量的产品，那将会非常慢。

The downside to this is that it has to retrieve all of the products from the database, and then all of their taxons in separate queries. It'll be pretty slow if you have a decent number of products.

我们可以通过以下方式使这一点更好：

We can make that a little bit better with:

Spree::Product.joins(:taxons).includes(:taxons).where(spree_taxons: { name: ["Rails", "Bags"]}).all.select do |product|
  taxon_names = product.taxons.map(&:name)  
  taxon_names.include?("Rails") && taxon_names.include?("Bags")  
end

这只会得到属于到两个分类单元之一，并使用单个查询检索所有分类单元数据。它将快得多，但是我们可以做得更好...种类……

This will only get products which belong to one of the two taxons, and retrieve all of the taxon data using a single query. It'll be much faster, but we can do better...kind of...

您可以使用SQL联接以便多次按顺序联接表将其简化为一个查询，该查询将检索您想要的所有记录：

You can use SQL joins in order to join the table multiple times in order to reduce this to one query which retrieves all of the records that you want:

Spree::Product
  .joins('INNER JOIN spree_products_taxons rails_join ON (spree_products.id = rails_join.product_id)')
  .joins('INNER JOIN spree_taxons rails_taxon ON (rails_join.taxon_id = rails_taxon.id AND rails_taxon.name == "Rails")')
  .joins('INNER JOIN spree_products_taxons bags_join ON (spree_products.id = bags_join.product_id)')
  .joins('INNER JOIN spree_taxons bags_taxon ON (bags_join.taxon_id = bags_taxon.id AND bags_taxon.name == "Bags")')

这有点丑陋，但这是最快的获取方法数据，因为您仅通过一次SQL查询就可以获取所需的产品。

It's a bit ugly, but it's the fastest way to get the data, as you get only the products you want with a single SQL query.

执行此操作的方法可能比较差，但这是我所能做到的最好的方法！

There might be less bad ways to do it, but this is the best I've got!

这篇关于狂欢：分类群之间的交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！