Rails的ActiveRecord的友好code，从一个复杂的联接，点心，和组查询 [英] Rails ActiveRecord friendly code from a Complex Join, Sum, and Group query

查看：196
本文介绍了Rails的ActiveRecord的友好code，从一个复杂的联接，点心，和组查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

问题

您好，

我有没有运气试图打破这条SQL语句到的ActiveRecord / Rails的友好code，我想了解我如何能避免这种情况的find_by_sql语句。

场景 我有创造的审计时，他们执行的操作的用户。每个审计是一个特定的audit_activity的。每个audit_activity是值得一定的点数，基于score_weight。我需要找到每个用户的总分数，基于它们的总积累audit_activity score_weights。最后，我需要对他们进行排名，这意味着增加了排序这一点。

我的code 这里是我的SQL和表中问题的简化版本。有什么想法？

SQL全列名（为清楚起见）

  SELECT users.id，u.email，SUM（audit_activity.score_weight）
从用户
JOIN审计ON users.id = audits.user_id
JOIN audit_activities ON audit_activities.id = audits.audit_activity_id
GROUP BY users.id;

模式：用户，审计，AuditActivity

用户字段：ID，邮箱

 类用户的LT;的ActiveRecord :: Base的
 包括间隙::用户
 的has_many：审计
结束

审核字段：ID，USER_ID，audit_activity_id 

 类审计＆LT;的ActiveRecord :: Base的
  belongs_to的：用户
  belongs_to的：audit_activity
结束

AuditActivity字段：ID，score_weight 

 类AuditActivity＆LT;的ActiveRecord :: Base的
  的has_many：审计
结束

示例数据

下面是一组SQL语句，所以你可以用相似的数据我正在玩，看看会发生什么吧有关查询运行时。你应该能够复制/粘贴整个事情到数据库中查询浏览器。

  CREATE TABLE的用户（
 ID INTEGER NOT NULL，
 电子邮件正文（25）
 PRIMARY KEY（ID）
）;

CREATE TABLE审计（
 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用户（ID，电子邮件）
值（1，1user@a.com）;
INSERT INTO用户（ID，电子邮件）
VALUES（2，2user@b.com）;
INSERT INTO用户（ID，电子邮件）
VALUES（3，3user@c.com）;

INSERT INTO审计（ID，USER_ID，audit_activity_id）
值（1，1，1）;
INSERT INTO审计（ID，USER_ID，audit_activity_id）
VALUES（2，1，2）;
INSERT INTO审计（ID，USER_ID，audit_activity_id）
VALUES（3,1，1）;
INSERT INTO审计（ID，USER_ID，audit_activity_id）
VALUES（4,1，3）;
INSERT INTO审计（ID，USER_ID，audit_activity_id）
VALUES（5，1,1）;
INSERT INTO审计（ID，USER_ID，audit_activity_id）
VALUES（6,1，4）;

INSERT INTO审计（ID，USER_ID，audit_activity_id）
VALUES（7，2,4）;
INSERT INTO审计（ID，USER_ID，audit_activity_id）
VALUES（8,2，4）;
INSERT INTO审计（ID，USER_ID，audit_activity_id）
VALUES（9,2，4）;

INSERT INTO审计（ID，USER_ID，audit_activity_id）
VALUES（10，3，3）;
INSERT INTO审计（ID，USER_ID，audit_activity_id）
VALUES（11，3，2）;
INSERT INTO审计（ID，USER_ID，audit_activity_id）
VALUES（12，3，2）;
INSERT INTO审计（ID，USER_ID，audit_activity_id）
VALUES（13，3，2）;
INSERT INTO审计（ID，USER_ID，audit_activity_id）
VALUES（14，3，3）;
INSERT INTO审计（ID，USER_ID，audit_activity_id）
VALUES（15，3，1）;
INSERT INTO审计（ID，USER_ID，audit_activity_id）
VALUES（16，3，1）;

INSERT INTO audit_activities（ID，score_weight）
值（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）;

的查询 同样，这里是查询。

  SELECT u.id，u.email，SUM（aa.score_weight）
来自用户的ü
JOIN审计一个ON u.id = a.user_id
JOIN audit_activities AA对aa.id = a.audit_activity_id
GROUP BY u.id;

非常感谢， 乍得

解决方案

  User.sum（：score_weight，：包括=＆GT; {：审计=＆GT;：audit_activity}：组=＆GT;'users.id）

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, "1user@a.com");
INSERT INTO users(id, email)
VALUES(2, "2user@b.com");
INSERT INTO users(id, email)
VALUES(3, "3user@c.com");

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

解决方案 

User.sum( :score_weight, :include => {:audits => :audit_activity}, :group => 'users.id' )

这篇关于Rails的ActiveRecord的友好code，从一个复杂的联接，点心，和组查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
高性能数据库开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆