Active Record查询以查找在一个日期范围内有孩子并且在该日期范围之前有孩子的父母 [英] Active Record query to find parents with child within a date range and child prior to that date range

问题描述

我需要一个活动记录查询，该查询将返回在一个日期范围内创建的子项和在该日期范围之前创建的子项的父项计数。

I need an active record query that returns the count of Parents with a Child created within a date range AND a Child created prior to the date range.

我目前有以下两个查询：
此查询返回在日期范围内创建的有孩子的父母人数

I currently have the following two queries: This query returns the count of Parents with a Child created within the date range

Parent.joins(:children).where("children.created_at BETWEEN ? AND ?", start, end).distinct.count(parent_id)

此查询计算在日期范围之前创建的有孩子的父母

This query counts the Parents with a Child created before the date range

Parent.joins(:children).where("children.created_at < ?", start).distinct.count(:parent_id)

我需要找到在该范围内创建了一个孩子并且在该范围之前创建了一个孩子的父母的数量。

I need to find the count of Parents that have a Child created within the range AND a Child created before the range.

如何获得最佳方法这样的结果？

How is the best way to get this result?

推荐答案

您可以按照以下方式进行操作，

You can do this in following ways,

简单的方法，获取p的ID不在第一个查询中，然后将过滤器应用于第二个查询中的父ID，

Simple way, get the ids for parent from the first query and apply the filter on parent id in second query,

 q1_pids = Parent.joins(:children)
                 .where("children.created_at BETWEEN ? AND ?", start, end)
                 .ids
 count = Parent.joins(:children)
               .where("children.created_at < ?", start)
               .where(id: q1_pids)
               .count("parents.id")

使用INTERSECT

Using INTERSECT

q1 = Parent.joins(:children)
           .where("children.created_at BETWEEN ? AND ?", start, end)

q2 = Parent.joins(:children)
           .where("children.created_at < ?", start)

count = (q1 & q1).count

第二个解决方案仅使用数组交集，但是您可以自己准备SQL语句以触发单个SQL查询数据库。

Second solution just uses array intersection, however you can prepare the SQL statement yourself to fire a single SQL query to the database.

这篇关于Active Record查询以查找在一个日期范围内有孩子并且在该日期范围之前有孩子的父母的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！