Rails - query for non-connecting records

Asked

Viewed 56 times

1

I have a Rails application and am creating a notification system, the problem is that I do not know how to query by notifications not viewed by a user in a practical way.

The scheme of the notification table is as follows:

create_table "notifications", force: :cascade do |t|t.integer  "performer_id"
    t.string   "content"
    t.integer  "kind"
    t.datetime "created_at",   null: false
    t.datetime "updated_at",   null: false
    t.index ["performer_id"], name: "index_notifications_on_performer_id"
end

The one in the view table is:

create_table "visualizations", force: :cascade do |t|
    t.integer "notification_id",                 null: false
    t.integer "collaborator_id",                 null: false
    t.boolean "visualized",      default: false
    t.index ["collaborator_id"], name: "index_visualizations_on_collaborator_id"
    t.index ["notification_id"], name: "index_visualizations_on_notification_id"
end

Sure, I got the models Notification and Visualization in the application.

What query could I make to select all notifications to a user from whom there is no view record in the views table?

I ask this because I do not have much experience in SQL to think about a query and I am also the only developer of the application.

sql query ruby-on-rails

2 answers

1

To leave a little more "Rails", performative and avoids any type of SQL Injection, I give you an alternative to reply by @Danilo.

user = User.first
user.notifications
  .left_outer_joins(:visualizations)
  .where(visualizations: { id: nil })

This will work for Rails 5, where the left_outer_joins was added. Today, it is the most performative and idiomatic way of doing.

The problem with the includes is going to force the one Eager load, by clicking for each notification, their respective views. With left_outer_joins, you avoid all the loading done, just taking what is really needed.

Another tip: to respect the DRY, can put on the model Notification, one Scope.

class Notification < ApplicationRecord
  scope :only_unseen, -> { left_outer_joins(:visualizations).where(visualizations: { id: nil }) }
end

So whenever you want notifications not viewed, you can do:

Notification.only_unseen

0

Browser other questions tagged sql query ruby-on-rails

You are not signed in. Login or sign up in order to post.