Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
# totally contrived example
User.select(Account[:name]).join(:accounts).where(User[:id].in(Group.where(:name => 'foo')))
@ernie
Copy link

ernie commented Apr 12, 2011

Not sure I follow the intent on this one. You're starting off with user, so you'd be returning a User object, but its only attribute would be name, which would be the value of the account's name? I get the right side of the query, doing a subselect, but the left side has me confused. I knew I should have had another cup of coffee this morning...

@tenderlove
Copy link
Author

tenderlove commented Apr 12, 2011

My point wasn't to produce a query that makes sense! ;-)

Basically, my point is that it seems there are cases when you would want to refer to tables and columns outside of your scope. So, even though we're querying the User model, we want to select the account.name. This line of code may not return a User object, but could be used to create other queries.

@ernie
Copy link

ernie commented Apr 12, 2011

Unless I'm smoking something, it would return a User object just because the chain started with User -- not that the object would be useful for much more than retrieving that "name" attribute.

Book.select('users.name').joins(:user).first
=> #
Book.select('users.name').joins(:user).first.name
=> "Some username"

But I think I get your point, and it's in line with what I was tweeting about earlier, which is that the whole Model[:attr] thing breaks down too quickly. Or loses its magic. Or whatever we want to call it.

In Squeel, we defer turning the wheres, etc into predicates until we are actually in the process of building the ARel relation. That lets us treat associations more like we'd expect to be able to:

User.select{accounts.name}.joins{accounts}.where{id.in(Group.where(:name => 'foo')}

OK, technically that last part won't work as I don't do subqueries right now (I'm curious as to whether it makes sense to do that or just take the rails route of converting them to a list of ids for use with IN). This looks almost like a glorified version of lolquery at first, but once you start to build up associations and the like, being able to refer to them by a keypath instead of their table name is very nice.

Or am I still missing your point?

@tenderlove
Copy link
Author

tenderlove commented Apr 12, 2011

WRT the user object, imagine we had this:

def magical_relation
  User.select(Account[:name]).join(:accounts).where(User[:id].in(Group.where(:name => 'foo')))
end

magical_relation.to_a # magical_relation results in a user object
ASubQuery.select('whatever').from(magical_relation) # magical_relation results in a relation

User.select doesn't necessarily result in a User object. It depends on the context.

I really like the block syntax of Squeel, but it seems to depend on the context in which it was executed. With the Model[:attribute] syntax, we can refer to attributes without context. That means we could have methods like:

def some_constraints
  [User[:name].eq 'Aaron']
end

def other_constraints
  [Account[:id].eq 100]
end

User.where(some_constraints + other_constraints)
Account.where(some_constraints + other_constraints)

This frees us from context. We're allowed to compose query arguments without knowing the context in which they will be used. Not only that, we can write regular, testable, ruby objects (taking advantage of modules, inheritance, etc) to compose complex queries.

I think having sweet block syntax like Squeel syntax would complement regular method composition swimmingly. Maybe I'm overthinking though. WDYT?

@ernie
Copy link

ernie commented Apr 12, 2011

OK, I get it now -- you were referring to using the result while still in Relation form. Makes more sense now.

I also like removing as much context as possible, and in the simpler cases, that would work great. It seems as though once joins enter the picture, a certain amount of context is necessary. That's why I piggy-(SQUEEL!!!)-back on JoinAssociation#table and JoinBase#table so heavily -- they take care of letting me know what the alias will be.

Consider this simple case, from the Squeel specs:

      it 'maps wheres inside a hash to their appropriate association table' do
        relation = Person.joins({
          :children => {
            :children => {
              :parent => :parent
            }
          }
        }).where({
          :children => {
            :children => {
              :parent => {
                :parent => { :name => 'bob' }
              }
            }
          }
        })

        arel = relation.build_arel

        arel.to_sql.should match /"parents_people_2"."name" = 'bob'/
      end

It's not in block form, ignore that fact for now. I don't see how the model syntax would work. Lazy interpretation of conditions, however, gets us part of the way there:

ruby-1.9.2-p180 :004 > id_constraint = Squeel::DSL.evaluate {id.eq 100}
 => #<Squeel::Nodes::Predicate:0x00000101558280 @expr=:id, @method_name=:eq, @value=100> 
ruby-1.9.2-p180 :005 > Article.joins(:person).where(id_constraint).to_sql
 => "SELECT "articles".* FROM "articles" INNER JOIN "people" ON "people"."id" = "articles"."person_id" WHERE "articles"."id" = 100" 
ruby-1.9.2-p180 :006 > Article.joins(:person).where(:person => id_constraint).to_sql
 => "SELECT "articles".* FROM "articles" INNER JOIN "people" ON "people"."id" = "articles"."person_id" WHERE "people"."id" = 100" 

@ernie
Copy link

ernie commented Apr 12, 2011

I should note that long, long ago, before Rails 3 and ARel 1.x were in the wild, I once played with something similar, only throwing ARel predicates directly into the where_values, etc, then trying to swap their relations out from under them at the last minute. In the end, I found it too brittle (or more likely, my skill at making it work well too weak), and ended up with something not unlike what you see above.

@tenderlove
Copy link
Author

tenderlove commented Apr 13, 2011

WRT join context, I see your point. But at the same time we can't be sure that the aliases we're picking are correct. For example, selecting from a self join:

Person.joins(:people).select(Person[:name])  # => yikes!

Do you select from the original table name, or the aliased table name? Even with context, that case (I think) would be ambiguous. Either we can guess (and possibly go wrong), or give the user exactly what they asked for with no guesses. Of course that means they would have to do extra work in the case of aliases.

WRT lazy evaluation in Squeel: I like it. I think it's good enough to get the job done (though I think you should rename the method to eval ;-) ).

I think I'm more and more convinced of a couple things:

  1. There is no DSL for SQL better than SQL (we're putting lipstick on a pig)
  2. I think of people want to use Model[:id], they should just use Squeel

@ernie
Copy link

ernie commented Apr 13, 2011

Your example (self-referencing associations) is precisely the reason I need to maintaining the context, and can't stomach the Person[:name] solution. :( We need to "mount" the constraints, for lack of a better term, on the proper table alias. Squeel does that right now via mapping the hash keys (or keypaths in the case of children.children.children) against the corresponding joins in the JoinDependency, then grabbing the JoinAssociation's table. Hope that makes sense.

See my post in the other thread for the pull request for sample queries and actual output.

@ernie
Copy link

ernie commented Apr 13, 2011

Also, I think I'm stealing your "putting lipstick on a pig" line for Squeel. It just fits.

@tenderlove
Copy link
Author

tenderlove commented Apr 13, 2011

lol! Please take it! :-D

@ernie
Copy link

ernie commented Apr 13, 2011

@tenderlove: see activerecord-hackery/squeel@f337842

FWIW: evaluate was a typical case of overthinking -- I didn't like stomping on Kernel#eval, even though there's no reason to care in this case. :)

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