I have an sql view V which has a 0:1 correspondence to a table X. I would like to join this view onto another table, Y, which has a reference to X (type XId). I have specified the view as I would any other table in persistent. V's id column is a reference to X, but declaring the view as a table in persistent naturally gives it the type VId instead of XId. And so I can't join the view onto Y because the types don't match up.
I realize I can do this with rawSQL, but my query also has an IN clause, which doesn't seem to play well with a list of values (using rawSQL). Another option is to select the XId column twice in the view, and specify the extra one as having type XId in the model definition. Lastly I could fall back to inserting the view query inline or doing the query entirely with raw sql, skipping persistent's interpolation.
Is there a way to do this without resorting to the methods above? I'd prefer to use esqueleto if possible.
I haven't found a proper solution to this yet.
For the time being I am selecting each primary key twice in the view eg
... SELECT id, id AS xId...along with adding the corresponding table's key type to the second selected id in the view schema:
XView sql=xView ... xId XId