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
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
