Mufaddal Khumri
2006-Jan-12 19:36 UTC
[Rails] Question about using DISTINCT in ActiveRecord find method.
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
2006-Jan-12 19:53 UTC
[Rails] Question about using DISTINCT in ActiveRecord find method.
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 On 1/12/06, Mufaddal Khumri <mkhumri@allegromedical.com> wrote:> > 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, > > ** > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060112/83e990d5/attachment.html
Mufaddal Khumri
2006-Jan-12 19:57 UTC
[Rails] Question about using DISTINCT in ActiveRecord find method.
I will have a look into that. In the meanwhile, could someone post the ActiveRecord way of doing this query? Thanks, matthew clark wrote:> 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 > > On 1/12/06, *Mufaddal Khumri* <mkhumri@allegromedical.com > <mailto:mkhumri@allegromedical.com>> wrote: > > 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 > <http://s.id> from orders > o, line_items li, shipment_lines sl, shipments s where o.number=? and > o.id <http://o.id> = li.order_id and li.id <http://li.id> > sl.line_item_id and sl.shipment_id > s.id <http://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 <http://o.id> > li.order_id join shipment_lines as sl on sl.line_item_id = li.id > <http://li.id> join > shipments as s on sl.shipment_id = s.id <http://s.id>") > > In the above query I need to add the "distinct s.id <http://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, > > ** > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org <mailto:Rails@lists.rubyonrails.org> > http://lists.rubyonrails.org/mailman/listinfo/rails > > >------------------------------------------------------------------------ > >_______________________________________________ >Rails mailing list >Rails@lists.rubyonrails.org >http://lists.rubyonrails.org/mailman/listinfo/rails > >
Liquid
2006-Jan-12 21:25 UTC
[Rails] Question about using DISTINCT in ActiveRecord find method.
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 On 1/13/06, Mufaddal Khumri <mkhumri@allegromedical.com> wrote:> > I will have a look into that. In the meanwhile, could someone post the > ActiveRecord way of doing this query? > > Thanks, > > matthew clark wrote: > > > 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 > > > > On 1/12/06, *Mufaddal Khumri* <mkhumri@allegromedical.com > > <mailto:mkhumri@allegromedical.com>> wrote: > > > > 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 > > <http://s.id> from orders > > o, line_items li, shipment_lines sl, shipments s where o.number=?and > > o.id <http://o.id> = li.order_id and li.id <http://li.id> > > sl.line_item_id and sl.shipment_id > > s.id <http://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 <http://o.id> > > li.order_id join shipment_lines as sl on sl.line_item_id = li.id > > <http://li.id> join > > shipments as s on sl.shipment_id = s.id <http://s.id>") > > > > In the above query I need to add the "distinct s.id <http://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, > > > > ** > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org <mailto:Rails@lists.rubyonrails.org> > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > >------------------------------------------------------------------------ > > > >_______________________________________________ > >Rails mailing list > >Rails@lists.rubyonrails.org > >http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060112/d4dbb0ba/attachment.html
Mufaddal Khumri
2006-Jan-12 22:08 UTC
[Rails] Question about using DISTINCT in ActiveRecord find method.
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, Liquid wrote:> 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 > > > On 1/13/06, *Mufaddal Khumri* <mkhumri@allegromedical.com > <mailto:mkhumri@allegromedical.com>> wrote: > > I will have a look into that. In the meanwhile, could someone post the > ActiveRecord way of doing this query? > > Thanks, > > matthew clark wrote: > > > 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 > > > > On 1/12/06, *Mufaddal Khumri* <mkhumri@allegromedical.com > <mailto:mkhumri@allegromedical.com> > > <mailto: mkhumri@allegromedical.com > <mailto:mkhumri@allegromedical.com>>> wrote: > > > > 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 > <http://s.id> > > <http://s.id> from orders > > o, line_items li, shipment_lines sl, shipments s where > o.number=? and > > o.id <http://o.id> <http://o.id> = li.order_id and li.id > <http://li.id> <http://li.id> > > sl.line_item_id and sl.shipment_id > > s.id <http://s.id> <http://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 <http://o.id> > < http://o.id> > > li.order_id join shipment_lines as sl on sl.line_item_id > li.id <http://li.id> > > <http://li.id> join > > shipments as s on sl.shipment_id = s.id <http://s.id> > <http://s.id>") > > > > In the above query I need to add the "distinct s.id > <http://s.id> < http://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, > > > > ** > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > <mailto:Rails@lists.rubyonrails.org> <mailto: > Rails@lists.rubyonrails.org <mailto:Rails@lists.rubyonrails.org>> > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > >------------------------------------------------------------------------ > > > >_______________________________________________ > >Rails mailing list > > Rails@lists.rubyonrails.org <mailto:Rails@lists.rubyonrails.org> > >http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org <mailto:Rails@lists.rubyonrails.org> > http://lists.rubyonrails.org/mailman/listinfo/rails > > >------------------------------------------------------------------------ > >_______________________________________________ >Rails mailing list >Rails@lists.rubyonrails.org >http://lists.rubyonrails.org/mailman/listinfo/rails > >
Liquid
2006-Jan-12 22:25 UTC
[Rails] Question about using DISTINCT in ActiveRecord find method.
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 On 1/13/06, Mufaddal Khumri <mkhumri@allegromedical.com> wrote:> > 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, > > Liquid wrote: > > > 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 > > > > > > On 1/13/06, *Mufaddal Khumri* <mkhumri@allegromedical.com > > <mailto:mkhumri@allegromedical.com>> wrote: > > > > I will have a look into that. In the meanwhile, could someone post > the > > ActiveRecord way of doing this query? > > > > Thanks, > > > > matthew clark wrote: > > > > > 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 > > > > > > On 1/12/06, *Mufaddal Khumri* <mkhumri@allegromedical.com > > <mailto:mkhumri@allegromedical.com> > > > <mailto: mkhumri@allegromedical.com > > <mailto:mkhumri@allegromedical.com>>> wrote: > > > > > > 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 > > <http://s.id> > > > <http://s.id> from orders > > > o, line_items li, shipment_lines sl, shipments s where > > o.number=? and > > > o.id <http://o.id> <http://o.id> = li.order_id and li.id > > <http://li.id> <http://li.id> > > > sl.line_item_id and sl.shipment_id > > > s.id <http://s.id> <http://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 <http://o.id> > > < http://o.id> > > > li.order_id join shipment_lines as sl on sl.line_item_id > > li.id <http://li.id> > > > <http://li.id> join > > > shipments as s on sl.shipment_id = s.id <http://s.id> > > <http://s.id>") > > > > > > In the above query I need to add the "distinct s.id > > <http://s.id> < http://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, > > > > > > ** > > > _______________________________________________ > > > Rails mailing list > > > Rails@lists.rubyonrails.org > > <mailto:Rails@lists.rubyonrails.org> <mailto: > > Rails@lists.rubyonrails.org <mailto:Rails@lists.rubyonrails.org>> > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > > >------------------------------------------------------------------------ > > > > > >_______________________________________________ > > >Rails mailing list > > > Rails@lists.rubyonrails.org <mailto:Rails@lists.rubyonrails.org> > > >http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org <mailto:Rails@lists.rubyonrails.org> > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > >------------------------------------------------------------------------ > > > >_______________________________________________ > >Rails mailing list > >Rails@lists.rubyonrails.org > >http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060112/bbd5bc8e/attachment-0001.html