gpt4 book ai didi

mysql - 连接三个表(2 级)并根据条件求和 - SQL Rails

转载 作者:行者123 更新时间:2023-11-29 08:16:37 26 4
gpt4 key购买 nike

我有以下架构:

User:
---
ID
---

Tasks:
-------------------
ID | classification
-------------------

Timesheets:
------------------------
ID | task_id | user_id
------------------------

TimesheetItem:
--------------------------------
ID | timesheet_id | hours | date
--------------------------------

协会:

class User 
has_many :timesheets
end

class Task
has_many :timesheets
end

class Timesheet
has_many :timesheet_items
belongs_to :user
belongs_to :task
end

class TimesheetItem
belongs_to :timesheet
end

分类可以是“可计费”或“不可计费”。现在我需要为每个用户找到可计费和不可计费时间的总和,如下所示:

-----------------------------------
|user_id | billable | nonbillable |
-----------------------------------

我在 Rails 中所做的是:

User.joins(:timesheets, :timesheets => :task, :timesheets => :timesheet_items)
.select("SUM(CASE
WHEN tasks.task_classification = 'Billable'
THEN timesheet_items.hours
ELSE
0
END) as billable,
SUM(CASE
WHEN tasks.task_classification = 'Non-Billable'
THEN timesheet_items.hours
ELSE 0
END) as nonbillable")

但是 MySQL 给出错误“tasks.classification”是未知列。查看正在生成的查询,这是可以理解的:

SELECT SUM(CASE WHEN tasks.classification = 'Billable' THEN hours ELSE 0 END) as billable, SUM(CASE WHEN tasks.classification = 'Non-Billable' THEN hours ELSE 0 END) as nonbillable FROM `users` INNER JOIN `timesheets` ON `timesheets`.`user_id` = `users`.`id` INNER JOIN `timesheet_items` ON `timesheet_items`.`timesheet_id` = `timesheets`.`id`

正如您所看到的,任务表没有被加入。

我该如何实现这一目标?谢谢。

编辑:

我继续进行了一个简单的 SQL 查询,该查询连接了任务表来获取结果,因为该数据仅在一个地方使用,并且很少使用。

但现在我需要按月对小时数进行分组,并找出每个用户记录的计费小时数和非计费小时数。例如。

结果:

-----------------------------------------------
user_id | month | BillableHrs | NonBillableHRS|
-----------------------------------------------

我尝试过group(user_id, MONTH(date))但是..结果很奇怪。我怎样才能获得此类信息?

顺便说一句,将连接更改为:

joins(:timesheets, :timesheets => [:task, :timesheet_items])

解决了未找到列的问题:)

我终于找到了这个解决方案。有什么优化的想法吗?

SELECT 
users.id as user_id,
users.name as user_name,
CONCAT(MONTHNAME(date)," ",YEAR(date)) as month,
SUM( CASE
WHEN tasks.task_classification = "Billable"
THEN hours
ELSE
0
END ) as blb_sum,
SUM( CASE
WHEN tasks.task_classification = "Non-Billable"
THEN hours
ELSE
0
END ) as nblb_sum
FROM `users`
INNER JOIN `timesheets` ON `timesheets`.`user_id` = `users`.`id`
INNER JOIN `timesheet_items` ON `timesheet_items`.`timesheet_id` = `timesheets`.`id`
INNER JOIN `tasks` ON `timesheets`.`task_id` = `tasks`.`id`
WHERE
timesheet_items.date >= '2013-11-1' AND
timesheet_items.date <= '2013-11-31'

最佳答案

由于如果将其表示为 activerecord 和 SQL 的混合体,那么这将是一些相当丑陋的代码,因此一种想法是将复杂查询定义为 View 并将其引用为模型。

做起来非常简单 - 如果这是用户级别的一组指标,则按照以下方式构建一个 View :

create view user_billing_metrics
as
select user.id user_id,
sum(case ... blah blah) billable_hours,
sum(case ... blah blah) unbillable_hours
from ...

然后创建一个只读模型...

class UserBillingMetric < ActiveRecord::Base

belongs_to :user, :inverse_of => :user_billing_metric

def read_only?
true
end

end

然后...

class User < ActiveRecord::Base

has_one :user_billing_metric, :inverse_of => :user

delegate :billable_hours , :to => :user_billing_metric
delegate :unbillable_hours, :to => :user_billing_metric

def read_only?
true
end
end

然后您可以:

u = User.find( ...)

u.billable_hours

...或...

u = User.find( ...)
hours= u.user_billing_metric

等等

可能在那里犯了一个愚蠢的打字错误。

它的一个很好的功能是您可以,例如:

users_to_fire = User.joins(:user_billing_metric).
where(:user_billing_metrics => {:billable_hours = 0})

再说一次,可能是愚蠢的拼写错误。

将其推送到数据库非常有效,比尝试通过 Rails 运行它要高效得多。一个好的查询优化器不会计算 View 中不需要的表达式,甚至不会执行逻辑上冗余的连接。

无论如何,只是将其发布以征求意见。我知道将业务逻辑放在数据库层并不符合每个人的口味,但它可以保持干燥,并且我在自己的应用程序中遇到了这样的情况,出于性能原因,这绝对是唯一的选择。

关于mysql - 连接三个表(2 级)并根据条件求和 - SQL Rails,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/20494082/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com