Self joins in Rails

I have the following tables: users(id), articles(id), favorites(article_id, user_id). I need to list articles added to favorites by current user with a flag indicating if they were also favori开发者_开发问答ted by other users. SQL is quite simple:

select articles.id, count(f2.article_id)
from articles a
inner join favorites f1 on f1.article_id = a.id
left join favorites f2 on f1.article_id = f2.article_id and not f1.user_id = f2.user_id
where f1.user_id = 1
group by a.id

Is there a way to do it using Rails query generator?

something like this should get you on your way (assuming you have an Article and Favorites model/table):

t = Article.joins("INNER JOIN #{Favorite.table_name} AS f1 on f1.article_id = #{Article.table_name}.id")
t = t.joins("LEFT JOIN #{Favorite.table_name} AS f2 on f1.article_id = f2.article_id AND NOT f1.user_id = f2.user_id")
t = t.where("f1.user_id = ?", 1)
t = t.group("#{Article.table_name}.id")
t.select("#{Article.table_name}.id, COUNT(f2.article_id)")