Give an example of a sub-select in a select

[d6y]

...because I couldn't figure it out for ages:

select 
 s16."ListPrice", 
 (select count(1) from "ListingPhoto" where "ListingID" = s16."ListingID")
from 
 "Listing" s16, "PropertyType" s17, "ListingStatus" s15 
where 
 (s16."PropertyTypeID" = s17."PropertyTypeID") and (s15."StatusID" = s16."StatusID")

is:

Listings.
  join(PropertyTypes).on{ _.propertyTypeId === _.propertyTypeId }.
  join(ListingStatuses).on{ case ((listing,propertyType), status) =>  status.statusId === listing.statusId }.
  map{ case ((listing,propertyType),status) => (listing.listPrice, ListingPhotos.filter(_.listingId === listing.listingId).length)  }

Notice the ListingPhotos.filter call right there in the map

[d6y]

NB: looks like you can only have Rep[T] in the map projection, not an arbitrary Query. In other words, that length call in the above example is what makes it work,

[d6y]

Currently Slick does not have a way to represent a query that returns a single row (it has headOption for actions, but not for queries). To get a sub-select into a select, there is a trick: use max (or min).

E.g.,

  def getAll(contactId: Long) = {

    val mainQuery = 
      Contacts
        .join(Sources).on(_.contactId === _.contactId)
        .join(Emails).on { case ((c, s), e) => c.contactId === e.contactId && e.isPrimary === true }

     mainQuery.map { case ((c, s), e) => 
      (c, e, s, SocialProfiles.
        filter(_.contactId === c.contactId).
        filterNot(_.defaultPhotoUrl.isEmpty).
        sortBy(_.socialType).
        map(_.defaultPhotoUrl).
        take(1).max
        )

     }

Which produces:

select 
  s21."ContactID", s21."TenantID", s21."WebVisitorID", s21."FirstName", s21."LastName", s21."BestPhone",
  s20."ContactID", s20."EmailAddress", s20."IsPrimary", s22."ContactID", s22."OriginalSource", 
  (select max(s142.s70) from (select top (1) "PhotoLink" as s70 from "ContactSocialDetails" where ("ContactID" = s21."ContactID") and ("PhotoLink" is not null) order by "SocialType") s142) 
from 
   "Contact" s21, "ContactWebDetail" s22, "ContactEmail" s20 
where 
  (s21."ContactID" = s22."ContactID") and 
  ((s21."ContactID" = s20."ContactID") and (s20."IsPrimary" = 1))

[d6y]

Related to this is an insert that takes values from subselects. Example: https://github.com/d6y/insert_from_selects