I am creating slightly less than a trivial request using Slick. And in this process, I realized that Slick creates a connection to a subquery, and not directly to the table.
Here is the code that builds the request:
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 a searchfunction is one that is called from one of the service classes. The request generated by Slick looks 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, the first three tables are joined directly, but they are combined with the "media" table - this is a subquery. What for? How can I get rid of this?