(Infix) / Custom - Filter/SortBy Query

[schmitch]

Version: 0.10.0
Module: quill-jdbc

At the moment we have a "more" complex Query.
Currently we have a History Table which has a total different Set of Schema than our current main table. To query it we use something like that:

private val uuidColumn = quote {
    () => infix"(data->>'order')::jsonb->>'pdf' AS pdf".as[UUID]
  }

  def byOrderId(id: Long): Option[OrderPdfHistory] = {
    run(quote {
      query[OrderPdfHistory].schema(_.entity("history").columns(
        _.orderId -> "reference_id",
        _.pdf -> "pdf"
      ))
          .filter(_.orderId == lift(id))
          .sortBy(_.date)(Ord.desc)
          .map(p => (p.id, p.orderId, uuidColumn(), p.username, p.date))
    }).headOption.map(OrderPdfHistory.tupled(_))
  }

This works pretty well, however we mostly also add the type of the history directly to the query, which we don't need to retrieve or use in the case class.
So I guess it would be great to have a way to add infix filters, i.e. filters that won't actually be queried.

At the moment you need to add a "WHERE / AND" part to your infix, which isn't really scalable, when you want to reuse the infix.

Workaround

The current workaround is maintaining a infix for both WHERE and AND and hope that you won't be hit by #521

Another possible way would be introducing raw for filters, which would have the downside that you can't add a infix like that:

(typ: String) => infix"reference_typ = $typ"where you could reuse the filter part for multiple queries and for combinations of queries where you only need it as a AND or WHERE part.

It would also make queries more compact so that you don't need two selects if one part of your filter is a infix. Maybe this is also useful by sortBy and others.

Another Workaround is using existsAny with a case class:

  case class HistoryType(referenceName: String)

  private val existsAny = quote {
    new {
      def apply[T](xs: Query[T])(p: T => Boolean) =
        xs.filter(p(_)).nonEmpty
    }
  }

However that will yield a way slower query:

SELECT p.id, p.reference_id, (data->>'order')::jsonb->>'pdf' AS pdf, p.username, p.date FROM history p WHERE EXISTS (SELECT x1.* FROM history x1 WHERE (x1.reference_name = 'order') AND (p.reference_id = ?)) ORDER BY p.date DESC

with:

.filter(p => existsAny(query[HistoryType].schema(_.entity("history")))(_.referenceName == "order" && p.orderId == lift(id)))

using two selects won't be too bad, but to touch a index over reference_name and reference_id it's really suboptimal to have them in different queries. And also the reference_name will actually go trough a really slow query.

See the analyzer:

 Sort  (cost=9315.36..9315.37 rows=3 width=684) (actual time=18.679..18.680 rows=2 loops=1)
   Sort Key: p.date DESC
   Sort Method: quicksort  Memory: 25kB
   ->  Nested Loop Semi Join  (cost=0.84..9315.33 rows=3 width=684) (actual time=12.962..18.664 rows=2 loops=1)
         ->  Index Scan using history_reference_name_id on history p  (cost=0.42..8132.74 rows=3 width=684) (actual time=12.841..18.537 rows=2 loops=1)
               Index Cond: (reference_id = 100042)
         ->  Materialize  (cost=0.42..1182.49 rows=373 width=0) (actual time=0.024..0.024 rows=1 loops=2)
               ->  Index Only Scan using history_reference_name_id on history x1  (cost=0.42..1180.63 rows=373 width=0) (actual time=0.033..0.033 rows=1 loops=1)
                     Index Cond: (reference_name = 'order'::text)
                     Heap Fetches: 1
 Planning time: 0.217 ms
 Execution time: 18.760 ms

in a normal WHERE reference_name = 'order' AND reference_id = ? it will be sub ms:

 Sort  (cost=8.46..8.47 rows=1 width=684) (actual time=0.145..0.145 rows=1 loops=1)
   Sort Key: date DESC
   Sort Method: quicksort  Memory: 25kB
   ->  Index Scan using history_reference_name_id on history p  (cost=0.42..8.45 rows=1 width=684) (actual time=0.136..0.137 rows=1 loops=1)
         Index Cond: ((reference_name = 'order'::text) AND (reference_id = 100042))
 Planning time: 0.157 ms
 Execution time: 0.178 ms

So the only "real" workaround to make this efficient is adding a unnecessary field to the case class.

Edit: The same thing happens when you want to sort by multiple keys. That is really inefficient.

@getquill/maintainers