Convert Array#select to active record query in rails 4

Question

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

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.

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)

returns the error

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?

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

because im using rails 4, i ended up just converting colors to an array column as part of the cards table. — PhilVarg
– PhilVarg, Commented Jun 16, 2014 at 18:13

dre-hh · Accepted Answer · 2014-06-15 23:26:57Z

1

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

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

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.

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'

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.

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.

edited Jun 15, 2014 at 23:26

answered Jun 15, 2014 at 16:15

dre-hh

8,0742 gold badges36 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

PhilVarg Over a year ago

im not familiar with array agg or its downsides. someone mentioned something about sql injection, and being that this is just a pet project, im not worried about any kind of malicious user behavior (for the time being). but thank you for your edit ill look into this

PhilVarg Over a year ago

current query:

cards.find_by_sql("SELECT *, array_to_string(array_agg(cards.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)  WHERE color_names ='Green, Black' GROUP BY cards.id")

error:

PG::SyntaxError: ERROR:  syntax error at or near "cards_colors" LINE 1: ...color_name), ',') as color_names FROM cards JOINS cards_colo...

i havent been able to resolve the error. am i able to call cards.color_name? because it goes cards -> colors -> color_name

PhilVarg Over a year ago

definitely cant call cards.color_name

dre-hh Over a year ago

my fault, color_name is on colors not on cards, edited the query

PhilVarg Over a year ago

PG::GroupingError: ERROR: aggregate functions are not allowed in WHERE LINE 1: ...rs.id=cards_colors.color_id WHERE array_to_string(array_agg(... :[

waqar mirza · Accepted Answer · 2014-06-14 11:30:51Z

0

try this

colors = Color.where(color_name: col).pluck(:id)
Card.includes(:colors).where('colors.id'=> colors)

answered Jun 14, 2014 at 11:30

waqar mirza

5552 silver badges15 bronze badges

1 Comment

PhilVarg Over a year ago

the issue with the 'IN' statement, is that when i search for ['Black','Blue'] i need cards that are ONLY black-blue. this pulls cards that are mono-black, and mono-blue as well

Collectives™ on Stack Overflow

Convert Array#select to active record query in rails 4

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related