How to optionally add WHERE clauses, to model REST APIs

[itsfarseen]

I have been working on this issue for a while and just stumbled upon a solution. Thought I'd share.

#[get("/posts?<start>&<n>&<location>&<item>")]
async fn posts(
    start: Option<i64>,
    n: Option<i64>,
    mut location: Option<String>,
    mut item: Option<String>,
    db: State<'_, PgPool>,
) -> MyRes<GetPosts, ()> {
    // surround with % for passing to LIKE operator.
    // Because neither `LIKE %$1%` nor `LIKE '%$1%'` work.
    location.as_mut().map(|s| {
        s.insert(0, '%');
        s.push('%');
    });
    item.as_mut().map(|s| {
        s.insert(0, '%');
        s.push('%');
    });

    let res = sqlx::query_as!(
        Post,
        r#"
        SELECT *
        FROM posts 
        WHERE ($3::text IS NULL OR location ILIKE $3) AND ($4::text IS NULL OR item ILIKE $4)
        OFFSET $1
        LIMIT $2
        "#,
        start,
        n,
        location,
        item
    )
    .fetch_all(&*db)
    .await;
   // .. snip ..
}

[itsfarseen]

Basically for WHERE clause I had to do $1::<pg datatype> IS NULL or <column> <operator> $1.
For OFFSET/LIMIT neither SQLx not Postgres seems to care that I'm passing an Option<T>. It seems to be working fine.

[mehcode]

Additionally you can look at https://docs.rs/ormx/0.7.0/ormx/macro.conditional_query_as.html