Slick Queries (II)

Enquiries (II)

Union

The results of the two queries can be combined by the operations of the union All and union:

val q1= Album.filter(_.artistid <10)
val q2 = Album.filter(_.artistid > 15)
val unionQuery  = q1 union q2
val unionAllQuery = q1 ++ q2

Union operations remove duplicate results, while unionAll simply connects the two query results (generally efficient).

Aggregation

Like SQL, Slick has collection operations such as min, max, sum, avg, and so on

val q = Album.map(_.artistid)
val q1 = q.max
val q2 = q.min 
val q3 = q.avg 
val q4 = q.sum

Note: Here q.max, min, avg, sum returns a result type of Column (Option) and to get the best scalar type of value T, you can call run, get Option(T), and then call Option's get or getOrDefault, for example:

val q = Album.map(_.artistid)
val q1 = q.max 
println(q1.run.get)

Printed results: 275

Other Aggregation operations include length, exists, such as:

val q1 = Album.length
val q2 = Album.exists

Grouping uses groupBy operations, similar to GroupBy operations of scala collection types:

val q= (for {
     a <- Album
     b <- Artist
     if a.artistid === b.artistid
   } yield (b.artistid,b.name)
).groupBy(_._2)
val q1 = q.map { case (name, records) =>
        (records.map(_._1).avg, name,records.length)}
q1 foreach println 

This code uses two queries to give Album statistics based on the artist's album, where the middle query q contains a nested Query, scala does not currently support direct query nested Query, so we need to query in two, and the printed part of the result is as follows:

(Some(230),Some(Aaron Copland & London Symphony Orchestra),1)
(Some(202),Some(Aaron Goldberg),1)
(Some(1),Some(AC/DC),2)
(Some(214),Some(Academy of St. Martin in the Fields & Sir Neville Marriner),1)
(Some(215),Some(Academy of St. Martin in the Fields Chamber Ensemble & Sir Neville Marriner),1)
(Some(222),Some(Academy of St. Martin in the Fields, John Birch, Sir Neville Marriner & Sylvia McNair),1)
(Some(257),Some(Academy of St. Martin in the Fields, Sir Neville Marriner & Thurston Dart),1)
(Some(2),Some(Accept),2)
(Some(260),Some(Adrian Leaper & Doreen de Feis),1)
(Some(3),Some(Aerosmith),1)
(Some(197),Some(Aisha Duo),1)
(Some(4),Some(Alanis Morissette),1)
(Some(206),Some(Alberto Turco & Nova Schola Gregoriana),1)
(Some(5),Some(Alice In Chains),1)
(Some(252),Some(Amy Winehouse),2)
...