CODEWITHSUNDEEP
mysqlruby-on-rails-3activerecordjoinassociations
Manish Shrivastava
Manish Shrivastava

Reputation: 32010

Convert SQL query to Rails Active Record query

Anyhow, I am able to fetch result in rails console by putting lines below

group = UserGroup.find_by_sql("select user_group_memberships.user_group_id, sum(-bets.amount + bets.amount_paid) as bankroll_net_profit  from bets  inner join user_group_memberships ON bets.user_id = user_group_memberships.user_id GROUP BY user_group_memberships.user_group_id")

It gives result below:

-+-----------------------------------+
| user_group_id | bankroll_net_profit |
+---------------+---------------------+
|             1 |                4765 |
|            57 |                1517 |
|            58 |                 -20 |
|            62 |                1517 |
|            64 |                1517 |
|            66 |                1507 |
|            67 |                 995 |
|            82 |                1517 |
-+-----------------------------------+

But I needed to get active records in groups variable but I got an Array(checked via group.class) so I won't be able to do extra calculations in active records.

Can anybuddy help me to find Active Record of Above Mysql Query: I need Model.joins(:another_model)

Associations are below:

 class UserGroup < ActiveRecord::Base
   has_many :user_group_memberships
   has_many :users, :through => :user_group_memberships

class User < ActiveRecord::Base
  has_many :user_group_memberships
  has_many :user_groups, :through => :user_group_memberships


class Bet < ActiveRecord::Base
  belongs_to :user

Upvotes: 0

Views: 2008

Answers (1)

MrTheWalrus
MrTheWalrus

Reputation: 9700

It's probably not exact, but I imagine you'd want something like this:

UserGroup.
  select('user_groups.*, sum(-bets.amount + bets.amount_paid) as bankroll_net_profit').
  joins(:users => :bets).
  group('user_groups.id')

Hopefully that will at least be enough to point you in the right direction. Given the associations you show, Rails should be smart enough to join UserGroups to UserGroupMemberships, UserGroupMemberships to Users, and Users to Bets.

When you use .select, you can fetch the non-attribute values from the resulting ActiveRecord objects, so if you save this relation as user_groups, you should be able to call user_groups.first.bankroll_net_profit. Note that if you're using PostGreSQL, this will be a String, and need to be converted to the correct data type with .to_f or whatever.

Upvotes: 2

Related Questions