How to write this query in activerecord?

Question

Assuming I have an ActiveRecord::Base subclass User and table users, I am not sure how to write this query in ActiveRecord:

SELECT *
FROM (
    SELECT users.* 
    FROM   follows
    INNER JOIN users ON users.id = follows.following_id
    WHERE  username LIKE 'r%' AND follows.follower_id = 5717
    LIMIT 10

    UNION

    SELECT * 
    FROM   users 
    WHERE  username LIKE 'r%' 
    LIMIT 10
) AS users
LIMIT 10

I am selecting from a custom generated table. How would I even start writing this query? Is this even possible? If so how, and if not, what are my alternatives?

Thanks!

What results are you looking for? In your query you are going to return all users with a username LIKE 'r%', since all the results of the first part of your query will also be in the second part of your query. You need to explain what you would like your query to return since it is unclear from looking at your query. — Tom, Sep 18 '13 at 13:51
Just to point out, the query result set is indeterminate there, unless you add ORDER BY clauses to complement every LIMIT clause — David Aldridge, Sep 19 '13 at 07:03

score 0 · Accepted Answer · edited May 23 '17 at 10:25

Thanks, that's an interesting question.

Based on that UNION implementation, I think this should do the trick :

# first sub query, inversed compared to your's, so that we get user fields
followed = User.group( 'users.id, users.username, follows.follower_id' ).having( "users.username like 'r%' and follows.follower_id = 5717" ).joins( :follows )

# second sub query
user = User.where( "username like 'r%'" )

# joining both using union
related_people_query = [ followed, user ].map { |query| query.select( 'users.id' ).to_sql }.join( ' UNION ' )
related_people = User.where( "users.id IN (#{related_people_query)" ).limit(10)

Of course, that's a big query and I could not test it, so I would love to know if it works for you.

Edit : I forgot #joins on first subquery

Edit 2 : forgot limit

Thanks, as soon as I get to my dev environment, i will test/let you know. — 0xSina, Sep 18 '13 at 15:22

score 0 · Answer 2 · answered Sep 18 '13 at 17:30

0

It looks like you just want to get a list of ten users, where username like '%r' (mandatory), and where follows.follower_id = 5717 (preferred). If this the case, try the following (split for legibility; hasn't been tested):

User
  .joins("LEFT OUTER JOIN follows ON users.id = follows.following_id")
  .where("username like 'r%'")
  .order('follows.follower_id = 5717 desc, rand()')
  .limit(10)
  .uniq

answered Sep 18 '13 at 17:30

Jacob Brown

7,221
4
30
50

Thanks but this is what I was using before, it's very slow. – 0xSina Sep 18 '13 at 22:32

tihom · Answer 3 · 2013-09-19T06:48:16.677

If users with username LIKE r% is a small set of records. I would use eager loading

users = User
        # get all users with matching username and all the associated follows
       .where("username LIKE 'r%' ").includes(:follows)
        # arrange the records in ruby  to get users with given follower id first
       .sort{|x|  x.follows.map(&:follower_id).include?(5717) ? 0 : 1 }
        # return first 10 records
       .first(10)

Otherwise I would use a staggered approach

# try to get 10 records with matching username and follower_id using an inner join
users  = User.joins(:follows).where("username LIKE 'r%' AND follows.follower_id = ?", 5717).group("users.id").limit(10)

# if less than 10 records found, run second query to get users with matching records only
users += User.where(" username LIKE 'r%' AND id NOT IN (?)", users.map(&:id)).limit(10-user.count) if users.count < 10

Both the above methods would make 2 SQL queries. The option with single query is to do a outer join which seems to be slow for your case.

score 0 · Answer 4 · answered Sep 19 '13 at 08:06

0

I think you can use find_by_sql to achieve such complex queries

User.find_by_sql("SELECT * FROM (SELECT users.* FROM   follows INNER JOIN users ON users.id = follows.following_id WHERE  username LIKE 'r%' AND follows.follower_id = 5717 LIMIT 10 UNION SELECT * FROM   users WHERE  username LIKE 'r%' LIMIT 10) AS users LIMIT 10")

answered Sep 19 '13 at 08:06

Vineeth Pradhan

8,201
7
33
34

But sorry, I haven't tested it though. I don't have `mysql` and `rails` installed in my system right now. Please help yourself :) – Vineeth Pradhan Sep 19 '13 at 08:10

How to write this query in activerecord?

4 Answers4