Rails - check for non-matching records

Question

Rails - check for non-matching records

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

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

The schema 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 views 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

Of course, I have the Notification and Visualization templates in the application.

What query could I do to select all notifications for a user whose view record does not exist in the view 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

asked by anonymous 18.06.2018 / 20:41

2 answers

I can not start an array with foreach Error finding the center of an OpenCV Python object

score 1 · Answer 1

To get a bit more "Rails", performative and avoiding any type of SQL injection, I'll give you an alternative to the answer

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

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

The problem with includes is that it will force the eager load , loading for each notification their respective views. With left_outer_joins , you avoid all loading done, just taking what is really needed.

Another tip: To respect the DRY , you can put in the model Notification , a scope .

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

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

Notification.only_unseen

score 0 · Answer 2

Read Active Record Query Interface

link

Associations

link

Rails has a way of searching that you do not need to use SQL. Only when you really need it.

id_colaborador = 123

Notification.includes(:visualizations)
 .where("visualizations.collaborator_id = ? and visualizations.visualized = false", id_colaborador)