Implementing a filter for a SQL query in a Rails app - Postgresql

ViniciusLibero
December 14, 2022
326 views
0 votes
2 Answers

This is a rails app that uses Vue as front-end. There are 2 similar models/tables, named user_notifications and seller_notifications, that have the same columns.

In the user_notifications_controller there are 2 distinct actions, one that shows only user_notifications (for the My Notifications page) and another that shows all notifications (user+seller) [for the Notifications page].

I was not able to have the query results show what I need using ActiveRecords helpers, so I decided to make it directly as an SQL query. This is the original query:

(
  SELECT sn.id, sellers.name, sn.seller_id AS member_id, sn.title, sn.body, sn.created_at, sn.is_new, sellers.platform_id, sn.pending_action_id, 'seller_notifications' AS type
  FROM seller_notifications sn
  INNER JOIN sellers ON sellers.id = sn.seller_id
  WHERE sn.platform_id = :platform_id
  ORDER BY sn.created_at DESC
) UNION ALL (
  SELECT un.id, users.name, un.user_id AS member_id, un.title, un.body, un.created_at, un.is_new, users.platform_id, un.pending_action_id, 'user_notifications' AS type
  FROM user_notifications un
  INNER JOIN users ON users.id = un.user_id
  WHERE un.platform_id = :platform_id
  ORDER BY un.created_at DESC
)
LIMIT :limit OFFSET :offset

The issue is that I need to include a filter, which I have done and it works fine for the most part except one: the type (where table’s name is), because I need to be able to filter for what kind of notification it is.

Below is the complete code, including query sanitizer:

query = "(
SELECT sn.id, sellers.name, sn.seller_id AS member_id, sn.title, sn.body, sn.created_at, sn.is_new, sellers.platform_id, sn.pending_action_id, 'seller_notifications' AS type FROM seller_notifications sn
INNER JOIN sellers ON sellers.id = sn.seller_id
WHERE sn.platform_id = :platform_id #{params[:is_new].nil? ? nil : ' AND sn.is_new = :is_new '}
#{params[:member_id].nil? ? nil : ' AND sn.seller_id = :member_id '}
#{params[:title].nil? ? nil : ' AND sn.title = :title '}
ORDER BY sn.created_at DESC)
UNION ALL
(SELECT un.id, users.name, un.user_id AS member_id, un.title, un.body, un.created_at, un.is_new, users.platform_id, un.pending_action_id, 'user_notifications' AS type FROM user_notifications un
INNER JOIN users ON users.id = un.user_id
WHERE un.platform_id = :platform_id #{params[:is_new].nil? ? nil : ' AND un.is_new = :is_new '}
#{params[:member_id].nil? ? nil : ' AND un.user_id = :member_id '}
#{params[:title].nil? ? nil : ' AND un.title = :title '}
ORDER BY un.created_at DESC)
LIMIT :limit OFFSET :offset"

sanitizer_ary = [
  query,
  platform_id: @user.platform_id,
  limit: limit,
  offset: offset,
  member_id: params[:member_id],
  title:  params[:title],   name: params[:name],
  type: params[:type],
  is_new: params[:is_new]
  ]

sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, sanitizer_ary)
result = ActiveRecord::Base.connection.execute(sanitized_query)
result.map { |notif| notif }

When I originally tried to query it as WHERE type = :type it raises an PG::Error that I believe it’s because of the lack of table prefix, e.g. un.type instead of just type, but there is no column named type. I have also tried placing the WHERE and ORDER BY statements outside of the parenthesis at the end, just before LIMIT, but it also raises PostgreSQL statement error.
So far, none of what I tried has worked.

ERRORS
This is the original error:

PG::UndefinedColumn: ERROR:  column "type" does not exist
LINE 3:       WHERE sn.platform_id = 1 AND type = NULL 
                                           ^
 excluded from capture: DSN not set
  
ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR:  column "type" does not exist
LINE 3:       WHERE sn.platform_id = 1 AND type = NULL 
                                           ^
):

And this is when I tried to use the WHERE statement outside of the parenthesis:

PG::SyntaxError: ERROR:  syntax error at or near "WHERE"
LINE 14:       WHERE type = NULL
               ^
 excluded from capture: DSN not set
  
ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at or near "WHERE"
LINE 14:       WHERE type = NULL
               ^
):

EXPECTATION
What I need is to be able to include a line, similar to the one below, just after the params[:title] line:

#{params[:type].nil? ? nil : ' AND type = :type'}

Does anybody know how to solve this, i.e.: filter a query result that includes the name of the table by the name of the table itself, which is not included in the table’s columns??

Answers

If you express your relations cleanly using ActiveRecord, then the queries will express themselves..

class SellerNotification < ApplicationRecord                                                                                                                                                                                      
  belongs_to :platform
  belongs_to :seller, class_name: "Seller", inverse_of: :notifications
                                
  scope :plaftorms, ->(*p) {                                                    
    p = p.flatten.compact.uniq                                                  
                                                                                
    p.any? where(plaform_id: p) : none                                          
  }                                                                             
                                                                                
  scope :title, ->(title) {                                                     
    where(title: title)                                                         
  }                                                                             
                                                                                
  scope :members, ->(*members) {                                                
    members = members.flatten.compact.uniq                                      
                                                                                
    members.any? ? where(user_id: members) : none                               
  }                                                                             
end                                                                             
                                                                                
class Seller < ApplicationRecord                                                               
  has_many :notifications, class_name: "SellerNotification", dependent: :destroy
    
  scope :platforms, ->(*p) {
    joins(:nofifications)
      .merge(SellerNotification.platforms(*p))
  }
    
 end

I would consider creating a materialized view of the union of the two tables.

CREATE MATERIALIZED VIEW unified_notifications AS
   SELECT 
      sn.id, 
      sellers.name, 
      sn.seller_id AS member_id, 
      sn.title, 
      sn.body, 
      sn.created_at, 
      sn.is_new, 
      sellers.platform_id, 
      sn.pending_action_id, 
      'seller_notifications' AS type 
   FROM seller_notifications sn
   INNER JOIN sellers ON sellers.id = sn.seller_id
   UNION ALL
   SELECT 
     un.id, 
     users.name, 
     un.user_id AS member_id, 
     un.title, 
     un.body, 
     un.created_at, 
     un.is_new, 
     users.platform_id, 
     un.pending_action_id, 
     'user_notifications' AS type 
   FROM user_notifications un
   INNER JOIN users ON users.id = un.user_id

The scenic gem can be used to create views with Rails migrations. Note that you need to switch to SQL schema dumps.

Since the view behaves like a table (at least from the perspective of ActiveRecord) you can just create an ActiveRecord model and query it like it was a single table:

class UnifiedNotification < ApplicationRecord
  def readonly?
    true
   end
end

UnifiedNotification.where(
  platform_id: @user.platform_id,
  # ...
)

Please signup or login to give your own answer.

Click here to cancel reply.

Implementing a filter for a SQL query in a Rails app – Postgresql

Answers