perdumonocle / sql-builder Goto Github PK

Hi, how to do the following:

let value = Some("Hard".to_string());
let query = SqlBuilder::select_from("users")
    .field("name")
    .field("surname")
    .and_where("surname = ?".bind(&"John"));
if let Some(surname) = value {
    query.and_where("surname = ?".bind(&surname));
}
let res = query.sql()?;

Is it possible?

Hey, can we add a function for postgres array text, something similar or better than this?

use serde::Serialize;
    use sql_builder::quote;
    use std::fmt::Display;

    pub fn pg_array<T: Display + Serialize>(value: &[T]) -> String {
        quote(
            serde_json::to_string(value)
                .expect("Failed to serialize value")
                .replace("[", "{")
                .replace("]", "}"),
        )
    }

E.g. where column like '%\%d%' escape '\' to match any string with the %d substring.

https://www.sqlite.org/lang_expr.html#the_like_glob_regexp_match_and_extract_operators

Hi, I've been trying to find the proper input to make a query for some columns where a timestamptz field is less than today.

For the column field DATE() SQL function works properly, but for value what should be the input?

let now: DateTime<Local> = Local::now();
let generated_query = &base_select_statement
                .and_where_le("DATE(duedate)",&("TO_DATE(**?????**)"))

Anyone already took care of this scenario?

Is there now (or in the future) UUID support or should I just convert to a string?

pub async fn find_by_id(ctx: &Context<'_>, id: Uuid) -> Result<Option<Organization>> {
    let pool = ctx.data::<PgPool>()?;

    let sql = SqlBuilder::select_from(ORGANIZATION_TABLE_NAME)
        .field("*")
        // Right now I need to convert to a string
        .and_where("id = $1".bind(&id.to_string()))
        .sql()?;

    Ok(sqlx::query_as::<_, Organization>(sql.as_str())
        .fetch_optional(pool)
        .await?)
}

Also, would you recommend using .and_where("id = $1".bind(&SqlName::new(id).safe())) to prevent sql injection since the id is passed from the client or is my understanding of SqlName incorrect?

Hi,

first, thank you for this great crate!

It would be nice to have support for bound values, i.e. statements with ? or $1. So it would benefit from DB statement caching.

Happy to submit a MR PR for this tomorrow if you'd like! (EDIT: Sorry, used to Gitlab!)

It would be nice to have support for common table expressions: https://www.postgresql.org/docs/13/queries-with.html

Are there any plans to include this functionality in a future version? Would be willing to contribute as well.

I want to construct a query that looks as following:

SELECT * FROM table WHERE field1 = '' OR (field2 = '' AND field3 = '');

The code below does not work for obvious reasons:

use sql_builder::{SqlBuilder, quote};

let sql = SqlBuilder::select_from("table")
    .field("*")
    .and_where_eq("field1", quote(""))
    .or_where_eq("field2", quote(""))
    .and_where_eq("field3", quote(""))
    .sql()?;

But I can't find a construct in the docs which would allow me to express this query.
It would be ideal if I would just be able to encapsulate the last two where clause statements within ()?
Note: The data for this query is being dynamically build by some dynamic filter data from user input, similar to as shown below.

enum Filter {
    Field1(StringFilter),
    Field2(StringFilter),
    Field2(StringFilter),
    And(Vec<Filter>),
    Or(Vec<Filter>),
}