CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Chris Odeon
Chris Odeon

Reputation: 33

How to use SQL using Active Record

I am trying to optimise the performance of a query in Active Record. Previously I would do two SQL queries and it should be possible to do it in one.

These are the tables that I am running the queries on:

# Table name: notifications
#
#  id         :integer          not null, primary key
#  content    :text(65535)
#  position   :integer

# Table name: dismissed_notifications
#
#  id              :integer          not null, primary key
#  notification_id :integer
#  user_id         :integer

This is the existing query:

where.not(id: user.dismissed_notifications.pluck(:id))

which produces:

SELECT `dismissed_notifications`.`id` FROM `dismissed_notifications` WHERE `dismissed_notifications`.`user_id` = 655

SELECT  `notifications`.* FROM `notifications` WHERE (`notifications`.`id` != 1)

This is the SQL I would like to get, which returns the same records:

select *
from notifications n
where not exists(
    select 1
    from dismissed_notifications dn
    where dn.id = n.id
      and dn.user_id = 655)

Upvotes: 3

Views: 113

Answers (1)

Sampat Badhe
Sampat Badhe

Reputation: 9075

You can write not exists Query like below 

where('NOT EXISTS (' + user.dismissed_notifications.where('dismissed_notifications.id = notifications.id').to_sql + ')')

OR

There is also another way to reduce the number of queries is use select instead of pluck, it will create sub-query instead pulling records from database. Rails ActiveRecord Subqueries

where.not(id: user.dismissed_notifications.select(:id))

Which will generate below SQL query 

SELECT  `notifications`.* 
  FROM `notifications` 
  WHERE (
    `notifications`.`id` NOT IN 
      (SELECT `dismissed_notifications`.`id` 
        FROM `dismissed_notifications` 
        WHERE `dismissed_notifications`.`user_id` = 655
      )
  )

Upvotes: 5

Related Questions