在rails 4中将Array＃select转换为活动记录查询 [英] Convert Array#select to active record query in rails 4

问题描述

我正在编写自定义搜索功能，并且必须通过关联进行过滤。
我有2个具有has_many_and_belongs_to的活动记录支持的模型，卡片和颜色，并且颜色具有color_name

I'm writing a custom search function, and I have to filter through an association. I have 2 active record backed models, cards and colors with a has_many_and_belongs_to, and colors have an attribute color_name

由于我的数据库已发展到大约1万张卡片，我的搜索功能异常缓慢，因为我有一个带有内部查询的select语句，因此本质上我必须进行数千个查询。

As my DB has grown to around 10k cards, my search function gets exceptionally slow because i have a select statement with a query inside it, so essentially im having to make thousands of queries.

我需要将array＃select方法转换为活动记录查询，这将产生相同的结果，并且在解决方案方面遇到了麻烦。当前（相关代码）如下：

i need to convert the array#select method into an active record query, that will yield the same results, and im having trouble coming up with a solution. the current (relevant code) is the following:

colors = [['Black'], ['Blue', 'Black']] #this is a parameter retrieved from a form submission
if color
  cards = color.flat_map do |col|
    col.inject( Card.includes(:colors) ) do |memo, color|
        temp = cards.joins(:colors).where(colors: {color_name: color})
        memo + temp.select{|card| card.colors.pluck(:color_name).sort == col.sort}
    end
  end
end

我试图模仿的功能是，只有颜色与传入数组完全匹配的卡片才会被搜索选择（比较两个数组）。由于卡可以是单红，红蓝或红蓝绿等，因此我需要能够仅搜索红蓝卡或仅单红卡

the functionality im trying to mimic is that only cards with colors exactly matching the incoming array will be selected by the search (comparing two arrays). Because cards can be mono-red, red-blue, or red-blue-green etc, i need to be able to search for only red-blue cards or only mono-red cards

我最初是沿着这条路线开始的，但是我在比较带有活动记录查询的数组时遇到了麻烦

I initially started along this route, but i'm having trouble comparing arrays with an active record query

color_objects = Color.where(color_name: col)
Card.includes(:colors).where('colors = ?', color_objects)

返回错误

ActiveRecord :: StatementInvalid：PG :: SyntaxError：错误：语法错误
位于选择第1行或附近：... id卡片。 id输入（2​​、3、4）和
（颜色=选择 co ...

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "SELECT" LINE 1: ...id" WHERE "cards"."id" IN (2, 3, 4) AND (colors = SELECT "co...

在我看来，它像失败了，因为它不想比较数组，只比较表元素。这种功能是否可能？

it looks to me like its failing because it doesnt want to compare arrays, only table elements. is this functionality even possible?

一种解决方案可能是将habtm转换为具有许多直通关系，并创建包含每个颜色排列键的连接表为了直接访问那些对象

One solution might be to convert the habtm into has many through relation and make join tables which contain keys for every permutation of colors in order to access those directly

推荐答案

我只需要搜索绿黑色卡，并且没有显示绿色，黑色或绿色的红色红色卡。

I need to be able to search for only green-black cards, and not have mono-green, or green-black-red cards show up.

我已经删除了我的previos答案，因为我没有意识到您正在寻找确切的匹配项。

I've deleted my previos answer, because i did not realized you are looking for the exact match.

我玩了一点，不使用聚合函数就看不到任何解决方案。
对于Postgres，它将为 array_agg 。

I played a little with it and i can't see any solution without using an aggregate function. For Postgres it will be array_agg.

您需要生成一个SQL查询，例如：

You need to generate an SQL Query like:

SELECT *,  array_to_string(array_agg(colors.color_name), ',')) as color_names FROM cards
JOINS cards_colors, colors 
ON (cards.id = cards_colors.card_id AND colors.id = cards_colors.color_id)
GROUP BY cards.id HAVING  color_names = 'green, black' 

我从没使用过这些聚合器，所以也许array_to_string是错误的格式化程序，无论如何，您都必须注意按字母顺序汇总颜色。只要您没有太多的卡片，它就会足够慢，但是它将扫描表中的每张卡片。

I never used those aggregators, so perhaps array_to_string is a wrong formatter, anyway you have to watch for aggregating the colors in alphabetical order. As long as you aint't having too many cards it will be slow enough, but it will scan every card in a table.

我想在此查询上使用索引，您应该对数据结构进行规范化，使用颜色名称数组在卡片记录上，对该数组字段进行索引并对其进行搜索。您还可以保持标准化的结构并定义自动的关联回调，每次将颜色分配给卡片时，将颜色名称分配给卡片的color_names数组。

I you want to use an index on this query, you should denormalize your data structure, use an array of color_names on a cards record, index that array field and search on it. You can also keep you normalized structure and define an automatic association callback which will put the colorname to the card's color_names array every time a color is assigned to a card.

这篇关于在rails 4中将Array＃select转换为活动记录查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！