Transform SQL query to ActiveRecord Query

Question

Issue: I don't know how to convert this PostgreSQL query:

select news_id, sum(case when likes.like then 1 else -1 end) as max_positive from likes group by(news_id) order by(max_positive) desc

to ActiveRecord Query.

What I tried:

Like.group(:news_id)
    .sum('CASE WHEN likes.like THEN 1 ELSE -1 end')
    .max_by { |_k, v| v }

But result is array and not select news_id.

lusketeer · Accepted Answer · 2016-08-08 17:37:21Z

2

Like this?

Like.group(:news_id).select("news_id, SUM(case when likes.like then 1 else -1 end) as max_positive").order("max_positive desc")

answered Aug 8, 2016 at 17:37

lusketeer

1,9301 gold badge12 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tomek Wejchorowski Over a year ago

it's not all good. Your query correct select id, but not sum. ` #<ActiveRecord::Relation [#<Like id: nil, news_id: 53>, #<Like id: nil, news_id: 46>, #<Like id: nil, news_id: 58>...`

mu is too short Over a year ago

@TomekWejchorowski: It is there but the model class's inspect doesn't know it (stackoverflow.com/a/38270375/479863), just ask for it by calling the max_positive method on the returned model instances. See stackoverflow.com/a/24131675/479863, stackoverflow.com/a/27494947/479863, stackoverflow.com/a/18351188/479863, stackoverflow.com/a/17714928/479863, ...

Tomek Wejchorowski Over a year ago

Thank you very much @lusketeer. Great links!

Collectives™ on Stack Overflow

Transform SQL query to ActiveRecord Query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related