1

How to make squeel generate RIGHT OUTER JOIN?

This:

  User.joins{comment.outer}

generates LEFT OUTER JOIN.

No, I cannot do Comment.joins{user.outer}, the whole query is much more complicated and I must start it with User.

If not in squeel maybe someone knows the other Rails-way to prepare such query?

EDIT:

Thank's @cpuguy83. I tried solution from the first comment, but it's not so simple. The right join I'm trying to do is just the beginning of chain query. I need another calls to joins to join relations that are connected through Comment. Unfortunately, if I do something like this:

User.joins("right outer join comments").joins(comments: :author)

I get the comments relation joined twice: once by first call to joins, and once by the second one. Is there a way to solve it, or I should switch to another call to joins with raw SQL JOIN statement?

wrzasa
  • 1,113
  • 10
  • 20
  • 1
    User.joins("right outer join comments")? – cpuguy83 May 13 '13 at 15:37
  • So the only way is to put almost whole SQL as a query parameter? Why then should I bother with object-oriented s..omething instead of using pure SQL? Is the object oriented query interface so poor? – wrzasa May 13 '13 at 16:06
  • Yep, it sucks when you have to write SQL directly... you are welcome to make a PR to ActiveRecord. – cpuguy83 May 13 '13 at 16:27
  • No. It sucks when you have to use both: OO and SQL at the same time in one search... – wrzasa May 13 '13 at 16:41
  • The query interface is not OO, it's an abstraction so you don't have to write SQL. – cpuguy83 May 13 '13 at 16:42
  • OK, its an OO abstraction that is well... rickety :( – wrzasa May 13 '13 at 16:46
  • 1
    Conceptually, how does it make sense to do a right outer join here? You are returning User objects, but a right outer essentially will show you Comments without a User association, thus returning nil because you are expected to be returning a list of User objects. – cpuguy83 May 13 '13 at 16:56
  • Conceptually, I just simplified problem and changed relation names to something standard to avoid necessity to present the whole domain of my app here... Probably users and comments are not perfect example here, you can however easily imagine different names where it makes perfect sense to have one-to-many relation not obligatory at the 'one' end. – wrzasa May 13 '13 at 17:07
  • Yep, you'll have to do it in raw SQL... I think the point here is that Ruby is MUCH more powerful than SQL, and you MAY be better off doing this processing in Ruby, at least in terms of how to express it and future changeability. – cpuguy83 May 13 '13 at 17:13
  • 1
    Well... you mean to pull all data from database to Ruby and process all that in Ruby arrays and hashes? Having whole SQL server ready to do the job? – wrzasa May 13 '13 at 17:17
  • RIGHT OUTER JOIN is syntactic sugar for LEFT OUTER JOIN. In principal, I think any query that needs a right join can be reordered to a logically identical left join. Did you ever solve your problem or have an answer to post for us? – dcorking Sep 16 '16 at 09:53
  • 1
    @dcorking right. In SQL it IS syntactic sugar and you certainly CAN reorder any query to use left or right join as you wish. In the OO abstraction of AR it is not so simple, because order is used to infer other parameters of the query. So as I stated I had to start the query from the User model. As you see the question is more then 3 years old and I don't remember how I actually did it, but certainly had to go to SQL. I also don't remember the actual complicated reasons behind this query, but I'm certain I didn't find solution not involving SQL. – wrzasa Sep 28 '16 at 12:49

0 Answers0