Ruby on Rails 查找没有记录的地方

发布于 2024-11-29 13:29:11 字数 709 浏览 3 评论 0原文

我有以下关联：

Outlet => has_many :checkins
Checkin => belongs_to :outlet

每天，Outlet 需要获得 1 次签入。我可以通过以下方式获取今天签到的网点列表：

Checkin.where('DATE(created_at) = ?', Date.Today)

但是，我不确定如何获取今天尚未签到的网点列表。

因此，如果商店 A 今天创建了签到记录，但商店 B 没有签入，因此没有创建记录，则商店 B 将被返回.

如果我们这样说：

@checkin_outlet_name = Checkin.where('DATE(created_at) = ?', Date.Today).outlet.name

@outlet = Outlet.all

理论上我可以找到今天没有签到的所有奥特莱斯，因为 @outlet 中而不是 @checkin_outlet_name 中的所有记录都将是我正在寻找的人。

但是，我不知道如何在 Rails 中实现这一点。

任何帮助将不胜感激。

原文

I have the following associations:

Outlet => has_many :checkins
Checkin => belongs_to :outlet

Every day, and Outlet needs to get 1 checkin.
I can get the list of outlets with a checkin made today with this:

Checkin.where('DATE(created_at) = ?', Date.Today)

However, I am not sure about how I could get a list of outlets that have not had a checkin associated with them today.

So if Outlet A had a check in record created today, but Outlet B had not had a check in, and hence no record made, Outlet B would be returned.

If we say that:

@checkin_outlet_name = Checkin.where('DATE(created_at) = ?', Date.Today).outlet.name

@outlet = Outlet.all

I would theoretically be able to find all the Outlets without checkins made today, because all the records that are in @outlet and NOT on @checkin_outlet_name would be the ones I am looking for.

However, I have no idea how to achieve this in Rails.

Any help would be greatly appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

遗忘曾经 2024-12-06 13:29:11

您可能想要类似 Outlet.joins(:checkins).where("DATE(checkins.created_at) <> ?", Date.today)

回复收藏 0 原文

初相遇 2024-12-06 13:29:11

如果您想在不编写复杂查询的情况下执行此操作，那么

class Outlet
  scope :checked_in_today, { joins(:checkins).where("DATE(checkins.created_at) = ?", Date.today) }
end

# remove items from Outlet.all which have been checked in today (Array - Array)
@not_checked_in_today = Outlet.all - Outlet.checked_in_today

在数据库中执行此操作的更好方法

class Outlet
  scope :not_checked_in_today, lambda {
    joins("LEFT OUTER JOIN (SELECT * FROM checkins WHERE created_at = #{Date.today.to_s(:db)}) c ON outltes.id = c.outlet_id"). #only select checkins created today
    where("c.outlet_id IS NULL"). # only keep outlets not checked in today
    select("DISTINCT outlets.*") # remove duplicates 
  }
end

@not_checked_in_today = Outlet.not_checked_in_today

If you want to do it without writing complex queries,

class Outlet
  scope :checked_in_today, { joins(:checkins).where("DATE(checkins.created_at) = ?", Date.today) }
end

# remove items from Outlet.all which have been checked in today (Array - Array)
@not_checked_in_today = Outlet.all - Outlet.checked_in_today

Better way to do it in DB

class Outlet
  scope :not_checked_in_today, lambda {
    joins("LEFT OUTER JOIN (SELECT * FROM checkins WHERE created_at = #{Date.today.to_s(:db)}) c ON outltes.id = c.outlet_id"). #only select checkins created today
    where("c.outlet_id IS NULL"). # only keep outlets not checked in today
    select("DISTINCT outlets.*") # remove duplicates 
  }
end

@not_checked_in_today = Outlet.not_checked_in_today

回复收藏 0 原文

~没有更多了~