Thank you very much Jeremy! sobota, 9 stycznia 2021 o 21:36:18 UTC+1 Jeremy Evans napisał(a):
> 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/5089e1b1-3f37-4491-a11f-57b850f5ff5bn%40googlegroups.com.
