drift icon indicating copy to clipboard operation
drift copied to clipboard

Result class name for list subqueries

Open JCKodel opened this issue 2 years ago • 1 comments

According to https://drift.simonbinder.eu/docs/using-sql/drift_files/, section ResultClassNames, we can write several queries on drift files and make them return some common type.

In the same page, in section LIST subqueries, it's written we can create List<T> on subqueries.

But how can I specify a common type for those query results. For instance:

getIssue:
  SELECT
    i.id,
    CASE front
      WHEN 'b' THEN 'Beleza'
      WHEN 'e' THEN 'Emocional'
      WHEN 's' THEN 'Saúde'
    END AS front,
    i.name,
    i.description,
    LIST(
      SELECT
        eo.id,
        eo.name as title,
        eo.botanicalName AS subtitle
        FROM EssentialOils_Issues AS eoi
        INNER JOIN EssentialOils AS eo ON eo.id = eoi.essentialOilId AND eoi.issueId = i.id
        WHERE eo.active = 1
    ) AS essentialOils,
    LIST(
      SELECT
        r.id,
        r.title,
        r.subtitle
      FROM Recipes AS r
      INNER JOIN Recipes_Issues AS ri ON ri.issueId = i.id AND ri.recipeId = r.id
      WHERE r.active = 1
      ORDER BY r.title
    ) AS recipes,
    LIST(
      SELECT
        vo.id,
        vo.name AS title,
        vo.botanicalName AS subtitle
      FROM VegetableOils AS vo
      INNER JOIN VegetableOils_Issues AS voi ON voi.issueId = i.id AND voi.vegetableOilId = vo.id
      WHERE vo.active = 1
      ORDER BY vo.name
    ) AS vegetableOils
  FROM Issues AS i
  WHERE i.id = :id AND i.active = 1;

All those LIST above are of the same type (a type containing id, title and subtitle).

It is even possible to specify the type on those LIST? If not, would be nice to standardize the WITH as an alias for an existing class (so we could write LIST (...) WITH OutputClass AS propertyName).

JCKodel avatar Sep 16 '22 19:09 JCKodel

It is even possible to specify the type on those LIST?

At the moment, this is not possible. I agree that it could be a useful feature.

simolus3 avatar Sep 16 '22 22:09 simolus3