skip to Main Content

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??

2

Answers


  1. 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 
    
    Login or Signup to reply.
  2. 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,
      # ...
    )
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search