Support Subqueries #150

Open
Wizek opened this Issue Nov 27, 2012 · 6 comments

Projects

None yet

5 participants

@Wizek
Wizek commented Nov 27, 2012

I'm redirected here from activerecord-hackery/squeel#187

Currently it seems we only have support for IN type of subqueries, like

SELECT 'users'.* FROM 'users' WHERE 'users'.'id' IN (SELECT max('users'.'id') FROM 'users')

It would be nice if we could write something like:

SELECT 'users'.* FROM 'users' WHERE 'users'.'id' < (SELECT max('users'.'id') FROM 'users')

This example is very simplistic and not overly useful at this complexity level, and only came up with it so that I could easily demonstrate the issue I (and others, please see activerecord-hackery/squeel#187) were having.

Furthermore, it would be all the best if by this improvement we could generate subquery-based single SQL statement wherever it is supported (not restricted to WHERE clause but also in SELECT, FROM, etc...)

@Wizek Wizek referenced this issue in activerecord-hackery/squeel Nov 27, 2012
Closed

Subqueries with some predicate matchers raise an exception #187

@dcunited001

IMO, if you're using subqueries, you're not doing it right.

Can you provide an example of what an Arel command would look like with a subquery? I'll look into the issue you referenced.

@Wizek
Wizek commented Dec 4, 2012

Have you had the chance to look into the referenced issue?

I don't feel experienced enough with ARel (I'd use this feature through Squeel) to write an example off the top of my head. If Squeel examples are sufficient, you can find some of those in the referenced issue.

Slightly off topic: Is there a complete tutorial, documentation or refference guide for ARel somewhere? One that could help me construct an example and to help me further my knowladge.

@dmitry
dmitry commented Nov 7, 2013

It works like that:

arel = User.arel_table

User.where(arel[:id].lt(Arel.sql("(#{User.select(Arel::Nodes::Max.new([arel[:id]])).to_sql})")))
@filipegiusti

@dmitry thanks, that hack works.

I'm trying to use subqueries for getting the down votes that happened after the last up vote. I couldn't find a different way to do it in sql and arel doesn't support it without a hack.

The code below produces a string with !ruby/object on it.

feedback = Feedback.arel_table
subquery = Feedback.select(feedback[:created_at].maximum).where(vote: :up)
Feedback
  .where(vote: :down)
  .where(feedback[:created_at].gt(subquery)).to_sql

@dcunited001 do you still think arel shouldn't support it?

@dmitry
dmitry commented Jul 30, 2015

Arel.sql("(#{subquery})") should fix that.

@kbrock
kbrock commented Apr 17, 2016

One issue is your subquery which is created with Feedback.select().where() uses a string builder. So the bind values are lost. Then when the time comes to build Feedback.where().where(subquery), it builds bind variables for just the main query, and the bind variables associated with subquery are not added.

This logic is found in associations.

Think the main problem here is with rails not arel.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment