for a green black card:

ok, so each card_id has one colour tuple. eg a single card_id can't be available in both blue and green unless it's blue-green.

so here you go:



you're essentially trying to normalize your colours. i'd go with my original recommendation and say drop card_colors and colors tables and just stick your color columns on your cards table. but if you insist on keeping it this way, just use a view that i mentioned above to denormalize it. maybe wrap it with a nvl/coalesce/if/case or whatever it's called to convert it to N/Y format

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

so because, the colors will come in from a form, how do i dynamically input those colors? because i obv cant just hard code blue.color_name.
all in all its starting to look like this is not a good format for the db. ill probably try to bitmap it like your suggestion.

This post was edited by Kamikizzle on Jun 15 2014 05:34pm

you talking about the where clause in the SELECT or in the INSERT?

at this point, i assume your view looks like this:

vw_cards:
columns:
t.integer "card_id"
t.string "name", null: false
t.string "mana_cost"
t.integer "loyalty"
t.string "img_link"
t.float "converted_mana_cost"
t.text "oracle"
t.string "rarity"
t.integer "power"
t.integer "toughness"
t.integer "mtg_set_id"
t.datetime "created_at"
t.datetime "updated_at"
// boolean, integer, string, whatever
"has_blue"
"has_green"
"has_black"
"has_red"
"has_white"

and i'm guessing you have some sort of checkboxes on your screen, and the user checks each one?

so you'd say select * from vw_cards where has_blue=? and has_green=? and has_black=? and has_red=? and has_white=?

and you pass in chkHasBlue.isChecked(), chkGreen.isChecked(), chkBlack.isChecked(), chkRed.isChecked, chkWhite.isChecked()

lol use php....ruby syntax looks weird to me..wish i could be more help

Actually(stupid jsp said i didint have permission to edit):

lol use php....ruby syntax looks weird to me..but im familar with mysql and mtg

honestly i think you should store the card with the colors or color_ids

for instance in your vw_cards table do:
t.string "card_colors"

and insert it as "black" or "black,green" or "black,green,blue" ect

also i would add a 6th color: colorless, just for cmc reasons.

anyways, if you dont, i think it would make it easier

just do something like:


or this might even be better:


anyways i think that would work, you can always figure out how to set that up with your checkboxes

such as:

black: checked
green: checked

some cool javascript code that handles options based on colors chosen:
search for:

black:checked
black green:checked
green:unchecked

or exlude:
Multicolor:unchecked
mono black: unchecked
green: checked


then if it was me...i would do an ajax call on search... but up to you.

This post was edited by AkuuZ on Jun 16 2014 06:44am

yea im going to get started on carteblanche's suggestion today. ill probably build a 5-bit binary map for the form submission and some methods to convert that into the 5 color true false queries (which will work for colorless being 00000). passing around a string/binary value will make it alot easier. and in general search functions are an easy get request. the reason i dont want to ajax it is because pressing back break the user flow. and typically when i search im going back and forth all the time (instead of pressing the search button again).

i dont want to just hard code a string value in there like that because it leaves room for 0 flexibility and its a pretty unconventional way of doing things. because this is just a pet project (for learning sake), adopting best practices is a priority. in retrospec, looking into a nosql db would probably have been the best solution, but i defaulted to postgresql because ive never used a nosql

This post was edited by Kamikizzle on Jun 16 2014 08:37am

well holy fuck. you can use arrays as a column type in rails 4
this solves all my problems http://reefpoints.dockyard.com/ruby/2012/09/18/rails-4-sneak-peek-postgresql-array-support.html

i didnt mean a literal string, sorry.
in your jquery get() u would pass the is_colors and not_colors in a function, and post them to you back end request page and do the querys using the variables..you might have M-D arrays you can break them down if you need..

$color_search1 = colors[0][1] or w/e..just count length and you should be fine... thats prolly what i would do...but idk