0

How would I convert the following sql query to Arel?

SELECT 
  SUM(
    LEAST(
      SPONSORSHIPS.AMOUNT_PER_LAP * (
        SELECT 
          SUM(LAPS.POINTS) 
        FROM 
          LAPS 
        WHERE 
          LAPS.CAMPAIGN_ID = :j_id
      ),
      SPONSORSHIPS.MAX_AMOUNT
    )
  ) 
FROM 
  SPONSORSHIPS 
WHERE 
  SPONSORSHIPS.CAMPAIGN_ID = :j_id;

There are 3 tables/models. jogathons, sponsorships, and laps. :j_id is the jogathon id.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
Cyrus
  • 3,687
  • 5
  • 35
  • 67
  • `LEAST` seems specific to PostgreSQL, so AREL does not support it natively. This makes your query very hard, if not impossible, to translate in a db-agnostic manner. The subselect itself is supported by arel, so the rest of the query may be implementable ; you just have to find a trick to get rid of `LEAST` – m_x Nov 13 '12 at 11:21
  • @m_x: Lots of databases understand LEAST and AREL supports things that aren't covered by the standard. The SQL standard and AREL have little to do with each other (and SQL and relational algebra are related but they're not the same thing either). You can usually replace LEAST with a CASE but I don't think AREL understands CASE (which is standard) either so you'd have to write SQL for that. I haven't seen any way to cleanly do subqueries or derived tables with AREL (I admit that I haven't looked that hard). – mu is too short Nov 13 '12 at 18:03
  • 1
    @muistooshort i agree ;my point was that if you want to translate a query in Arel, you probably want it to be db-agnostic too. AFAIK you can use `Arel::SQLLitteral` to create custom nodes, and subqueries are possible (there's an example in [arel's select_manager tests](https://github.com/rails/arel/blob/master/test/test_select_manager.rb), see 'can make a subselect'). Too bad it's so hard to find good documentation about arel... – m_x Nov 14 '12 at 08:13
  • 1
    @m_x: I didn't mean to be too critical, I just find it easier, clearer, and more compact to write SQL than figure out how to express it it using the various *friendly* tools and this is a pet peeve of mine. I hear on the documentation problems, it is a shame. – mu is too short Nov 14 '12 at 08:22
  • @muistooshort agreed. IMHO where Arel really shines is to create reusable, parameterizable bits of queries. This is why it's so useful in ORMs, but good'ol SQL for one-shot-one-purpose queries is better. As to me, i tend to use Arel whenever possible because it somehow helps me to think in a db-agnostic frame of mind : maybe because it is so limitating ? ;) – m_x Nov 14 '12 at 11:17
  • I think I'll just keep it as a direct sql query in my project for now. Thanks for explaining. – Cyrus Nov 18 '12 at 01:26

0 Answers0