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. -- 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/-/ksXbH9_SrOMJ. 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.
