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??
CodePudding user response：
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,
# ...
)
CodePudding user response：
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