CODEWITHSUNDEEP
sqlruby-on-railsrubyruby-on-rails-3activerecord
Ludovic
Ludovic

Reputation: 2002

Complex SQL with multiple inner join & count with rails

I would like to know if it's possible to write very complex SQL with Rails Active Record helper like this one (numbers will be replaced by variables later) :

SELECT 
photographs.id,
(COUNT(distinct comments.id) / 50.0 * 100 * 0.25) + (15 * 0.1) + (COUNT(distinct likes.id) / 100.0 * 100 * 0.05) +(COUNT(distinct likes.id) / COUNT(distinct views.id) * 100 * 0.4) +(COUNT(distinct likes.id) * 2.4  / 100.0  * 0.2) AS FINAL_RANKING
FROM photographs
INNER JOIN comments 
ON photographs.id = comments.photograph_id
INNER JOIN likes 
ON photographs.id = likes.photograph_id
INNER JOIN views 
ON photographs.id = views.photograph_id
GROUP BY photographs.id
ORDER by FINAL_RANKING DESC;

I found on Rails doc more simple example or simple multiple inner join but without counts

Upvotes: 0

Views: 1891

Answers (1)

Dan Grahn
Dan Grahn

Reputation: 9404

Rails accepts SQL in the select command.

See Active Record Query Interface | Selecting Specific Fields

Something like this should work for you:

Photographs.joins(:comments, :likes, :views, :users).where(
"users.id = 1").select("COUNT(whatever)")

No guarantee that actually runs.

Upvotes: 1

Related Questions