I have an API with Rails 5.0.5 in API mode. On every controller request we respond with render json: using Active Model Serializers. We use postgresql as a database.
Lets suppose this is my database:
Given that all the queries need the user to be logged in, then i can get a current_user object:
I can get all the projects for the user from the HABTM relation with something like:
render json: current_user.projects
I want to query Departments, ActivityTypes, and Status only for the projects the user belongs (without passing a params).
(In the real API there are 7 of this similar models belonging to Project, and at least 2 have at least a few hundred records. Also, the client needs to load the 7 of them when viewing a Project, to allow them to build new Activities from within the same view.)
From now i've come up with this to show on index, at least Departments from the organization users belong:
def index
# @departments = Department.all
@departments = Department.joins(project: 'organization').where(:project => { 'organizations' => {:id => current_user.organization.id }})
render json: @departments
end
With makes 3 queries in the database:
Started GET "/api/v1/departments" for 127.0.0.1 at 2017-08-09 19:40:57 -0400
Processing by Api::V1::DepartmentsController#index as HTML
User Load (0.4ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]]
Organization Load (0.4ms) SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]]
Department Load (6.8ms) SELECT "departments".* FROM "departments" INNER JOIN "projects" ON "projects"."id" = "departments"."project_id" INNER JOIN "organizations" ON "organizations"."id" = "projects"."organization_id" WHERE "organizations"."id" = $1 [["id", 1]]
[active_model_serializers] Project Load (0.5ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]]
[active_model_serializers] CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]]
[active_model_serializers] Project Load (0.3ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 [["id", 3], ["LIMIT", 1]]
[active_model_serializers] CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 [["id", 3], ["LIMIT", 1]]
[active_model_serializers] Rendered ActiveModel::Serializer::CollectionSerializer with ActiveModelSerializers::Adapter::Attributes (21.79ms)
Completed 200 OK in 110ms (Views: 33.5ms | ActiveRecord: 20.4ms)
That's from one side.
Also, the equivalent to Activities in my real API, have in fact thousands of records for each organization,
and if i can pass project_id as parameter, i can get get a more narrow subset of them, but i need to respond with a default query if no parameter is passed on.
All those queries in fact, have response times that have reached the order of several seconds in the production environment.
So, my questions when trying to optimize this are:
Is there a way to make those 3 queries only one?
Is there a way to make this default for all the #all queries of those models?
How can i respond with at least all the Activities from all the Projects the user have, if no 'project_id' is passed to Activities#index?
Or is out there some convention of how to code this better with Rails that i've been missing out?
I have also Pundit installed, but used only to authorize actions on users according to their role, i've seen it can be used to write custom scopes for models, but i have not got how to use it properly for anything complex than the "all projects for user's organization" query (use case for User with role "admin").
Also tried in the beginning to use "Apartment" gem, and separate schemas for each organization, in that case queries where simpler, but i gave me weird behaviors like randomly fail to create relations complaining some records didn't exist while creating the foreign keys. So i had to stop using it.
