I am building somewhat less than trivial query using Slick. And in the process, I realized that Slick creates a join with a subquery instead of directly with a table.
Here is the code that builds the query:
val baseProgramSearchQuery = Programs.programs join
Schools.schools on ((p, s) => p.schoolId === s.id && s.deletedAt.isEmpty) joinLeft
Addresses.addresses on { case ((_, s), a) => s.addressId === a.id && a.deletedAt.isEmpty } joinLeft
Medias.medias on { case (((_, s), _), m) => s.mediaId === m.id && m.deletedAt.isEmpty }
def search(searchCriteria: SearchCriteria,
drop: Long = 0,
take: Long = 100): Future[Seq[(Program, School, Option[Address], Option[Media])]] = {
val q = for {
(((program, school), address), schoolLogo) <- baseProgramSearchQuery
.filter {
case (((programs, schools), addresses), medias) =>
List(
searchCriteria.program.map(programCriteria => buildProgramFilters(programs, programCriteria)),
searchCriteria.school.map(schoolCriteria => buildSchoolFilters(schools, schoolCriteria))
).collect({ case Some(criteria) => criteria }).reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
}
.drop(drop)
.take(take)
} yield (program, school, address, schoolLogo)
db.run(q.result)
}
private def buildProgramFilters(programs: Programs, programSearchCriteria: ProgramSearchCriteria): Rep[Boolean] =
List(
Some(programs.deletedAt.isEmpty),
programSearchCriteria.name.map(n => programs.name.toLowerCase like s"%${n.toLowerCase}%"),
programSearchCriteria.ready.map(r => programs.ready === r)
).collect({ case Some(criteria) => criteria }).reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
private def buildSchoolFilters(schools: Schools, schoolSearchCriteria: SchoolSearchCriteria): Rep[Boolean] =
List(
Some(schools.deletedAt.isEmpty),
schoolSearchCriteria.name.map(n => schools.name.toLowerCase like s"%${n.toLowerCase}%"),
schoolSearchCriteria.ready.map(r => schools.ready === r)
).collect({ case Some(criteria) => criteria }).reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
where search function is the one that gets called from one of the service classes.
The query that Slick generates is something like this:
SELECT
x2.x3,
x2.x4,
x2.x5,
x2.x6,
x2.x7,
x2.x8,
x2.x9,
x2.x10,
x2.x11,
x2.x12,
x2.x13,
x2.x14,
x2.x15,
x2.x16,
x2.x17,
x2.x18,
x2.x19,
x2.x20,
x2.x21,
x2.x22,
x2.x23,
x2.x24,
x2.x25,
x2.x26,
x2.x27,
x2.x28,
x2.x29,
x2.x30,
x2.x31,
x2.x32,
x2.x33,
x2.x34,
x2.x35,
x2.x36,
x2.x37,
x2.x38,
x2.x39,
x2.x40,
x2.x41,
x2.x42,
x2.x43,
x2.x44,
x2.x45,
x2.x46,
x2.x47,
x2.x48,
x2.x49,
x2.x50,
x2.x51,
x2.x52,
x2.x53,
x2.x54,
x2.x55,
x2.x56,
x2.x57,
x2.x58,
x2.x59,
x2.x60,
x2.x61,
x2.x62,
x2.x63,
x2.x64,
x2.x65,
x2.x66,
x2.x67,
x2.x68,
x2.x69,
x2.x70,
x2.x71,
x2.x72,
x2.x73,
x2.x74,
x2.x75,
x2.x76,
x2.x77,
(CASE WHEN (x78."id" IS NULL)
THEN NULL
ELSE 1 END),
x78."id",
x78."name",
x78."file",
x78."size",
x78."tag",
x78."createdat",
x78."updatedat",
x78."deletedat",
x78."createdby",
x78."updatedby"
FROM (SELECT
x79."deletedat" AS x42,
x79."slug" AS x10,
x79."updatedby" AS x44,
x79."tuitions_url" AS x36,
x79."acceptance_id" AS x16,
x79."description" AS x7,
x79."media_id" AS x11,
x79."study_duration_unit" AS x26,
x79."min_pay_acceptance" AS x34,
x79."practicum_duration_unit" AS x28,
x79."internship_duration" AS x23,
x79."coop_duration_unit" AS x27,
x79."exam_cost" AS x33,
x79."class_size" AS x17,
x79."practicum_duration" AS x22,
x79."tuition_scheme_id" AS x38,
x79."id" AS x3,
x79."terms" AS x8,
x79."createdat" AS x40,
x79."short_info" AS x15,
x79."duration_unit" AS x25,
x79."coop_duration" AS x21,
x79."availability" AS x39,
x79."original_name" AS x6,
x79."origin_url" AS x35,
x79."credits" AS x19,
x79."name" AS x5,
x79."duration" AS x18,
x79."schedule_id" AS x13,
x79."cources_url" AS x37,
x79."updatedat" AS x41,
x79."weekly_hours" AS x24,
x79."books" AS x32,
x79."registration" AS x30,
x79."study_duration" AS x20,
x79."registration_intl" AS x31,
x79."internship_duration_unit" AS x29,
x79."createdby" AS x43,
x79."study_kind_id" AS x9,
x79."school_id" AS x4,
x79."work_permit_attr_id" AS x14,
x79."ready" AS x12,
x80."deletedat" AS x60,
x80."slug" AS x52,
x80."updatedby" AS x62,
x80."website_url" AS x50,
x80."media_id" AS x51,
x80."year_established" AS x56,
x80."about" AS x48,
x80."classrooms" AS x55,
x80."display_copyright" AS x57,
x80."short_description" AS x53,
x80."dli_number" AS x63,
x80."id" AS x45,
x80."createdat" AS x58,
x80."name" AS x47,
x80."updatedat" AS x59,
x80."number_of_students" AS x49,
x80."address_id" AS x46,
x80."createdby" AS x61,
x80."ready" AS x54,
(CASE WHEN (x81."id" IS NULL)
THEN NULL
ELSE 1 END) AS x64,
x81."deletedat" AS x75,
x81."address2" AS x70,
x81."zip" AS x72,
x81."updatedby" AS x77,
x81."city" AS x71,
x81."address1" AS x69,
x81."continent_id" AS x67,
x81."id" AS x65,
x81."createdat" AS x73,
x81."country_id" AS x66,
x81."state_id" AS x68,
x81."updatedat" AS x74,
x81."createdby" AS x76
FROM "program" x79 INNER JOIN "school" x80 ON (x79."school_id" = x80."id") AND (x80."deletedat" IS NULL)
LEFT OUTER JOIN "address" x81 ON (x80."address_id" = x81."id") AND (x81."deletedat" IS NULL)) x2 LEFT OUTER JOIN
"media" x78 ON (x2.x51 = x78."id") AND (x78."deletedat" IS NULL)
WHERE (((x2.x42 IS NULL) AND (lower(x2.x5) LIKE '%arts%')) AND (x2.x12 = TRUE)) AND
(((x2.x60 IS NULL) AND (lower(x2.x47) LIKE '%alexander%')) AND (x2.x54 = TRUE))
LIMIT 100
OFFSET 0
As you can see, first three tables are getting joined directly, but these are joined with `media' table is a subquery. Why? How can I get rid of that?