Explicit Alias in Arel joins

[Dmytrii Nagirniak]

Hi,

I wonder how is it possible to provide an alias for joins with Arel?

We probably can use the `alias` method of the relation, but there should be an easier way to do that.

Assuming the Comment class in a Blogging app.

We can write something simple as

Comment.scope :owned_by, lambda {|user| joins(:post).where(["posts.user_id = ?", user.id])

but I prefer to explicitly reference the joins via aliases when the queries get more complex.

What I would like to write is this:

Comment.scope :owned_by, lambda {|user| joins(:post, :alias => 'p').where(["p.user_id = ?", user.id])

Can you suggest how I can do it?

Cheers,

Dima

http://www.ApproachE.com

[Chris Berkhout]

Hi Dmytrii,

Arel does let you alias a table.

I might be improperly mixing the rails and pure Arel syntaxes, but
hopefully this will help:

Comment.scope :owned_by, lambda do |user|
p = Arel::Table.new(:post).alias("p")
joins(p).where(p[:user_id].eq(user.id))
end

Cheers,
Chris

> --
> You received this message because you are subscribed to the Google Groups
> "Ruby or Rails Oceania" group.
> To post to this group, send email to rails-...@googlegroups.com.
> To unsubscribe from this group, send email to
> rails-oceani...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/rails-oceania?hl=en.
>

[Ryan Bigg]

I would really really really suggest here to not use a lambda here, mainly because it's ugly in comparison to a class method. Others suggest the same: http://stackoverflow.com/questions/5899765/activerecord-rails-3-scope-vs-class-method/5899890#5899890

So in this case, I would write it like this inside the Comment class.

class Comment < ActiveRecord::Base

  def self.owned_by(user)

    p = Arel::Table.new(:posts).alias("p")

    joins(p).where(p[:user_id].eq(user.id))

  end

end

It looks a lot cleaner, and comes with the added benefit that you can document the owned_by method. 

Because, as you know, *everybody* writes documentation.

[Dmytrii Nagirniak]

> I would really really really suggest here to not use a lambda here, mainly because it's ugly in comparison to a class method.

Thanks. Note taken :)

>> Comment.scope :owned_by, lambda do |user|
>> p = Arel::Table.new(:post).alias("p")
>> joins(p).where(p[:user_id].eq(user.id))
>> end

Yeah. I know this syntax, but it requires one additional line and then using that alias object in the 'where' clause.
What I ideally would like to write is something a bit closer to SQL, so that I can just say "this join is alias as `p`" and then use the p.

I realise what you suggested does exactly this, but I wonder if there is a more one-liner-ish way? Like so:

joins(:post, "p").where("p.user_id = ?", user.id)

it just reads easier than creating an alias as an object.

[Ryan Bigg]

Nope, this sadly isn't possible. You're going to have to use straight-ARel for this or devise your own methods for it.

[Dmytrii Nagirniak]

Well, I guess bad luck for me this time.

If it will become an issue I think extending relation with additional method is the next step.

I have just played a bit more with it and it seems that we can write:

joins("posts p on p.id = comments.post_id").where("p.user_id = ?", user.id)

Not exactly what I wanted and not really leveraging Arel here. But is as close as I could get there.

Nope, this sadly isn't possible. You're going to have to use straight-ARel for this or devise your own methods for it.