Forum: Ruby on Rails Question about using DISTINCT in ActiveRecord find method.

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
3320715fa6264e6309503d5bd089fad2?d=identicon&s=25 Mufaddal Khumri (mkhumri)
on 2006-01-12 20:38
(Received via mailing list)
Hi,

Am relatively new to the RoR world. My question is quite simple.

I have a query like such:

shipmentNumbers = Order.find_by_sql(["select distinct s.id from orders
o, line_items li, shipment_lines sl, shipments s where o.number=? and
o.id = li.order_id and li.id = sl.line_item_id and sl.shipment_id =
s.id", number])

The above query works fine. I wanted to do the same query using the
query below:

shipmentNumbers = Order.find(:all,
:conditions => ["number = ?", number],
:joins => "as o join line_items as li on o.id = li.order_id join
shipment_lines as sl on sl.line_item_id = li.id join
shipments as s on sl.shipment_id = s.id")

In the above query I need to add the "distinct s.id" equivalent for
getting the correct results. Does anybody have an idea as to how I would
use the DISTINCT clause in the find(..) method?

Thanks,

**
69b2ef4bce76b5b27c94e898976dc6d8?d=identicon&s=25 matthew clark (Guest)
on 2006-01-12 20:54
(Received via mailing list)
It looks to me like you still thinking about your data in a relational
way,
instead of ActiveRecord's Object mindset.  The book Agile Rails has an
example almost exactly like what you are trying to do.  I suggest
reading
there.

Rails makes it easy to do things the right way, using ActiveRecord and
objects.  It makes other ways possible, but much much harder.

matt
3320715fa6264e6309503d5bd089fad2?d=identicon&s=25 Mufaddal Khumri (mkhumri)
on 2006-01-12 21:00
(Received via mailing list)
I will have a look into that. In the meanwhile, could someone post the
ActiveRecord way of doing this query?

Thanks,
9d1f5d2d9de70bd9a934f557dc95a406?d=identicon&s=25 Daniel ----- (liquid)
on 2006-01-12 22:28
(Received via mailing list)
Hi Mufaddal,

Assuming that you have the Order object i think you should be able to
use
the following

@shipments = @order.line_items.shipment_lines.shipments

I think this should work, haven't tested it tho.

Cheers
3320715fa6264e6309503d5bd089fad2?d=identicon&s=25 Mufaddal Khumri (mkhumri)
on 2006-01-12 23:11
(Received via mailing list)
Hi Liquid,

Here are the relationships between my table,

order has_many line_items. line_item belongs_to order
line_item has_many shipment_lines. shipment_line belongs_to line_item
shipment has_many shipment_lines. shipment_line belongs_to shipment

so if i do what you are suggesting, won't I get duplicate shipments for
some orders?

I could go though the @shipments and remove the duplicate shipments.
(Please let me know if I am missing something here).

Thanks,
9d1f5d2d9de70bd9a934f557dc95a406?d=identicon&s=25 Daniel ----- (liquid)
on 2006-01-12 23:26
(Received via mailing list)
Hi Mufaddal,

I misunderstood your associations.  I see your issue.

you could use the following then

@shipments = @order.line_items.shipment_lines.shipments.uniq!

This will collect the array, and then remove duplicate entries.

I hope ;)

Cheers
This topic is locked and can not be replied to.