Got it. Thanks-a-lot. -Aditya
On Mon, Sep 14, 2009 at 9:56 PM, Jeremy Evans <[email protected]>wrote: > > > > On Sep 14, 9:14 am, arrac <[email protected]> wrote: > > Hello, > > > > I'm new to Sequel and I'm having trouble writing a particular SQL > > query in Sequel. > > > > My schema (simplified): > > > > docs > > ------- > > id > > content > > > > tags > > ------- > > id > > name > > > > doc_tags > > -------------- > > doc_id > > tag_id > > > > My SQL query to fetch co-tagged tags ordered by count is > > > > select b.tag_id, count(b.tag_id) as c from doc_tags as a inner join > > doc_tags as b on a.doc_id = b.doc_id where a.tag_id = 2897 group by > > b.tag_id order by c desc > > > > In Sequel, > > > > I tried doing: > > co_tags = DB[:doc_tags___a].join(:doc_tags___b, :b__doc_id > > => :a__doc_id).filter(:a__tag_id => 2897) > > > > But when I try > > p co_tags.columns! > > > > I get > > => [:doc_id, :tag_id, :doc_id, :tag_id] > > > > And I'm unable to group due to duplicate column names. > > > > I'm lost! > > > > What would be the best/Sequel way of doing it? > > You should either alias the columns manually: > > co_tags = DB[:doc_tags___a]. > join(:doc_tags___b, [:doc_id]). > select(:a__doc_id, :a__tag_id___tag_id1, :b__tag_id___tag_id2). > filter(:a__tag_id => 2897) > > or, use graph instead of join: > > co_tags = DB[:doc_tags___a]. > graph(:doc_tags, [:doc_id], :table_alias=>:b). > filter(:a__tag_id => 2897) > > See http://pastie.org/616339 for an example of both of these > approaches. > > Jeremy > > > -- Aditya Ramana Rachakonda International Institute of Information Technology, Bangalore. +91 998 014 2950 +91 934 219 0839 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en -~----------~----~----~----~------~----~------~--~---
