Support allowing additional columns in query that does not appear in the struct

[Pajn]

I'm eyeing to port my application from tokio-postgres (and a bunch of helper libs) to sqlx and one of the queries I'm struggeling with is

SELECT tags.*, COUNT(tasks_tags.*) AS popularity FROM tags
INNER JOIN tasks_tags
  ON tasks_tags.tag_id = tags.id
WHERE tags.user_id = $1
GROUP BY tags.id
ORDER BY popularity DESC

and my struct looks like this

pub struct Tag {
  pub id: Uuid,
  pub user_id: Uuid,
  pub name: String,
  pub color: String,
  pub created_at: DateTime<Utc>,
}

popularity is only used for sorting in the query and is not something that I want to add to my struct. Would you consider allowing this, either via a special character in the query (like the special treatment of ! and _ in 0.4) or maybe by some attribute on the struct (for example #[sqlx(non_exhaustive)] or #[sqlx(ignore = "popularity")])?

[abonander]

@Pajn as a workaround, you can do the main select in a CTE and then select only the columns you need out of that:

WITH popular_tags AS (
    SELECT tags.*, COUNT(tasks_tags.*) AS popularity FROM tags
    INNER JOIN tasks_tags
      ON tasks_tags.tag_id = tags.id
    WHERE tags.user_id = $1
    GROUP BY tags.id
    ORDER BY popularity DESC
)
SELECT id, user_id, name, color, created_at
FROM popular_tags

You're using the macros, right? Unfortunately we can't introspect attributes on the struct from sqlx::query!(), we can only see the direct input. I believe if you used the function variant with #[derive(sqlx::FromRow)] on the struct it should work as-is, although you lose the compile-time checking:

sqlx::query_as::<Tag, _>(<query>).fetch[_all, _one, _optional](<conn>)

[Pajn]

Thank you! I like that workaround.

But it would be a nice bonus to have a nice way to exclude, maybe with a leading underscore if attributes can't be used.

SELECT tags.*, COUNT(tasks_tags.*) AS "_popularity" FROM tags
INNER JOIN tasks_tags
  ON tasks_tags.tag_id = tags.id
WHERE tags.user_id = $1
GROUP BY tags.id
ORDER BY "_popularity" DESC

and the rule would be columns with a leading name would be allowed to be missing in the struct, but must match the type if they exists. So the above query would work with both:

pub struct Tag {
  pub id: Uuid,
  pub user_id: Uuid,
  pub name: String,
  pub color: String,
  pub created_at: DateTime<Utc>,
}

and

pub struct Tag {
  pub id: Uuid,
  pub user_id: Uuid,
  pub name: String,
  pub color: String,
  pub created_at: DateTime<Utc>,
  pub _popularity: u64,
}

but not with

pub struct Tag {
  pub id: Uuid,
  pub user_id: Uuid,
  pub name: String,
  pub color: String,
  pub created_at: DateTime<Utc>,
  pub _popularity: String,
}

However I'm only fantasizing at this point. I would be entirely happy to just use the workaround if you prefer to close this.

[abonander]

and the rule would be columns with a leading name would be allowed to be missing in the struct, but must match the type if they exists.

That's not possible as the macros are currently implemented because they directly emit struct literals. The set of columns must be exact. We're talking about eventually switching sqlx::query_as!() to use FromRow in which case we could allow extra columns without issue while still typechecking the ones that exist. That's been discussed in #514 but it requires the const_panic feature of Rust which is not stable.

Otherwise, we could extend the naming rule to ignore columns with a leading underscore but that won't work for the use-case you specify since you want to be able to decide when to use the column vs not.

[Pajn]

Otherwise, we could extend the naming rule to ignore columns with a leading underscore but that won't work for the use-case you specify since you want to be able to decide when to use the column vs not.

I just thought about it to not destroy other possible use cases but for me personally, always ignoring columns with a leading underscore (or similar rule) would be perfectly fine. But you workaround is great so waiting for const_panic and #514 is also perfectly fine by me.