mardi 19 décembre 2017

How do I shorten a triple Joins ActiveRecord Query with a has_many through relations?

I'm trying to clean up a very long ActiveRecord query. The one I have is working, though it hurts to look at it. Here's what is happening.

1) User has_many Simulations through UserSimulations (and vice versa).

2) User has_many Groups through UserGroups (and vice versa).

3) Group has_many Simulations through SimulationGroups (and vice versa).

What ends up happening here is that a user can be associated to a Simulation in two ways, either directly through the has_many to has_many relationship, or indirectly through a Group that the user belongs to.

I've been able to gather all the Simulations a User has access to in a single query, and it looks like this. I have access to the user (current_user) object where the query needs to be called.

    # Define Queries
    user_sim_join = "LEFT JOIN user_simulations ON user_simulations.simulation_id = simulations.id"
    user_grp_join = "LEFT JOIN user_groups ON user_groups.group_id = groups.id"
    where_clause  = ["user_groups.user_id = :user_id OR user_simulations.user_id = :user_id", { user_id: user.id }]

    # Run Query
    Simulation.joins(user_sim_join, :groups, user_grp_join).where(where_clause).group('simulations.id') 

    => Simulation Load (1.1ms)  SELECT "simulations".* FROM "simulations" 
         INNER JOIN "simulation_groups" ON "simulation_groups"."simulation_id" = "simulations"."id" 
         INNER JOIN "groups" ON "groups"."id" = "simulation_groups"."group_id" 
         LEFT JOIN user_simulations ON user_simulations.simulation_id = simulations.id 
         LEFT JOIN user_groups ON user_groups.group_id = groups.id 
         WHERE (user_groups.user_id = 2 OR user_simulations.user_id = 2) 
         GROUP BY simulations.id

I'm happy that it's working, but would like to clean it up as to be more concise (not 4 lines of code to build a single query).

Thanks!

Aucun commentaire:

Enregistrer un commentaire