On May 21, 6:34 am, byrnejb <[email protected]> wrote:
> On May 20, 4:42 pm, Jeremy Evans <[email protected]> wrote:
> .
>
>
>
> > If you have questions about the best way to accomplish something in
> > Sequel for a given snippet of ActiveRecord code, please post here.
>
> Thank-you very much. I am writing a new project using Rails and have
> some misgivings about AR's approach to SQL and RDBMS in general. I
> have selected PostgreSQL as the backend and there are a lot of things
> available in PG that AR makes difficult to use. So I am considering
> replacing AR before I become too bound to its implementation specific
> attributes.
>
> The most recent case was a need to report the most recent N records in
> each category from a table that contained multiple categories. I
> ended up with SQL that looked like this:
>
> -- Selects most recent exchange rate for all currencies
> -- Change LIMIT value to N to get N most recent rates
>
> SELECT *
> FROM currency_exchange_rates AS xchg1
> WHERE id
> IN (
> SELECT id
> FROM currency_exchange_rates as xchg2
> WHERE
> xchg1.currency_code_base = xchg2.currency_code_base
> AND
> xchg1.currency_code_quote = xchg2.currency_code_quote
> ORDER BY currency_code_base,
> currency_code_quote,
> effective_from DESC
> LIMIT 1 )
> ORDER BY currency_code_base,
> currency_code_quote,
> effective_from DESC
>
> I basically had to pass this code to AR.find_by_sql() as a string,
> using "#{}" to substitute the LIMIT value dynamically. I am not sure
> if any ORM is capable of dealing with this sort of construct. It
> seemed to me as if there was no other way to do it with AR, short of
> reading the entire table into an array and doing the sorting and
> selecting inside Ruby. How would SEQUEL do it?
order =
[:currency_code_base, :currency_code_quote, :effective_from.desc]
subquery = DB[:currency_exchange_rates___xchg2].
filter(:xchg1__currency_code_base=>:xchg2__currency_code_base).
filter(:xchg1__currency_code_quote=>:xchg2__currency_code_quote).
order(*order).
limit(1)
DB[:currency_exchange_rates___xchg1].
filter(:id=>subquery).
order(*order)
That yields the following SQL:
SELECT * FROM "currency_exchange_rates" AS "xchg1"
WHERE ("id" IN (
SELECT * FROM "currency_exchange_rates" AS "xchg2"
WHERE (("xchg1"."currency_code_base" = "xchg2"."currency_code_base")
AND
("xchg1"."currency_code_quote" =
"xchg2"."currency_code_quote"))
ORDER BY "currency_code_base", "currency_code_quote",
"effective_from" DESC
LIMIT 1))
ORDER BY "currency_code_base", "currency_code_quote", "effective_from"
DESC
Jeremy
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
