Rails 5 complex query with multiple sub queries

Question

I'm on rails 5 using postgres and I have a users table and a reports table. Users have many reports, and these reports need to be created every day. I want to fetch all of the users that are not archived, that have not completed a report today, and show yesterdays report notes if available.

Here are the models:

Users Model

# == Schema Information
#
# Table name: users
#
#  id         :bigint(8)        not null, primary key
#  name       :string           not null
#  created_at :datetime         not null
#  updated_at :datetime         not null
#  archived   :boolean          default(FALSE)
#

class User < ApplicationRecord
  has_many :reports
end

Reports Model

# == Schema Information
#
# Table name: reports
#
#  id         :bigint(8)        not null, primary key
#  notes      :text
#  created_at :datetime         not null
#  updated_at :datetime         not null
#  user_id    :bigint(8)
#

class Report < ApplicationRecord
  belongd_to :user
end

Here is an example of what I want from this query:

Users Table

------------------------------------------------------------------------------------
| id |  name  | archived |         created_at         |         updated_at         |
------------------------------------------------------------------------------------
| 1  |  Jonn  |  false   | 2018-05-11 00:01:36.124999 | 2018-05-11 00:01:36.124999 |
------------------------------------------------------------------------------------
| 2  |  Sam   |  false   | 2018-05-11 00:01:36.124999 | 2018-05-11 00:01:36.124999 |
------------------------------------------------------------------------------------
| 3  | Ashley |  true    | 2018-05-11 00:01:36.124999 | 2018-05-11 00:01:36.124999 |
------------------------------------------------------------------------------------

Reports Table (Imagine this report was yesterdays)

--------------------------------------------------------------------------------------
| id |  user_id  |  notes  |         created_at         |         updated_at         |
--------------------------------------------------------------------------------------
| 1  |     1     | Nothing | 2018-06-13 16:32:05.139284 | 2018-06-13 16:32:05.139284 |
--------------------------------------------------------------------------------------

Desire output:

-------------------------------------------------------------------------------------------------------
| id |  name  | archived |         created_at         |         updated_at         | yesterdays_notes |
-------------------------------------------------------------------------------------------------------
| 1  |  Jonn  |  false   | 2018-05-11 00:01:36.124999 | 2018-05-11 00:01:36.124999 |     Nothing      |
-------------------------------------------------------------------------------------------------------
| 2  |  Sam   |  false   | 2018-05-11 00:01:36.124999 | 2018-05-11 00:01:36.124999 |       NULL       |
-------------------------------------------------------------------------------------------------------

I was able to get the desired query results writing raw SQL, but I have run into a lot of issues trying to convert it to an active record query. Would this be an appropriate scenario to use the scenic gem?

Here is the raw SQL query:

SELECT u.*, (
  SELECT notes AS yesterdays_notes
  FROM reports AS r
  WHERE r.created_at >= '2018-06-13 04:00:00'
  AND r.created_at <= '2018-06-14 03:59:59.999999'
  AND r.user_id = u.id
)
FROM users AS u
WHERE u.archived = FALSE
AND u.id NOT IN (
  SELECT rr.user_id
  FROM reports rr
  WHERE rr.created_at >= '2018-06-14 04:00:00'
);

Answer 1

Here is how I would do it:

First, select all active users with most recent reports created yesterday and assign it to a var:

users = User.where(archived: false).joins(:reports)
.where.not('DATE(reports.created_at) IN (?)', [Date.today]) 
.where('DATE(reports.created_at) IN (?)', [Date.yesterday])
.select('users.id', 'users.name', 'notes')

now the users var will have the attrs listed in .select available so you can call users.map(&:notes) to see the list of nodes, including nil / null notes.

another trick that may come in handy is the ability to alias the attrs your listed in .select. For example, if you want to store users.id as id, you can do so with

...
.select('users.id as id', 'users.name', 'reports.notes')

you can call users.map(&:attributes) to see what these final structs would look like

more info on available Active Record querying can be found here

Answer 2

users = User.joins(:reports).where("(reports.created_at < ? OR reports.created_at BETWEEN ? AND ?) AND (users.archived = ?)", Date.today.beginning_of_day, Date.yesterday.beginning_of_day, Date.yesterday.end_of_day, false).select("users.id, users.name, reports.notes").uniq

users will return as #<ActiveRecord::Relation [....]

Possibly joins returns duplicate records so use uniq

Filter reports

reports.created_at < Date.today.beginning_of_day OR yesterday.beginning_of_day > reports.created_at < Yesterday.end_of_day

which is required reports as "not completed a report today, and show yesterdays report notes if available"

And users.archived = false

Answer 3

I was able to get the desired results from the 2 answers posted here, however, after doing some more research I think using a database view using the scenic gem is the appropriate approach here so I am going to move forward with that.

Thank you for the input! If you want to see some of the reasoning behind my decision this stackoverflow post summarizes it nicely: https://stackoverflow.com/a/4378166/2909095

Here is what I ended up with using the scenic gem. I changed the actual query a little bit to fit my needs better but it resolves this answer:

Model

# == Schema Information
#
# Table name: missing_reports
#
#  id                  :bigint(8)
#  name                :string
#  created_at          :datetime
#  updated_at          :datetime
#  previous_notes      :text
#  previous_notes_date :datetime

class MissingReport < ApplicationRecord
end

Database View

SELECT u.*, rs.notes AS previous_notes, rs.created_at AS previous_notes_date
FROM users AS u
LEFT JOIN (
  SELECT r.*
  FROM reports AS r
  WHERE r.created_at < TIMESTAMP 'today'
  ORDER BY created_at DESC
  LIMIT 1
) rs ON rs.user_id = u.id
WHERE u.archived = FALSE
AND u.id NOT IN (
  SELECT rr.user_id
  FROM standups rr
  WHERE rr.created_at >= TIMESTAMP 'today'
);

Usage

def index
  @missing_reports = MissingReport.all
end