4

I'm using the sqlx crate to interact with a Postgres instance.

I have the following function:

pub async fn fetch_tweets_by_metric(
    pool: &PgPool,
    metric: &str,
) -> Result<Vec<Tweet>, sqlx::error::Error> {
    let tweets = sqlx::query_as!(
        Tweet,
        r#"
        SELECT * 
        FROM tweets
        ORDER BY $1 DESC
        "#,
        metric,
    )
    .fetch_all(pool)
    .await?;
    Ok(tweets)
}

I'd like to order the results by one of the columns in the table, passed in as metric. If I simply pass a string with column name it doesn't work (ie the code above is broken).

What's the right way to do it? I can't find anything in the docs / on google.

Sebastián Palma
  • 32,692
  • 6
  • 40
  • 59
ilmoi
  • 1,994
  • 2
  • 21
  • 45

1 Answers1

4

The macros are only usable with a constant SQL.

If your sql is dynamic, use the query function:

pub async fn fetch_tweets_by_metric(
    pool: &PgPool,
    metric: &str,
) -> Result<Vec<Tweet>, sqlx::error::Error> {
    let sql = format!(
        r#"
        SELECT * 
        FROM tweets
        ORDER BY {} DESC
        "#,
        metric,
    );
    let tweets = sqlx::query(sql)
        .fetch_all(pool)
        .await?;
    Ok(tweets)
}
Denys Séguret
  • 372,613
  • 87
  • 782
  • 758
  • Wow this worked, thanks! Question - how exactly does query(slqx) figure out to deserialize the results into Tweet struct, given we never explicitly pass it it? Does it somehow infer it from the return type of the function? – ilmoi Jun 05 '21 at 04:47
  • The job of inferring the right type in case of generics is done by the rust compiler. You need to specify it only when it can't be inferred. – Denys Séguret Jun 05 '21 at 05:36