Activerecord query with joins

Question

So far, I have this query for the gem PublicActivity. One (that I'm aware of) problem with it, is if the activity.trackable is nil, it will still include it in the query, thus forcing me in the view to run the conditional, if activity.trackable, which basically helps if the trackable object has been deleted, so you don't try to access a nil object.

PublicActivity::Activity.
  includes(:trackable, :owner).
  order('created_at DESC').
  where(recipient_id: current_user, kind: "Notification", read: false).
  where('owner_id not in (?)', current_user).
  size

I want to include a where query to only query the activities where the activity.trackable IS NOT NULL. In the view, with: activity.trackable.nil?, I can see if the trackable object is nil, but I can't figure out how to do this in the query, even after searching S.O. and rails api/docs. Any guidance would be appreciated.

EDIT

Even after you destroy the trackable object, the trackable_id will still be stored on the activity. Below is an example of an activity after I destroy the trackable object.

#<PublicActivity::Activity id: 389, trackable_id: 865, trackable_type: "Vote", owner_id: nil, owner_type: nil, key: "vote.update", parameters: {}, recipient_id: nil, recipient_type: nil, created_at: "2014-06-22 14:33:37", updated_at: "2014-06-22 14:33:37", kind: nil, read: false>

Answer 1

Add a where('activities.trackable_id is not null') before the .size. If your tables are namespaced, you might need to use 'public_activity_activities.trackable_id is not null' instead.

You have larger problems if the trackable_id points to a record that no longer exists. The solution is to fix that bug, not to work around it. Either add a foreign key constraint that sets the trackable_id to null when the associated record is deleted, or fix your Rails association to set the field to null when the associated object is destroyed with a :dependent option.

The resulting association would look something like this:

class Trackable < ActiveRecord::Base
  has_one :activity, dependent: :nullify
end

Don't ignore this, and don't design a system to work this way. It's an extremely serious data-inconsistency to have dangling foreign keys; you're effectively defeating the entire purpose of a relational database.

Alternatively, don't actually destroy your objects. Use a "soft-delete" strategy where you simply toggle an active field to false. The important thing is that you should never design a system where it's possible to have foreign keys pointed to deleted records.

Answer 2

As I understood you want to receive all activity records with trackable, but not where only tackable_id present. So you can simply joins(:trackable) this will query INNER JOIN and work the way you want. This query will joins trackables and activities tables only where it possible(where trackable_id present and this id present in trackables table).

PublicActivity::Activity.
   includes(:trackable, :owner).
   joins(:trackable).
   order('created_at DESC').
   where(recipient_id: current_user, kind: "Notification", read: false).
   where('owner_id not in (?)', current_user).
   size