Thanks Jeremy, this worked perfectly well!
On Friday, June 15, 2012 3:56:18 PM UTC-4, Jeremy Evans wrote:
>
>
> 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/-/H1yo8KOqvGwJ.
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.
