0

So, Members have many subscriptions. A member is canceled if the last subscription canceled_at is not nil. But I can't seem to get the query to work right, to find all members with presently canceled subscriptions.

I'm doing this in the member model

  scope :canceled, includes(:subscriptions).
    where('subscriptions.canceled_at IS NOT NULL')

but it returns for users who have previously canceled subscriptions too. I need to use limit or something like that, I have the relation defined as

has_many :subscriptions, :order => "subscriptions.created_at DESC", :dependent => :destroy

so i can just work offf the .first, but not sure how to apply that logic to the class scope like that.

  • still not entirely clear, you want to find users whose subscriptions are presently canceled ? Whats the difference between presently cancelled subscriptions and the past ones? Is their a date range? – Kyle C Aug 22 '12 at 23:50
  • users who's latest subscription record status is canceled, which is denoted by canceled_at being null or with a datetime obj –  Aug 23 '12 at 00:04

1 Answers1

0

How about adding another association 

 Class Member < ActiveRecord::Base
   has_one :last_subscription, :class_name => "Subscription", :foreign_key => "member_id", :order => "created_at desc"

Then try 

 scope :canceled, includes(:last_subscription).where('subscriptions.canceled_at IS NOT NULL')
Kyle C
  • 4,077
  • 2
  • 31
  • 34
  • that comes out to SELECT `members`.* FROM `members` ORDER BY members.last_name ASC, members.first_name ASC LIMIT 1 so it only returns 1 member –  Aug 23 '12 at 00:53
  • try calling it with Member.includes(:last_subscripton).all. That query executed doesnt make sense, not sure why it isnt calling subscriptions – Kyle C Aug 23 '12 at 00:59
  • ok, did that, then ran: Member.canceled.all.each do |m|; p m.full_name unless m.subscriptions.first.canceled?; end and i returned a few members, which i shouldn't in theory –  Aug 23 '12 at 01:14
  • I am testing it out on my machine and I get a different query, what does m = member.first m.last_subscription give you? – Kyle C Aug 23 '12 at 01:19
  • I dont think its possible to eager load the most recent objects, you will have to do raw sql, check this past answer http://stackoverflow.com/questions/2111384/sql-join-selecting-the-last-records-in-a-one-to-many-relationship – Kyle C Aug 23 '12 at 01:38
  • yes, it returns only one subscription which is most recent, which could be a cancled or active subscription. checking out link now –  Aug 23 '12 at 01:42