1

I have a model Indent. On which I am using STI. An indent can be of two types Sale and Purchase. In purchase class I am using an optional has_one association. 

class Purchase < Indent
    has_one :sale , :class_name => 'Sale', :foreign_key => 'linked_indent_id'
    # Make it work.  
    scope :unsold, lambda {includes(:sale).where('id not in (select distinct linked_indent_id from indents)')}
end

class Sale < Indent
   belongs_to :purchase , :class_name => 'Purchase', :foreign_key => 'linked_indent_id'
end

I just need a scope on Purchase class using which i can find all the purchases which are not having a Sale associated to them.

I am using Rails 3.2 and Postgres as database.

Update:

The query which is getting generated is as follows.

 SELECT "indents".* FROM "indents" WHERE "indents"."type" IN ('Purchase') AND 
 (id not in (select distinct linked_indent_id from indents)) ORDER BY indent_date DESC

Following part of the query is working fine.

=# select distinct linked_indent_id from indents;

 linked_indent_id 
 ------------------

        15013
        15019
       (3 rows)

And this is also working fine.

SELECT "indents".* FROM "indents" WHERE "indents"."type" IN ('Purchase') AND
(id not in (15013, 15019)) ORDER BY indent_date DESC

What am i missing in coupling these two parts of query?

user229044
  • 222,134
  • 40
  • 319
  • 330
Bot
  • 1,001
  • 2
  • 13
  • 32

2 Answers2

1

I first got confused by the terms purchase and sale. But your update I believe helped me understand the problem more.

So what I understood is anything unsold is purchases minus sales. Following should give you that list:

scope :unsold, lambda {includes(:sale).select { |p| !p.sale.present? } }

Update:

A brief explanation of what's happening here:

The scope does not really do all the work in the database. It does a SQL select of all the purchases including the joined sale first. This gives you all the records in your purchases table. Then this scope falls back to Ruby Array on the select method. The method returns all the purchases p without a sale which is done by negating purchases with sale.

Hope this clears up on what the scope is doing a bit.

Update 2:

A scope that's chainable!

scope :unsold, lambda { where('id not in (?)', Sale.pluck(:linked_indent_id)) }

In this scope the ids of Purchases that are not in Sale's linked_indent_id are selected.

Bot
  • 1,001
  • 2
  • 13
  • 32
vee
  • 37,584
  • 7
  • 71
  • 74
  • Wow. Worked in a single go. – Bot Dec 21 '13 at 08:38
  • Thanks a lot. Also can you explain what have you done there a bit so that I and others can understand a bit better. – Bot Dec 21 '13 at 08:39
  • Thanks again. I thought about filtering purchases in rails but couldn't have done it in such a DRY way. Also I am going to use this scope to search while auto-completing a select menu. Bit worried if it is going to hit performance. – Bot Dec 21 '13 at 08:54
  • @Bot, please see my update. You're right the `Array#select` is not going to make it chainable which is one of the best features of scopes in Rails. – vee Dec 21 '13 at 10:03
  • Thanks a ton. Here is what is needed in this situation which i extracted out of your answer. scope :unsold, where('id not in (?)', Sale.pluck(:linked_indent_id)) – Bot Dec 21 '13 at 10:44
  • For the 'Purchase' rows in the table the linked_indent_id will always be null, as only 'Sale' rows have the id of 'Purchase' indent as FK. So the 'or' condition shouldn't be there. – Bot Dec 21 '13 at 10:49
  • @Bot, Glad to hear you got it working finally, and thanks for the edit. – vee Dec 21 '13 at 10:55
  • That's the least I could have done to save you more trouble of editing. Thanks once more. – Bot Dec 21 '13 at 11:02
  • This scope again has a bug. :) The scope loads when the class loads. So the "Sale.pluck(:linked_indent_id)" part doesn't get changed even after new purchases are linked. The query always contains "id not in (15013,15019)", despite changes. – Bot Dec 21 '13 at 11:29
0

Rails-ey database-centric way to do this:

scope :sold, -> { joins(:sale) }
scope :unsold, -> { includes(:sale).where(sales: {linked_indent_id: nil}) }

(Note that you have to use the table name in the where clause, not the relation name. That is 'sales', not 'sale'.)

Tom Wilson
  • 770
  • 9
  • 25