ActiveRecord：获取今天应被禁止的所有用户

问题描述

我有两个模型：

  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'）
  

更新

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

  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

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：获取今天应被禁止的所有用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！