Do we support select from subquery?

[xpol]

Say I have a details table

CREATE TABLE `details`
(
    `id`             TEXT PRIMARY KEY NOT NULL,
    `user_id`     TEXT NULL,
    `item_id`        TEXT NULL,
    `unit_price`     INTEGER NULL,
    `quantity`       INTEGER NULL,
    `date`    TEXT NULL,
);

I want to get all the details records that the same user on the same date have bought both items specified 2 items (eg. item_id IN ("1", "2").

I can use a window function in a subquery:

SELECT id, user_id, item_id, date FROM (
    SELECT *, COUNT(item_id) OVER(PARTITION BY date, user_id) AS num FROM "details" WHERE item_id IN ("1", "2")
) WHERE num > 1

I also want to use rel.Repository.Iterate to get the final record in 500 items per batch.

How can I use current rel APIs to do so?

[Fs02]

we don't have support for sub query table yet
probably the only way for now is to use FindAll with Raw query