SELECT ARRAY function?

[boehs]

Hello! These two should be functionally equivalent:

SELECT ARRAY(SELECT id FROM mytable)

Or:

SELECT array_agg(id) FROM mytable

The former should be more performant, but as far as I can tell its not implemented. I have a query like

      .select((e) =>
        e
          .selectFrom("entitytags")
          .select("tag")
          .whereRef("user_entity", "=", "userentities.id")
          .as("tags")
      )

but as far as I can tell I can't wrap this (or I could use the sql template string but that'd be no fun)!

    .select((e) =>
      sql<string[]>`array(${e
        .selectFrom("entitytags")
        .select("tag")
        .whereRef("user_entity", "=", "userentities.id")})`.as("tags")
    );

[koskimas]

This seems to work https://kyse.link/we2xz

What's the issue?

[koskimas]

You can create a helper function like this so that you don't need to explicitly give the output type: https://kyse.link/Rip3y

[boehs]

Thanks, this helper is nice.

The specific issue was that wouldn't it make sense to have a c.fn.array? Or maybe I'm misunderstanding the point of c.fn?

Also, for anyone who maybe is trying something like where I eventually was going (which is pretty unrelated to the issue but for issue search), I ended up doing

.leftJoinLateral(
  (e) =>
    sql<{ tags: string[] }>`(select array(${e
      .selectFrom("entitytags")
      .select("tag")
      .whereRef(
        "user_entity",
        "=",
        "userentities.id"
      )}) as "tags")`.as<"tags">(sql`tags(tags)`),
  (e) => e.onTrue()
)

like

builder = builder.where("tags", "&&", [tags]);

The reason for this is I wanted to be able to filter using where on that column. Its a bit of a mess of typings and such but it works well enough