On Fri, Feb 26, 2021 at 8:58 AM [email protected] <
[email protected]> wrote:
> Hi Jeremy,
>
> Based on my previous question, how do you use Sequel Models when FROM
> contains distinct?
>
>
> class Email < Sequel::Model; end
>
> SELECT
> name,
> string_agg(user_id::text, ',' order by ordering desc)
> FROM (
> SELECT DISTINCT ON (em.id, c.user_id)
> *
> FROM emails em
> JOIN contacts c ON c.email_id = em.id
> ) s
> GROUP BY name
>
>
> Because, when Email used, FROM becomes "emails"...
>
DB.from{emails.as(:em)}.
distinct{[em[:id], c[:user_id]]}.
join(:contacts, {:email_id=>:id}, :table_alias=>:c).
from_self(:alias=>:s).
select_group(:name).
select_append{string_agg(user_id.cast(String), ',').order(ordering.desc)}
SELECT "name", string_agg(CAST("user_id" AS text), ',' ORDER BY "ordering"
DESC)
FROM (
SELECT DISTINCT ON ("em"."id", "c"."user_id") *
FROM "emails" AS "em"
INNER JOIN "contacts" AS "c" ON ("c"."email_id" = "em"."id")
) AS "s"
GROUP BY "name"
You can use Email.from instead of DB.from if you want it to return model
instances.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CADGZSSeCVMgBzWUuXZo8_6VtM%3D1Z%2BOhoJ2WrcPXKjdD4gg5%3DUw%40mail.gmail.com.
