stack overflow: http://stackoverflow.com/questions/24215170/convert-arrayselect-to-active-record-query-in-rails-4/24231582

stack: ruby on rails 4, postgresql

scheema: cards have and belong to many colors, colors have attribute color_names.

desired functionality: I'm making a magic the gathering web app as a pet project. I'm trying to mimic cardkingdom.com in the search for colors. basically, any card can have any permutation of 5 colors (blue, black, green, red, white) and i have a search form that needs to be able to search for mono-black cards, black-green cards but not return mono-green cards, or black-green-red cards etc. my current implementation began to break because i have an Array#select statement which must iterate over my entire query and call an association on every item: O(n)

issue: im having trouble using active record (or writing an sql query) that will select only cards with exact color matches: ex cards.where( array = array ). im trying to replace my Array#select with an AR query so i dont need to iterate over my collection.

solutions (not working): most of the info is in the stack overflow thread but ill recap here.
someone suggested using a query along the lines of:

but its returning the error

and i dont know how to fix it


my current implementation is this:
color comes in as a parameter from the form and is manipulated into:



and the search is ran with this code


this implementation works, but takes 5 seconds to complete, as my db has grown to 10k+ cards.

i'm not a postgres expert, i've never heard of the JOINS keyword in postgres, just JOIN. are you using some kind of postgres extension? have you tried to just run it in your postges IDE? pgadmin or whatever you use

if you're mixing it up with another flavour's syntax, try something like this:


i've never used arrays inside of postgres, so i'm not sure what the syntax is for that. iirc you can't use column aliases in where clauses, but i could be wrong.

This post was edited by carteblanche on Jun 15 2014 01:02pm

yea that joins was definitely a typo. theres an issue with that sql anyway, because i cant call cards.color_names as it would be cards.colors and each color has a color_name

i'm not sure i understand your problem. if you need help writing the sql, post your table structure. if you solved the sql problem, can you explain what the problem is?

using your query (fixed some typos):



yields this error:


because cards are related to colors with a has_and_belong_to_many relationship. meaning i cant call cards.color_name


scheema:



models (they have other non-related relations and validations):



This post was edited by Kamikizzle on Jun 15 2014 02:33pm

i don't know anything about Magic, so you'll have to explain what you want to return better.



i'm not clear how the colours work. you said any permutation is available. does that mean blue-black is different from black-blue? and this card can have both options? or did you mean to say any combination is available? eg blue-black is the same as black-blue?

if you meant any combination is available, and i'm guessing colours are constant, then i suggest you redesign your mapping table. eg, tablename = 'colours', columns = {cardid, blue, black, green, red, white}

then your query would be like so (example of all blue-green-white cards):

select * from cards
inner join colours on colours.cardid = cards.cardid
where colours.blue = 'Y' and colours.black = 'N' and colours.green = 'Y' and colours.red = 'N' and colours.white = 'Y'

i used separate varchar columns for clarity, but you can stick them all into a single column or use numerics/boolean if you wish. imo this design is the simplest and prolly performs best. your own design seems to overcomplicate it.

if you were correct by saying permutation instead of combination, then it'll be much different and i'll revisit it once you confirm. essentially your columns would be colour1, colour2, etc. and you'd query them in the order given. eg colour1 = 'blue' and colour2 = 'black', normalized as desired.

This post was edited by carteblanche on Jun 15 2014 03:02pm

its combination. blue-black == black-blue.
i was considering something along the lines of your implementation. i just wanted to try it this way to avoid overhauling my db.

since it sounds like you're not very far, i suggest you make the db change. can't take more than a few hours, i'm guessing.

how are you chaining a colour combination with a card? is colors.color_name a delimited list of colours? eg: "black,blue"? if that's the case, i recommend you sort them alphabetically, then you can build the query in your code, eg where colors.color_name='black,blue'. though if this is the case, you might as well use my design for it without the table change. eg instead of 'black,blue' you can use 'YYNNN' where each position corresponds to the colours

if you're using a single color in color_name, then you'll have to explain how you're differentiating between:
blue
green
blue+green

theyre just color objects with a single name. so theres only 6 color objects and their color names are black, blue, green, red, white, colorless.
the behavior is as follows:
mono-green card: card.colors = [<Color object color_name: 'Green'>]

greeb-blue card: card.colors = [<Color object color_name: 'Green'>, <Color object color_name: 'Blue'>]

i think i need to redo the db..




This post was edited by Kamikizzle on Jun 15 2014 04:43pm

if colors.color_name is a single color, please show me sample data in your database to represent this.

assuming this is the colors table:
color_id = 1, color_name = black
color_id = 2, color_name = blue
color_id = 3, color_name = red
color_id = 4, color_name = green

i'm assuming this is how you set up your card:color correlations:

card_colors table:
card_id = 1, color_id = 4 -- this card is available in green
card_id=1, color_id = 2 -- this card is also available in blue

now how are you representing that the card is available in green, blue, and blue|green?

This post was edited by carteblanche on Jun 15 2014 05:06pm