How to dynamically insert a column name into the query! macro in Rust's sqlx

Question

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.

Answer 1

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)
}