This is the query I've got at present:
SELECT t1.discipline_id AS discipline1,
t2.discipline_id AS discipline2,
COUNT(DISTINCT t1.product_id) as product_count
FROM (SELECT "product_disciplines".* FROM "product_disciplines") t1
INNER JOIN (SELECT "product_disciplines".* FROM "product_disciplines") t2
ON t1.product_id = t2.product_id
WHERE (t1.discipline_id < t2.discipline_id)
GROUP BY t1.discipline_id, t2.discipline_id
ORDER BY "product_count" DESC
Basically, I've got a list of Products and Disciplines, and each Product may be associated with one or more Disciplines. This query lets me figure out, for each possible (distinct) pair of disciplines, how many products are associated with them. I'll use this as input to a dependency wheel in Highcharts.
The problem arises when I involve Active Model Serializers. This is my controller:
class StatsController < ApplicationController
before_action :get_relationships, only: [:relationships]
def relationships
x = @relationships
.select('t1.discipline_id AS discipline1, t2.discipline_id AS discipline2, COUNT(DISTINCT t1.product_id) as product_count')
.order(product_count: :DESC)
.group('t1.discipline_id, t2.discipline_id')
render json: x, each_serializer: RelationshipSerializer
end
private
def get_relationships
query = ProductDiscipline.all
@relationships = ProductDiscipline
.from(query, :t1)
.joins("INNER JOIN (#{query.to_sql}) t2 on t1.product_id = t2.product_id")
.where('t1.discipline_id < t2.discipline_id')
end
end
each_serializer
points to this class:
class RelationshipSerializer < ApplicationSerializer
has_many :disciplines do
Discipline.where(id: [object.discipline1, object.discipline2])
end
attribute :product_count
end
When I query the database, there are ~1300 possible pairs, which translates my single query in ~1300 Discipline lookups.
Is there a way to avoid the N+1 queries problem with this structure?
I ended up splitting this in two separate API queries. RelationshipSerializer
saves just the discipline IDs,
class RelationshipSerializer < ApplicationSerializer
# has_many :disciplines do
# # Discipline.where(id: [object.discipline1, object.discipline2])
# [object.discipline1, object.discipline2].to_json
# end
attributes :discipline1, :discipline2
attribute :product_count
end
Since in my app I already need the list of available disciplines, I chose to correlate them client-side.