I have a table with an integer column. I want to retrieve pairs of records where this integer sums to a total. For example:
4|red 6|blue 2|yellow
If I want pairs that add up to 8 I should get [[red,red],[blue,yellow], [yellow,blue]]. Writing the query myself I would just use a join with no ON clause (I think this is a cross join):
SELECT a.color, b.color FROM mytable a INNER JOIN mytable b WHERE a.value + b.value = 8
This works just fine from a sqlite console, but when I try to use find_by_sql I can't get all of the information. It attempts to fit everything into an instance of the model class, which means that I only see b.color.
What is the 'rails' way of doing what I am trying to accomplish? Is there a set of options I can use with find to recreate this query and get both colors out? I'm hoping for an array of 2-element arrays.