Rust SQLX iLike '%$1%' - select by substring

Question

Good day! Could you tell please how tow make it works?

db: Postrgresql

iLike '%$1%' - doesn't works properly in rust sqlx crate, cant find in library working solution

const QUERY_SELECT_COUNTRY_BY_COUNTRY_NAME: &str = "SELECT * FROM dictionary_country WHERE name_en iLike '%$1%' ";

pub async fn get_country_list_by_country_name(tx: &mut Transaction<'static, Postgres>, country_name: &str) -> Result<Vec<CountryModel>, AppGenericError> {
    info!("get_country_list_by_country_name country_name = {}", &country_name);

    match sqlx::query(QUERY_SELECT_COUNTRY_BY_COUNTRY_NAME)
        .bind("Rus")
        .map(|row: PgRow| CountryModel::from(row))
        .fetch_all(&mut *tx)
        .await {
        Ok(data) => Ok(data),
        Err(err) => Err(AppRepositoryError::general_error(err.to_string()))
    }
}

expect to see 1 result row

UPDATED: working solution

const QUERY_SELECT_COUNTRY_BY_COUNTRY_NAME: &str = "SELECT * FROM dictionary_country WHERE name_en iLike $1 ";

pub async fn get_country_list_by_country_name(tx: &mut Transaction<'static, Postgres>, country_name: &str) -> Result<Vec<CountryModel>, AppGenericError> {
    info!("get_country_list_by_country_name country_name = {}", &country_name);

    match sqlx::query(QUERY_SELECT_COUNTRY_BY_COUNTRY_NAME)
        .bind(format!("%{}%", country_name))
        .map(|row: PgRow| CountryModel::from(row))
        .fetch_all(&mut *tx)
        .await {
        Ok(data) => Ok(data),
        Err(err) => Err(AppRepositoryError::general_error(err.to_string()))
    }
}

Have you tried putting the wrapping `%` in the bind instead? — PitaJ, Apr 26 '23 at 16:22

score 0 · Answer 1 · answered Apr 26 '23 at 16:39

You need to put the wrapping % in the bind instead:

const QUERY_SELECT_COUNTRY_BY_COUNTRY_NAME: &str = "SELECT * FROM dictionary_country WHERE name_en iLike $1 ";

pub async fn get_country_list_by_country_name(tx: &mut Transaction<'static, Postgres>, country_name: &str) -> Result<Vec<CountryModel>, AppGenericError> {
    info!("get_country_list_by_country_name country_name = {}", &country_name);

    match sqlx::query(QUERY_SELECT_COUNTRY_BY_COUNTRY_NAME)
        .bind(format!("%{}%", "Rus"))
        .map(|row: PgRow| CountryModel::from(row))
        .fetch_all(&mut *tx)
        .await {
        Ok(data) => Ok(data),
        Err(err) => Err(AppRepositoryError::general_error(err.to_string()))
    }
}

Rust SQLX iLike '%$1%' - select by substring

1 Answers1