ActiveRecord：获取今天应被禁止的所有用户 [英] ActiveRecord: Get all users who should be banned today

问题描述

我有两个模型：

  class User< ActiveRecord :: Base 
 has_many：ban_messages，从属：：destroy 
 end 
 
 class BanMessage< ActiveRecord :: Base 
 Emirates_to：user 
 end 
  

表 BanMessage 包含一个字段 t.datetime：ban_date ，用于存储应禁止用户的日期。 User 表包含字段 status ，其中包含状态值之一（活动，暂停，禁止）。当我向用户发送BanMessage时，他的状态字段从活动更改为已暂停。另外，我还可以激活用户，因此不会在’：ban_date’处禁止他，但不会销毁他的BanMessage。
因此，我需要创建查询以选择状态为已暂停的所有用户，这些用户的最新BanMessages记录中的 ban_date字段具有DateTime值，该值介于'DateTime.now之间。

解决方案<和'DateTime.now.end_of_day'。

解决方案

这就是我需要的：

  User.joins（：ban_messages）
 .where（' users.status =？'，：suspended）
 .where（'ban_messages.created_at =（从ban_messages中选择MAX（ban_messages.created_at）从ban_messages中选择ban_messages.user_id = users.id）'）
 .where（ 'ban_messages.ban_date BETWEEN？和？'，DateTime.now.beginning_of_day，DateTime.now.end_of_day）
 .group（'users.id'）
  

更新

MrYoshiji ：代码中的警告是，如果您错过了一天，那么第二天的
就不会看到应该在前一天被禁止的用户。您可能想让所有用户的ban_date比DateTime.now.end_of_day少
，在同一视图中或在另一视图中
。

所以最终查询可能是这样的

  User.joins（： ban_messages）
 .where（'users.status =？'，：suspended）
 .where（'ban_messages.created_at =（SELECT MAX（ban_messages.created_at）FROM ban_messages哪里ban_messages.user_id = users.id ）'）
 .where（'ban_messages.ban_date<？'，DateTime.now.end_of_day）
 .group（'users.id'）
  

I have two models:

class User < ActiveRecord::Base
  has_many :ban_messages, dependent: :destroy
end

class BanMessage < ActiveRecord::Base
  belongs_to :user
end

Table BanMessage contains a field t.datetime :ban_date that stores the date, when user should be banned. User table contains a field status, that contains one of the status values (active, suspended, banned). When I send BanMessage to User, his status field changes from 'active' to 'suspended'. Also I can activate user back, so he would not be banned at ':ban_date', but his BanMessage wouldn't be destroyed. So I need to create query for selecting all Users with status 'suspended', who have in their newest BanMessages records 'ban_date' field, with DateTime value, which is between 'DateTime.now.beginning_of_day' and 'DateTime.now.end_of_day'.

解决方案

Here's what I needed:

User.joins(:ban_messages)
  .where('users.status = ?', :suspended)
  .where('ban_messages.created_at = (SELECT MAX(ban_messages.created_at) FROM ban_messages WHERE ban_messages.user_id = users.id)')
  .where('ban_messages.ban_date BETWEEN ? and ?',  DateTime.now.beginning_of_day, DateTime.now.end_of_day)
  .group('users.id')

UPDATE

MrYoshiji: The caveat in your code is that if you miss one day, then the next day you won't see the people that should have been banned the day before. You might want to get all User where the ban_date is lesser than DateTime.now.end_of_day, either in the same view or another one.

So final query might be something like this

User.joins(:ban_messages)
  .where('users.status = ?', :suspended)
  .where('ban_messages.created_at = (SELECT MAX(ban_messages.created_at) FROM ban_messages WHERE ban_messages.user_id = users.id)')
  .where('ban_messages.ban_date < ?', DateTime.now.end_of_day)
  .group('users.id')

这篇关于ActiveRecord：获取今天应被禁止的所有用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！