Rails 3: Polymorphic conditioned query

Question

I've got the following models:

class Notification < ActiveRecord::Base

belongs_to :notificatable, polymorphic: true

end

class BounceEmailNotification < ActiveRecord::Bas

has_one :notification, :as => :notificatable, :dependent => :destroy

end

class UserNotifierEmailNotification < ActiveRecord::Base

has_one :notification, :as => :notificatable, :dependent => :destroy

end

As you can see, a notification can be of type "bounce email notification" or "user notifier email notification". The BounceEmailNotification model has a event string attribute. What if I want retrieve all user notifier email notifications and all bounce email notifications which have a specific event value, ordered by created_at?

Something like this (using squeel):

(Notification.joins{ notificatable(BounceEmailNotification) }.where('bounce_email_notifications.event' => 'error') + Notification.joins { notificatable(UserNotifierEmailNotification) }).sort_by { |n| n.created_at }

will work, but I don't want to use Ruby to order the notifications. What can I do? Thanks

Answer 1

Try this.

Notification.joins('left outer join bounce_email_notifications on notifications.notificatable_id = bounce_email_notifications.id and notificatable_type = "BounceEmailNotification"').
where("bounce_email_notifications.event = ? or notificatable_type = ?",'error',UserNotifierEmailNotification.to_s).
order('notifications.created_at')

This is using active record 3.2 but I guess it should work in rails 3.

I have not used squeel cant comment much on how to use it with squeel but this can give you a brief idea.

In squeel I came up with something like this ( NOT TESTED) created by using squeel docs

Notification.notificatable{notable(BounceEmailNotification).outer}. where{
         { ('bounce_email_notifications.event'=>'error') | (:notificatable_type=>UserNotifierEmailNotification.to_s) }

Conceptual steps

1. left join on the bounce_email_notifications to get all bounce_email_notifications and and non bounce_email_notifications notification in result

2. check if the bounce_email_notifications.event = event
   or notificatable_type = 'UserNotifierEmailNotification' for all UserNotifierEmailNotification records

3. sort the records by notifications.created at

Hope this helps.