Load associations from custom SQL or Prepared Statements

[valikos]

Is it possible to create a custom SQL in a way how Sequel builds complex queries and automatically load model associations from the query?

I have complex query with many_to_many relation where I need to filter records by income parameter.
I would like to work with instances of Sequel model with associations.
Also I want create a prepared statement for that.
I discovered that prepared statement prepared with eager loading produces N+1.

For example my query:

select students.uid, student_profile.first_name, student_profile.last_name, enrollment.uid, enrollment.status, enrollment.assesssment, enrollment.capacity
from students
inner join student_profiles as student_profile on (student_profile.id = students.student_profile_id)
inner join classroom_students on (classroom_students.student_id = students.id)
inner join enrollments as enrollment on (enrollment.classroom_student_id = classroom_students.id AND classroom_students.student_id = students.id)
where classroom_students.classroom_id = 1
order by student_profile.first_name, student_profile.last_name

My schema:

# Table: classroom_students
# Columns:
#  id                    | integer
#  classroom_id | integer
#  student_id     | integer
-----------------------------
# Table: enrollments
# Columns:
#  id                                   | integer
#  uid                                 | uuid
#  status                            | boolean
#  classroom_student_id | integer  
-----------------------------
# Table: classrooms
# Columns:
#  id                    | integer
#  uid                  | uuid
#  name              | character varying(80)
#  capacity         | integer
#  school_id       | integer
-----------------------------
# Table: students
# Columns:
#  id                             |  integer
#  uid                           | uuid
#  status                      | student_status
#  school_id                | integer
#  student_profile_id | integer
-----------------------------
# Table: student_profiles
# Columns:
#  id                 | integer
#  uid               | uuid   
#  first_name  | character varying(255) 
#  last_name   | character varying(255)

[jeremyevans]

It's possible to use custom SQL. See the :dataset and :eager_loader association options:

• https://sequel.jeremyevans.net/rdoc/files/doc/association_basics_rdoc.html#label-3Adataset
• https://sequel.jeremyevans.net/rdoc/files/doc/advanced_associations_rdoc.html#label-Custom+Eager+Loaders

Note that you shouldn't need either for what you want. I would guess that it would be better to use the many_through_many plugin (https://sequel.jeremyevans.net/rdoc-plugins/classes/Sequel/Plugins/ManyThroughMany.html), which appears to handle your needs without any custom SQL.

[valikos]

I have tried plugin many_through_many and I find some problem with eager_block. Looks like association described with many_through_many not support eager_block.
I made association with custom dataset and eager_loader.
Also I find many_through_many very useful.

Thanks for advice!