Hi,
I've one table with basic/raw data element and another table imageFile that might mask/alter the data from the first table.
Most of the time I want to fetch the masked data instead of the raw data.
I currently solve this using a CTE that combines the data and returns the data as if it's coming from the raw table:
static func polarisedElements() -> QueryInterfaceRequest<Element> {
let imageFileAlias = TableAlias()
let clearPolaritySelectable: SQLSpecificExpressible = Element.Columns.clearPolarity != (imageFileAlias[Column("clearPolarity")] ?? false)
let cteRequest = Element.select([Element.Columns.id,
.....
Element.Columns.yStep,
clearPolaritySelectable.forKey("clearPolarity"),
Element.Columns.polarityIndex,
......
Element.Columns.apertureOffsetY,])
.joining(optional: Element.image.aliased(imageFileAlias))
let cte = CommonTableExpression<Element>(named: "elementPolarised",
request: cteRequest)
return cte.all().with(cte)
}
try Element.polarisedElements().fetchAll(db)
This works fine for basic fetches and additional filters() that I defined on Element can be used as is.
It becomes more problematic for fetches where I want to join this CTE to other tables.
I was thinking about adding the various CTE associations to a separate container struct PolarisedElement, and still return Element's, but carefully reading the CTE documentation I noticed that .including(all:) is not supported for CTE's. Which I unfortunately need, hence I can't really go this way.
My next thought was to use a proper SQLite view instead of the CTE. The record associated with this view behaves a bit like a subclass of Element, as it does everything that Element does. But Element is a struct and hence I can't subclass it. Defining a separate struct PolarisedElement that matches to the view would duplicate all functionality of Element.
Is there a way to have some sort of Element that fetches from the view, but inserts into the table? This of course to prevent a lot of duplication between a table and a view bound type.
Thanks in advance.
Kind regards,
Remco Poelstra
