I have a many-to-many, HMT model setup and I want to add a count value to return as part of my tab_community#index method. The models for the table of interest and the join table are as follows:
class TabCommunity < ApplicationRecord
belongs_to :ref_community_type
has_many :tab_client_project_communities
has_many :tab_projects, through: :tab_client_project_communities
has_many :tab_community_accounts
has_many :tab_accounts, through: :tab_community_accounts
has_many :tab_client_project_communities
has_many :ref_platforms, through: :tab_client_project_communities
end
class TabCommunityAccount < ApplicationRecord
belongs_to :tab_community
belongs_to :tab_account
end
The #index method currently looks like this:
_tab_community_ids = params[:tab_community_ids].split(',')
@tab_communities = TabCommunity.where(id: _tab_community_ids).includes(:ref_platforms).all.order(:updated_at).reverse_order
This query is what I want to replicate in ActiveRecord:
select (select count(*) from tab_community_accounts where tab_community_id = c.id) as cnt, c.* from tab_communities c
The results I want are below:
7318 149 sports_writers 7 2017-12-17 15:45:36.946965 2017-12-17 15:45:36.946965
0 172 random_admin 8 2018-04-16 19:21:21.844041 2018-04-16 19:21:21.844041
2731 173 random_aacc 7 2018-04-16 19:22:35.074461 2018-04-16 19:22:35.074461
(The 1st column is count(*) from tab_community_accounts, the rest is from tab_communities.)
From what I've seen so far I should use either .select() or .pluck() but neither one works for me. I tried this out:
TabCommunity.pluck("distinct tab_community_accounts.tab_account_id as cnt").where(id: _tab_community_ids).includes(:ref_platforms).all.order(:updated_at).reverse_order
Is this close to what I need or am I completely off?
What you want is something like:
@tab_communities = TabCommunity
.where(id: _tab_community_ids)
.select('tab_communities.*, count(tab_community_accounts.id) AS cnt')
.left_outer_joins(:tab_community_accounts)
.includes(:ref_platforms) # consider if you actually need this
.group(:id)
.order(updated_at: :desc) # use an explicit order instead!
TabCommunity Load (1.1ms) SELECT tab_communities.*, count(tab_community_accounts.id) AS cnt FROM "tab_communities" LEFT OUTER JOIN "tab_community_accounts" ON "tab_community_accounts"."tab_community_id" = "tab_communities"."id" WHERE "tab_communities"."id" = 1 GROUP BY "tab_communities"."id" ORDER BY "tab_communities"."updated_at" DESC
=> #<ActiveRecord::Relation [#<TabCommunity id: 1, created_at: "2018-05-07 21:13:24", updated_at: "2018-05-07 21:13:24">]>
.select just alters the SELECT portion of the query. The result returned is still an ActiveRecord::Relation containing model instances.
ActiveRecord will automatically create an attribute for cnt:
irb(main):047:0> @tab_communities.map(&:cnt)
=> [1]
.pluck on the other hand just pulls the column values and returns an array or array of arrays if the query contains multiple columns.
@tab_communities = TabCommunity
.where(id: _tab_community_ids)
.left_outer_joins(:tab_community_accounts)
.includes(:ref_platforms) # consider if you actually need this
.group(:id)
.order(updated_at: :desc)
.pluck('tab_communities.id, count(tab_community_accounts.id) AS cnt')
(1.0ms) SELECT tab_communities.id, count(tab_community_accounts.id) AS cnt FROM "tab_communities" LEFT OUTER JOIN "tab_community_accounts" ON "tab_community_accounts"."tab_community_id" = "tab_communities"."id" WHERE "tab_communities"."id" = 1 GROUP BY "tab_communities"."id" ORDER BY "tab_communities"."updated_at" DESC
=> [[1, 1]]
Using .* with pluck is not a good idea since you don't know what order the attributes have in the resulting array.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments