rustrust-sqlx

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


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.


Solution

  • 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