Slick Queries (I)

Enquiries (I)

This article describes Slick's basic queries, such as selecting, inserting, updating, deleting records, and so on.

Sorting and filtering

Slick provides a variety of methods that you can use to sort and filter, such as:

val q = Album.filter(_.albumid === 101)

//select `AlbumId`, `Title`, `ArtistId` 
//from `Album` where `AlbumId` = 101

val q = Album.drop(10).take(5)
//select .`AlbumId` as `AlbumId`, .`Title` as `Title`,
// .`ArtistId` as `ArtistId` from `Album`  limit 10,5

val q = Album.sortBy(_.title.desc)
//select `AlbumId`, `Title`, `ArtistId` 
//from `Album` order by `Title` desc

Join and Zipping

Join refers to multi-table queries, which can be implemented in two different ways, one by explicitly calling methods that support multi-table connections, such as the innerJoin method, and the other by explicitly calling methods that support multi-table connections , and the other by implicitly connecting , which do not directly use these connection methods (such as the LeftJoin method).

An implicit cross-Join is Query's flatMap operation (using multiple builds in the for expression), for example:

val q = for{a <- Album
            b <- Artist
        } yield( a.title, b.name)

//select x2.`Title`, x3.`Name` from `Album` x2, `Artist` x3

If you add a conditional filter expression, it becomes an implicit inner join, for example:

val q = for{a <- Album
            b <- Artist
            if a.artistid === b.artistid
        } yield( a.title, b.name)

//select x2.`Title`, x3.`Name` from `Album` x2, `Artist` x3 
//where x2.`ArtistId` = x3.`ArtistId`

Explicit multi-table connections use the innerJoin, leftJoin, rightJoin, outerJoin methods, such as:

val explicitCrossJoin = = for {
             (a,b) <- Album innerJoin Artist  
             } yield( a.title, b.name)

//select x2.x3, x4.x5 from (select x6.`Title` as x3 from `Album` x6) 
//x2 inner join (select x7.`Name` as x5 from `Artist` x7) x4 on 1=1

val explicitInnerJoin  = for {
         (a,b) <- Album innerJoin Artist on (_.artistid === _.artistid)
         } yield( a.title, b.name)
//select x2.x3, x4.x5 from (select x6.`Title` as x3, x6.`ArtistId` as x7 from `Album` x6) x2 
//inner join (select x8.`ArtistId` as x9, x8.`Name` as x5 from `Artist` x8) x4 on x2.x7 = x4.x9

val explicitLeftOuterJoin   = for {
         (a,b) <- Album leftJoin Artist on (_.artistid === _.artistid)
         } yield( a.title, b.name.?)
//select x2.x3, x4.x5 from (select x6.`Title` as x3, x6.`ArtistId` as x7 from `Album` x6) x2 
//left outer join (select x8.`ArtistId` as x9, x8.`Name` as x5 from `Artist` x8) x4 on x2.x7 = x4.x9

val explicitRightOuterJoin   = for {
         (a,b) <- Album rightJoin Artist on (_.artistid === _.artistid)
         } yield( a.title.?, b.name)
//select x2.x3, x4.x5 from (select x6.`Title` as x3, x6.`ArtistId` as x7 from `Album` x6) x2 
//right outer join (select x8.`ArtistId` as x9, x8.`Name` as x5 from `Artist` x8) x4 on x2.x7 = x4.x9

Note the b.name in leftJoin and rightJoin.? a nd a.title.? " .?" is because an additional NULL value is generated when an external query is made, and you must guarantee that the value of the Option type is returned.

In addition to the usual InnerJoin, LeftJoin, RightJoin, Scala provides the Zip method, which has a syntax similar to Scala's collection type, such as:

val zipJoinQuery  = for {
       (a,b) <- Album zip Artist
     } yield( a.title.?, b.name)

In addition, there is a zipWithIndex that can put a table row and an integer sequence Zip operation starting at 0, which is equivalent to adding a serial number to a row, for example

val zipWithIndexJoin  = for {
       (a,idx) <- Album.zipWithIndex 
     } yield( a.title, idx)