Does anyone know how to use sqlx sqlite query "select" with "where in"?

[LeafWarrior]

I'm trying to retrieve specific data from my sqlite database. Please help, I've been trying to solve this for 2 days.

Now I'm starting to regret choosing tauri for my project. :( Simple queries which could have been easily done in php is so difficult in tauri/rust. The documentations are so bad and difficult to understand.

`
#[derive(Debug, Serialize, Deserialize, FromRow)]
struct Person {
cid: u32,
name: String,
address: String,
phonenum: String
}

async fn spec_get_cust(state: tauri::State<'_, AppState>, name: Vec<&str>) -> Result<Vec, String> {
let db = &state.db;

let cust: Vec<Person> = sqlx::query_as::<_, Person>("SELECT * FROM customer WHERE name in ?1">
    .bind(name)
    .fetch(db)
    .try_collect()
    .await
    .map_err(|e| format!("Failed to get customer {}", e))?;
Ok(cust)

}
`

[evanwmart]

Seems like the code pasted has a missing parenthesis ")" where:
let cust: Vec<Person> = sqlx::query_as::<_, Person>("SELECT * FROM customer WHERE name in ?1">
should probably be:
let cust: Vec<Person> = sqlx::query_as::<_, Person>("SELECT * FROM customer WHERE name in ?1")>

I notice you are trying to bind "name" where "name" is a Vec<&str>, you might want to try something like:

async fn spec_get_cust(state: tauri::State<'_, AppState>, names: Vec<&str>) -> Result<Vec<Person>, String> {
    let db = &state.db;

    if names.is_empty() { // probably good to check if there are any names before trying to do the query stuff
        return Ok(vec![]);
    }

    let placeholders = vec!["?"; names.len()].join(", ");
    let query = format!("SELECT * FROM customer WHERE name IN ({})", placeholders);

    let mut query_builder = sqlx::query_as::<_, Person>(&query);
    
    for name in &names {
        query_builder = query_builder.bind(name);
    }

    let cust = query_builder
        .fetch_all(db)
        .await
        .map_err(|e| format!("Failed to get customer: {}", e))?;

    Ok(cust)
}

This would dynamically generate the query based on your input names, idk if it is optimal lol but I think if you wanted the same function signature it might work.

Also try looking at the query_as and bind docs

P.S. rust syntax and type checking can be tedious but don't don't let it sway you from how cool tauri is :)

[LeafWarrior]

Wow man. That worked. You are a hero. Thank you!

Yeah, I had the same problems with other programming languages/platforms too until I master them. Then I love them. Hopefully, I get to master tauri. :)