How to use sqlx query_as to fetch some of the model fields

Question

I have a table called order_data which has timestamp field called created_on and i32 field order_id. I want to query those in different methods. For created_on:

pub async fn fetch_last_created_on(pool: &Pool<Postgres>) -> Option<NaiveDateTime> {
    let result = match query_as::<Postgres, OrderDb>("select max(created_on) as created_on from order_data")
        .fetch_one(pool)
        .await
    {
        Ok(result) => result.created_on,
        Err(e) => {
            error!("Error fetching data: {}", e);
            None
        }
    };
    result
}

And for order_id:

pub async fn fetch_all_order_ids(pool: &Pool<Postgres>) -> Option<HashSet<i32>> {
    let result = match query_as::<Postgres, OrderDb>("select order_id from order_data")
        .fetch_all(pool)
        .await
    {
        Ok(result) => Some(result.iter().map(|order| order.order_id.unwrap()).collect()),
        Err(e) => {
            error!("Error fetching data: {}", e);
            None
        }
    };

    result
}

I've defined OrderDb as:

#[derive(FromRow)]
struct OrderDb {
    order_id: Option<i32>,
    created_on: Option<NaiveDateTime>,
}

But with this if use fetch_last_created_on it results in the following error

no column found for name: order_id

I could define two separate derive(FromRow) structs for each case, but is there a better way to handle it? Do note that I'm not using macros but methods.

I'm not well-versed in sqlx, but you probably want `query_as::<_, (NaiveDateTime,)>(...` or similar if you're expecting rows with only a single timestamp column. — kmdreko, Feb 13 '23 at 04:11
@kmdreko tried that, but `the trait for<'r> FromRow<'r, PgRow> is not implemented for NaiveDateTime` — Enigo, Feb 14 '23 at 02:54

score 1 · Accepted Answer · answered Feb 21 '23 at 03:54

Alright, I figured it out. There are different techniques to be used. For created_on:

pub async fn fetch_last_created_on(pool: &Pool<Postgres>) -> Option<NaiveDateTime> {
    let result: (Option<NaiveDateTime>, ) = query_as("select max(created_on) from order_data")
        .fetch_one(pool)
        .await
        .unwrap_or_else(|e| {
            error!("Couldn't fetch data! {}", e);
            (None, )
        });

    result.0
}

And for order_id (note, that I've changed the return type from HashSet to Vec):

pub async fn fetch_all_order_ids(pool: &Pool<Postgres>) -> Option<Vec<i32>> {
    return match query_scalar("select order_id from order_data")
        .fetch_all(pool)
        .await {
        Ok(order_ids) => {
            Some(order_ids)
        }
        Err(e) => {
            error!("Couldn't fetch order ids! {}", e);
            None
        }
    };
}

As a side note, one could also implement it in a way to return Result instead of Option. I prefer to handle errors in a place where they occur, thus I transform Result into Option here

score 0 · Answer 2 · answered Jun 04 '23 at 10:16

You can put #[sqlx(default)] for order_id and created_on in your struct. With that, if you do not select the field in your query, the default value would be used (for Option, default would be None)

#[derive(FromRow)]
struct OrderDb {
    #[sqlx(default)]
    order_id: Option<i32>,
    #[sqlx(default)]
    created_on: Option<NaiveDateTime>,
}

see: https://docs.rs/sqlx/latest/sqlx/trait.FromRow.html#default

MR NOBODY · Answer 3 · 2023-04-05T12:09:27.773

You may need to create new struct for this and you can map to Tags

// original struct 
#[derive(Debug, Serialize, Deserialize, FromRow)]
pub struct Tags {
    pub uuid: Option<Uuid>,
    pub name: Option<String>,
    pub created_at: Option<DateTime<chrono::Utc>>,
}


// this struct to get only uuid from sql
#[derive(Debug, Serialize, Deserialize, FromRow)]
pub struct TestTags {
    pub uuid: Uuid,
}

pub async fn exists(
    name: String, 
    conn: &mut PgConnection,
) -> Result<Tags, sqlx::Error> {
    let query_str = r#"
    SELECT uuid FROM tags
    WHERE name = $1
    "#;
    let tag = sqlx::query_as
        ::<_, TestTags>(query_str)
            .bind(name)
            .fetch_one(conn)
            .await
            .map(|tag| {
                Tags {uuid: tag.uuid, name: None, created_at: None}
            });
            // .map_err(|e| {
            //     OurError::from_sqlx_error(e)
            // });
    tag

}

Yes I know that, but I didn't want to create a separate struct for each possible combination of fields — Enigo, Apr 05 '23 at 13:25

How to use sqlx query_as to fetch some of the model fields

3 Answers3