Rails ActiveRecord friendly code from a Complex Join, Sum, and Group query
- by Chad M
PROBLEM
Hello,
I am having no luck trying to break down this SQL statement into ActiveRecord/Rails friendly code and I'd like to learn how I can avoid a find_by_sql statement in this situation.
Scenario
I have users that create audits when they perform an action. Each audit is of a specific audit_activity. Each audit_activity is worth a certain number of points, based on score_weight. I need to find the total scores of each user, based on their total accumulated audit_activity score_weights. Eventually I'll need to rank them which means adding a sort to this as well.
My Code
Here is my sql and simplified versions of the tables in question. Any thoughts?
SQL with full column names (for clarity)
SELECT users.id, u.email, SUM(audit_activity.score_weight)
FROM users
JOIN audits ON users.id = audits.user_id
JOIN audit_activities ON audit_activities.id = audits.audit_activity_id
GROUP BY users.id;
Models: User, Audit, AuditActivity
User fields: id, email
class User < ActiveRecord::Base
include Clearance::User
has_many :audits
end
Audit fields: id, user_id, audit_activity_id
class Audit < ActiveRecord::Base
belongs_to :user
belongs_to :audit_activity
end
AuditActivity fields: id, score_weight
class AuditActivity < ActiveRecord::Base
has_many :audits
end
Example Data
Here is a set of SQL statements so you can play with similar data I'm working with and see what comes up when the concerned query is run. You should just be able to copy/paste the whole thing into a database query browser.
CREATE TABLE users(
id INTEGER NOT NULL,
email TEXT (25),
PRIMARY KEY (id)
);
CREATE TABLE audits(
id INTEGER NOT NULL,
user_id INTEGER,
audit_activity_id INTEGER,
PRIMARY KEY (id)
);
CREATE TABLE audit_activities(
id INTEGER NOT NULL,
score_weight INTEGER,
PRIMARY KEY (id)
);
INSERT INTO users(id, email)
VALUES(1, "[email protected]");
INSERT INTO users(id, email)
VALUES(2, "[email protected]");
INSERT INTO users(id, email)
VALUES(3, "[email protected]");
INSERT INTO audits(id, user_id, audit_activity_id)
VALUES(1, 1, 1);
INSERT INTO audits(id, user_id, audit_activity_id)
VALUES(2, 1, 2);
INSERT INTO audits(id, user_id, audit_activity_id)
VALUES(3, 1, 1);
INSERT INTO audits(id, user_id, audit_activity_id)
VALUES(4, 1, 3);
INSERT INTO audits(id, user_id, audit_activity_id)
VALUES(5, 1, 1);
INSERT INTO audits(id, user_id, audit_activity_id)
VALUES(6, 1, 4);
INSERT INTO audits(id, user_id, audit_activity_id)
VALUES(7, 2, 4);
INSERT INTO audits(id, user_id, audit_activity_id)
VALUES(8, 2, 4);
INSERT INTO audits(id, user_id, audit_activity_id)
VALUES(9, 2, 4);
INSERT INTO audits(id, user_id, audit_activity_id)
VALUES(10, 3, 3);
INSERT INTO audits(id, user_id, audit_activity_id)
VALUES(11, 3, 2);
INSERT INTO audits(id, user_id, audit_activity_id)
VALUES(12, 3, 2);
INSERT INTO audits(id, user_id, audit_activity_id)
VALUES(13, 3, 2);
INSERT INTO audits(id, user_id, audit_activity_id)
VALUES(14, 3, 3);
INSERT INTO audits(id, user_id, audit_activity_id)
VALUES(15, 3, 1);
INSERT INTO audits(id, user_id, audit_activity_id)
VALUES(16, 3, 1);
INSERT INTO audit_activities(id, score_weight)
VALUES(1, 1);
INSERT INTO audit_activities(id, score_weight)
VALUES(2, 2);
INSERT INTO audit_activities(id, score_weight)
VALUES(3, 7);
INSERT INTO audit_activities(id, score_weight)
VALUES(4, 11);
The Query
Again, here is the query.
SELECT u.id, u.email, SUM(aa.score_weight)
FROM users u
JOIN audits a ON u.id = a.user_id
JOIN audit_activities aa ON aa.id = a.audit_activity_id
GROUP BY u.id;
Many Thanks,
Chad
