squeel

squeel copied to clipboard

I'm attempting to perform a row-wise comparison (9.16.6 at the very bottom) subquery using squeel. I've managed to hack it together with this.

message = select('created_at').where{ id.eq(my{message_id}) }.to_sql
where{ created_at.lte(`(#{message})`) & (self.id != my{message_id}) }

The key being the parenthesis around the subquery.

If I remove the escape ticks and #to_sql I get this error.

Cannot visit Arel::SelectManager

I'm sure I'm either missing something easy or this is a PostgreSQL edge case outside of the scope of squeel. Either way I haven't had any luck beyond what I have above.

Hi, Would you please provide a full example to demonstrate this issue? I will be glad to check where the problem is :)

The code example I posted above is a full example. That's directly copy and pasted from my code. Is there something more you need?

@anthonator maybe I misunderstood what you meant. I tried to guess your goal.

# I haven't seen any classes in both lines. 
# I guessed you were using query methods in an ActiveRecord. 
# In my opinion, you didn't need to use to_sql, just use the relation itself.
message = select('created_at').where{ id.eq(my{message_id}) }.to_sql
where{ created_at.lte(`(#{message})`) & (self.id != my{message_id}) }

My example:

message = User.select('created_at').where { id == 1 }
relation = User.where { created_at <= message }.where { id != 1 }
puts relation.to_sql

# => SELECT "users".* 
#    FROM "users" 
#    WHERE "users"."created_at" <= (SELECT created_at FROM "users" WHERE "users"."id" = 1) 
#    AND "users"."id" != 1

I think this example could run correctly. Hope it helps you.

And I'm sorry to reply you so late, I was too busy to review this last week :)

Sorry, let me try this again.

When I do this:

subquery = select('created_at').where{ id.eq(my{pageable_id}) }
relation = where{ created_at.lte(subquery) & (self.id != my{pageable_id}) }

I get:

Cannot visit Arel::SelectManager

I can't do a puts because it throws an exception before that happens.

The only way I can even get the query to execute is with the work around I posted in a previous comment.

Could you provide a runnable gist like #312 ? so we can debug what you described easily , thank you very much.