On Friday, June 15, 2012 12:26:16 PM UTC-7, Sean Mackesey wrote:
>
> I am wondering how to use dataset operations to obtain an effect
> equivalent to MySQL's GROUP_CONCAT statement.
>
> Right now I have a table with a list of scientific sources, a table with a
> list of authors, and a junction table for the many-many relation between
> authors and sources. The primary keys in the 'source' and 'author' table
> are both 'id'; the junction table contains columns named 'id_author' and
> 'id_source'. When I simply join these three tables together, I get a table
> where there is a row for each author-source combination.
>
> *table = DB[:author_to_source].join(src,
> :id=>:id_source).join(DB[:author], :id=>:author_to_source__id_author)
> *
> *
> *
> *"SELECT * FROM `author_to_source` INNER JOIN (SELECT * FROM `source`) AS
> `t1` ON (`t1`.`id` = `author_to_source`.`id_source`) INNER JOIN (SELECT *
> FROM `author`) AS `t2` ON (`t2`.`id` = `t1`.`id_author`)"*
>
> I need to collapse all rows having the same source into a single row, by
> combining all the author names into a single comma-separated list that can
> fit in one field. In MySQL I can do this with GROUP_CONCAT. I can't
> figure out how to do it Sequel. I have tried:
>
> *table.group(:id_source)*
> *
> *
> *"SELECT * FROM `author_to_source` INNER JOIN (SELECT * FROM `source`) AS
> `t1` ON (`t1`.`id` = `author_to_source`.`id_source`) INNER JOIN (SELECT *
> FROM `author`) AS `t2` ON (`t2`.`id` = `author_to_source`.`id_author`)
> GROUP BY `id_source`"*
>
> But as you can see, this just adds a GROUP BY clause, which is a necessary
> part of GROUP_CONCAT but does not do the same thing. Is there a way to get
> GROUP_CONCAT functionality? Thanks.
>
GROUP_CONCAT is just an SQL function. In the simplest case, you can
probably do:
*table.select_group(:id_source).select_more{group_concat(:author)}
*If you want to use more advanced GROUP_CONCAT forms using DISTINCT, ORDER
BY, and/or SEPARATOR, you'll probably just want to use a literal string:
*table.select_group(:id_source).select_more{group_concat(`DISTINCT author
ORDER BY author DESC SEPARATOR ', '`)}
*Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/H7kJjH168VoJ.
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.
