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>

user229044 · Accepted Answer · 2014-06-22T16:35:09.137

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.

edited Jun 22 '14 at 16:35

answered Jun 22 '14 at 14:59

user229044

232,980
40
330
338

@meager, thanks for the quick answer. There will always be a `trackable_id` stored in each `activity` though...even if the `trackable` object is nil. I need to check if that associated object is `nil`. – Justin Jun 22 '14 at 15:06
Still curious about the answer to this, as I can use it to apply to other needs + others might find it useful...but I think I may have solved my problem with [this answer](http://stackoverflow.com/a/23071663/2456549). Just a different way of solving it. – Justin Jun 22 '14 at 15:17
@Justin See my update. The answer to this is to fix the underlying problem with having `trackable_id`s pointin to records that no longer exist. – user229044 Jun 22 '14 at 16:35
Yep I agree. I'm working on fixing this issue today. Thanks for the time spent on this. – Justin Jun 22 '14 at 16:38

score 0 · Answer 2 · answered Jun 22 '14 at 16:39

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

Activerecord query with joins

2 Answers2