Rails - Apr 2006 - Simple Question: How to merge SQL results?

Hopefully an easy one, how do I merge two or more SQL query results?

Example:
   result1 = find_by_sql(x)
   result2 = find_by_sql(y)

What is the best way to merge result1 and result2?  I want to be able  
to reference the objects as if they were obtained via one query.

Cheers,
Dan

On 4/20/06, Dan Harper <dan@danharper.org> wrote:
> Hopefully an easy one, how do I merge two or more SQL query results?
>
> Example:
>    result1 = find_by_sql(x)
>    result2 = find_by_sql(y)
>
> What is the best way to merge result1 and result2?  I want to be able
> to reference the objects as if they were obtained via one query.
The results are just arrays, so you can just do:

  resultset = result1 + result2

This *should* work:

result =      find_by_sql(x)
result.concat find_by_sql(y)

--  
-- Tom Mornini

On Apr 20, 2006, at 5:07 PM, Dan Harper wrote:
> Hopefully an easy one, how do I merge two or more SQL query results?
>
> Example:
>   result1 = find_by_sql(x)
>   result2 = find_by_sql(y)
>
> What is the best way to merge result1 and result2?  I want to be  
> able to reference the objects as if they were obtained via one query.

You could also use a UNION caluse in your SQL

On 4/21/06, Tom Mornini <tmornini@infomania.com>
wrote:
>
> This *should* work:
>
> result =      find_by_sql(x)
> result.concat find_by_sql(y)
>
> --
> -- Tom Mornini
>
> On Apr 20, 2006, at 5:07 PM, Dan Harper wrote:
>
> > Hopefully an easy one, how do I merge two or more SQL query results?
> >
> > Example:
> >   result1 = find_by_sql(x)
> >   result2 = find_by_sql(y)
> >
> > What is the best way to merge result1 and result2?  I want to be
> > able to reference the objects as if they were obtained via one query.
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060421/744eb364/attachment.html

Perfect, this works a treat.

I can then even chuck a result.uniq on the return statement to  
eliminate duplicate records in the result.  Ruby is so beautiful it  
brings a tear to the eye...

Thanks for the tip,
Dan


On 21/04/2006, at 10:24 AM, John Tsombakos wrote:
> On 4/20/06, Dan Harper <dan@danharper.org> wrote:
>> Hopefully an easy one, how do I merge two or more SQL query results?
>>
>> Example:
>>    result1 = find_by_sql(x)
>>    result2 = find_by_sql(y)
>>
>> What is the best way to merge result1 and result2?  I want to be able
>> to reference the objects as if they were obtained via one query.
>
> The results are just arrays, so you can just do:
>
>   resultset = result1 + result2
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>

One more way :

      result = []
      result << find_by_sql(x)
      result << find_by_sql(y)
      result << find_by_sql(z)
      return result.flatten.uniq


Alain