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.
Mufaddal K. (Guest)
on 2006-01-12 21: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,

**
matthew clark (Guest)
on 2006-01-12 21: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
Mufaddal K. (Guest)
on 2006-01-12 22: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,
Daniel -. (Guest)
on 2006-01-12 23: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
Mufaddal K. (Guest)
on 2006-01-13 00: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,
Daniel -. (Guest)
on 2006-01-13 00: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.