On Sat, Jan 9, 2021 at 4:22 AM Mateusz Urbanski <
[email protected]> wrote:
> I have the following SQL query:
>
> select
> c.id,
> c.name,
> COUNT(t.id)
> from
> categories c
> left join
> categories_teachers ct on ct.category_id = c.id
> left join
> teachers t on t.id = ct.teacher_id
> group by
> c.id;
>
> I'm trying to rebuild that SQL query with Sequel:
>
>
> Category
> .select(Sequel[:categories].*, Sequel.function(:count, "teachers.id"))
> .left_join(:categories_teachers, category_id: :id)
> .left_join(:teachers, id: Sequel[:categories_teachers][:teacher_id])
> .group(Sequel[:categories][:id])
> .all
>
> but this generates the following SQL query:
>
> SELECT
>
> "categories".*,
>
> count('teachers.id')
>
> FROM
>
> "categories"
>
> LEFT JOIN
>
> "categories_teachers" ON ("categories_teachers"."category_id" =
> "categories"."id") LEFT JOIN "teachers" ON ("teachers"."id" =
> "categories_teachers"."teacher_id")
>
> GROUP BY
>
> "categories"."id"
>
>
> What I'm doing wrong?
>
You can't specify a qualified identifier as a string.
Here's a more direct translation:
Category.
from{categories.as(:c)}.
select{[c[:id], c[:name], count(t[:id])]}.
left_join(:categories_teachers, {category_id: :id}, :table_alias=>:ct).
left_join(:teachers, {id: :teacher_id}, :table_alias=>:t).
group{c[:id]}.
all
This uses the following SQL:
SELECT c.id, c.name, count(t.id)
FROM categories AS c
LEFT JOIN categories_teachers AS ct ON (ct.category_id = c.id)
LEFT JOIN teachers AS t ON (t.id = ct.teacher_id)
GROUP BY c.id
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/CADGZSSeaTwRStfBee3AXHGs4eQt4LVap_S1hxcZ8GEzQip2Z2Q%40mail.gmail.com.
