Dfyjkt

I need to calculate users count inside of select query.
Here is my SQL snippet

SELECT count(id) FROM demo.users WHERE year_id = 'c4c62a9d-801f-4573-92a8-aa0a8589200a'

I use it inside of

 CASE count(DISTINCT assigned_lessons.id)
 WHEN 0 THEN 0
 ELSE count(DISTINCT homework_results.id) :: float /
 (
 count(DISTINCT assigned_lessons.id) *
 (SELECT count(id) FROM demo.users WHERE year_id = 'c4c62a9d-801f-4573-92a8-aa0a8589200a')
 )
 END AS completed_homework_rate

Here is my implementation on Sequel which works

 Sequel.case( 0 => 0 ,
 Sequel.cast(count(:homework_results__id).distinct, :float) /
 (
 count(:assigned_lessons__id).distinct *
 DB['demo__users'.to_sym].select do
 count(id)
 end.where(year_id: 'c4c62a9d-801f-4573-92a8-aa0a8589200a')
 ),
 count(:assigned_lessons__id).distinct,
 ).as(:completed_homework_rate),

but I need use something like this

 Sequel.case( 0 => 0 ,
 Sequel.cast(count(:homework_results__id).distinct, :float) /
 (
 count(:assigned_lessons__id).distinct *
 DB["#schema__users".to_sym].select do
 count(id)
 end.where(year_id: year.id)
 ),
 count(:assigned_lessons__id).distinct,
 ).as(:completed_homework_rate),

I need to use schema and year varibale inside of with query but Sequel say me

undefined method `id' for #<Sequel::SQL::Identifier @value=>:year>

or if I pass year id directly as a string it replaces schema variable in the wrong way

SELECT count("id") FROM "#<Sequel::SQL::Identifier:0x00007f8d36b553b8>"."users"

Simplified use case

 DB[:curriculum_strands].select do
 [
 Sequel.case( 0 => 0 ,
 Sequel.cast(count(:homework_results__id).distinct, :float) /
 (
 count(:assigned_lessons__id).distinct *
 DB["#schema__users".to_sym].select do
 count(id)
 end.where(year_id: year.id)
 ),
 count(:assigned_lessons__id).distinct,
 ).as(:completed_homework_rate),
 ]
 end.left_join(...)

Is any way to pass variables to the select block in this case?

I found that I don't need to use block it select.

Here is my solution

 DB[:curriculum_strands].select(
 completed_homework_rate.as(:completed_homework_rate),
 ).left_join(...)

private

def completed_homework_rate
 Sequel.case( 0 => 0 ,
 count_distinct(:homework_results__id).cast(Float) /
 (count_distinct(:assigned_lessons__id) * users_count),
 count_distinct(:assigned_lessons__id),
 )
end

def users_count
 DB["#schema__users".to_sym].select count(id) .where(year_id: year.id)
end

def count_distinct(column)
 Sequel.function(:count, column).distinct
end

Ideas for improving this code are accepting... )