1

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

2

Hello @remcopoelstra,

Indeed including(all:) has to run an extra request:

// SELECT * FROM author WHERE ...
// SELECT * FROM books WHERE ...
Author.filter(...).including(all: Author.books)

If we use a CTE, GRDB would have to know which request(s) should use the WITH clause:

// WITH ... SELECT * FROM author ...
Author.with(cte)

// SELECT * FROM author ...
// WITH ... SELECT * FROM books WHERE ...
Author
    .with(cte)
    .including(all: cteAssociation)

// WITH ... SELECT * FROM author ...
// WITH ... SELECT * FROM books WHERE ...
Author
    .with(cte)
    .filter(/* some cte stuff */)
    .including(all: cteAssociation)

This logic is not implemented (assuming it can be implemented), and that's why including(all:) does not work with CTEs.

So I understand why you consider using a view instead of a CTE. This indeed unlocks including(all:).

No, record types are tied to a single table/view.

3

Thanks for the explanation.

I was already afraid this couldn't be done, but I was just hoping I had some sort of oversight :slight_smile: