Does this crate provide feature of conditional filtering for probable Option<None> variables?

[mxchist]

I have the code using vanila sqlx (not the sqlx-conditional):

use sqlx::PgPool;
use tokio::task;
use tracing::info;
use crate::Rws;

...
#[derive(Debug)]
#[derive(sqlx::FromRow)]
struct Rws {
    c: Option<String>
}

let pg_pool = match PgPool::connect(&connection_string).await;

and then

let a: Option<i32> = ...;
let b: Option<i32> = ...;

let row: Vec<Rws> = sqlx::query_as!(
    Rws,
    "select c
from public.table1
where 1 =1
and a = $1
and b = $2",
    a,  
    b   
)
.fetch_all(&pg_pool)
.await.unwrap();

I need the query above to be executed the next way:

• if a is Some(1) and b is None, then the DB connection crate should to send the next query:

select c
from public.table1
where 1 =1
and a = 1

• if a is Some(2) and b is `Some(3), then the DB connection crate should to send the next query:

select c
from public.table1
where 1 =1
and a = 2
and b = 3

• if both a and b are None, then the DB connection crate should to send the next query (perhaps without the where part):

select c
from public.table1
where 1 =1

So, I have the questions:

• Does the conditional_query_as! macros (or any other function in your crate) have a such feature?
• If it have, can you briefly sketch how to build a such query using your crate API?

[mxchist]

Important note: there is not known at the compile time which option will have both a and b. They get their values during runtime from an API

[kyrias]

You can do that with SQLx itself:

SELECT c
FROM table1
WHERE
    1 = 1
    AND (
        a IS NULL
        OR a = $1
    )
    AND (
        b IS NULL
        OR b = $2
    )

The one downside is that PostgreSQL doesn't have the the opportunity to optimize the query as well, but in a lot of cases that's good enough, and I would generally suggest going with the simpler option.

But you can certainly do this with sqlx-conditional-queries as well:

    let row: Vec<Rws> = sqlx_conditional_queries::conditional_query_as!(
        Rws,
        r#"
            SELECT c
            FROM table1
            WHERE
                1 = 1
                {#a}
                {#b}
        "#,
        #a = match a {
            Some(_) => "AND a = {a}",
            None => "",
        },
        #b = match b {
            Some(_) => "AND b = {b}",
            None => "",
        },
    )

or

    let row: Vec<Rws> = sqlx_conditional_queries::conditional_query_as!(
        Rws,
        r#"
            SELECT c
            FROM table1
            {#wc}
        "#,
        #wc = match (a, b) {
            (Some(_), None) => r#"
                WHERE a = {a}
            "#,
            (Some(_), Some(_)) => r#"
                WHERE
                    a = {a}
                    AND b = {b}
            "#,
            (None, Some(_)) => r#"
                WHERE b = {b}
            "#,
            (None, None) => "",
        },
    )

Both of those options are makes for harder-to-read queries though, so I would recommend avoiding them where possible.

[mxchist]

You can do that with SQLx itself:
...
The one downside is that PostgreSQL doesn't have the the opportunity to optimize the query as well, but in a lot of cases that's good enough, and I would generally suggest going with the simpler option.

The another downside for me is that I need to tinker with bulky and ugly SQL query. I wish to move as much as possible such sophisticated operations on the Rust side, not to split logic between two languages and two applications levels. As for me, this is more elegant approach.

If I not find such tool, I will use your way

[kyrias]

Thank for your answer. If I don't miss understand, this work only at compile time, and not possible to do in runtime (according to the doc), right? If I don't know the values of a and b at the compile time, this will not work.

It generates four query versions at compile time which are selected between at runtime.

[mxchist]

Sounds great! Thanks, I'll try it.

[mxchist]

...
But you can certainly do this with sqlx-conditional-queries as well:

    let row: Vec<Rws> = sqlx_conditional_queries::conditional_query_as!(
        Rws,
        r#"
            SELECT c
            FROM table1
            WHERE
                1 = 1
                {#a}
                {#b}
        "#,
        #a = match a {
            Some(_) => "AND a = {a}",
            None => "",
        },
        #b = match b {
            Some(_) => "AND b = {b}",
            None => "",
        },
    )

or
...

The compiler is experiencing troubles with handling Option arguments in your first example:
through_conditional.rs:

  1 use sqlx::PgPool;
  2 use crate::Rws;
  3 
  4 pub async fn example_of_kyrias(pg_pool_ref: &PgPool) {
  5 
  6     tokio::spawn(async move {
  7         let a: i32 = 1;
  8         let b: Option<i32> = None;
  9 
 10         let row: Vec<Rws> = sqlx_conditional_queries::conditional_query_as!(
 11             Rws,
 12             r#"
 13                 SELECT c
 14                 FROM table1
 15                 WHERE
 16                     1 = 1
 17                     {#a}
 18                     {#b}
 19             "#,
 20             #a = match a {
 21                 Some(_) => "AND a = {a}",
 22                 None => "",
 23             },
 24             #b = match b {
 25                 Some(_) => "AND b = {b}",
 26                 None => "",
 27             }
 28         )
 29         .fetch_all(pg_pool_ref)
 30         .await.unwrap();
 31 
 32     });
 33 }

Another project files

main.rs:

use std::env;
use sqlx::PgPool;

mod through_conditional;

#[derive(Debug)]
#[derive(sqlx::FromRow)]
struct Rws {
    c: Option<String>
}

#[tokio::main]
async fn run_async_tasks() {

    let database_url = match env::var("DATABASE_URL") {
        Ok(connection_string) => connection_string,
        Err(error) => panic!("{}, {}", error, "DATABASE_URL"),
    };

    let pg_pool = match PgPool::connect(&database_url).await {
        Ok(pool) => pool,
        Err(err) => panic!("{}", err),
    };
    through_conditional::example_of_kyrias(&pg_pool).await;

}

fn main() {
        run_async_tasks()
}

Cargo.toml

[package]
name = "estate_aggregator_telegram_bot"
version = "0.2.1"
edition = "2021"

[dependencies]
rust-i18n = "3.1.2"
tokio = { version = "1.40.0", features = ["macros", "rt", "rt-multi-thread", "sync", "time"] }
serde = { version = "1.0.210", features = ["derive"] }
sqlx = { version = "0.8.2", features = ["runtime-tokio", "postgres", "tls-rustls", "chrono", "macros"] }
futures = "0.3.31"
sqlx-core = "0.8.2"
openssl = { version = "0.10.68", features = ["vendored"] }
tracing = "0.1.40"
tracing-subscriber = { version = "0.3.18", features = ["std", "fmt"] }
chrono = "0.4.39"
sqlx-conditional-queries = { version = "0.3.1", features = ["postgres"] }

Then preparing statement from command line:
$ cargo sqlx prepare
And I get:

error[E0308]: mismatched types
  --> src/through_conditional.rs:21:17
   |
10 |           let row: Vec<Rws> = sqlx_conditional_queries::conditional_query_as!(
   |  _____________________________-
11 | |             Rws,
12 | |             r#"
13 | |                 SELECT c
...  |
21 | |                 Some(_) => "AND a = {a}",
   | |                 ^^^^^^^ expected `i32`, found `Option<_>`
...  |
27 | |             }
28 | |         )
   | |_________- this expression has type `(i32, std::option::Option<i32>)`
   |
   = note: expected type `i32`
              found enum `std::option::Option<_>`

error[E0308]: mismatched types
  --> src/through_conditional.rs:22:17
   |
10 |           let row: Vec<Rws> = sqlx_conditional_queries::conditional_query_as!(
   |  _____________________________-
11 | |             Rws,
12 | |             r#"
13 | |                 SELECT c
...  |
22 | |                 None => "",
   | |                 ^^^^ expected `i32`, found `Option<_>`
...  |
27 | |             }
28 | |         )
   | |_________- this expression has type `(i32, std::option::Option<i32>)`
   |
   = note: expected type `i32`
              found enum `std::option::Option<_>`

For more information about this error, try `rustc --explain E0308`.
warning: `test_project` (bin "test_project") generated 4 warnings
error: could not compile `test_project` (bin "test_project") due to 2 previous errors; 4 warnings emitted
error: `cargo check` failed with status: exit status: 101

The cargo check results with the same outcome. Looks like it cannot handle both Some() and None variants of the same option inside a match
What am I doing wrong?

[kyrias]

You said that both a and b should be Options but your a variable is just a plain i32, which as per the error message cannot be matched on against Some(_).

[mxchist]

Isn't is possible to bind variables using tuples? I mean, such code:

        r#"
            SELECT
            ...
        "#,
        #a = match a {
            Some(_) => "AND a = {a}",
            None => "",
        },
        #b = match b {
            Some(_) => "AND b = {b}",
            None => "",
        },

written in this manner:

        r#"
            SELECT
            ...
        "#,
        (#a, #b) = match some_condition {
            Some(_) => ("AND a = {a}","AND b = {b}"),
            None => ("", ""),
        },

I got the error:

expected `#`
   --> src/through_conditional.rs:338:9
    |
338 | ...   (#a, #b) = match condition...
    |       ^

[mxchist]

The similar question - is it possible to write down macros in the next manner?

sqlx_conditional_queries::conditional_query_as!(
...
r#"
    SELECT
    ... 
"#,
match some_condition {
    Some(_) => {
        #a = ...;
        #b = ...;
    },
    None =>...,
},
)

[kyrias]

1. The syntax is

       #(a, b) = match some_condition {
           Some(_) => ("AND a = {a}", "AND b = {b}"),
           None => ("", ""),
       },

   (C.f. https://docs.rs/sqlx-conditional-queries/latest/sqlx_conditional_queries/macro.conditional_query_as.html#examples, suggestions for improving the documentation would be appreciated.)

2. No, and I don't think that's going to change as it would be too complicated to translate cleanly and consistently.

[mxchist]

It looks I found a infinite recursion of something like this

The code:

#[derive(Debug)]
#[derive(sqlx::FromRow)]
struct Rws {
    c: Option<String>
}

#[derive(Clone, Deserialize, Default, PartialEq, Debug, sqlx::FromRow)]
pub struct Infrastructure {
    has_parking: bool,
    is_kindergarden_nearby: bool,
    is_school_nearby: bool,
}

...

    let row: Vec<Rws> = sqlx_conditional_queries::conditional_query_as!(
        Rws,
        r#"
            SELECT "some text"
            FROM table1
            INNER JOIN infrastructure USING (infrastructure.id)
            WHERE
                1 = 1
                {#a}
                {#b}
                {#infrastructure_filter}
        "#,
        #a = match a {
            Some(_) => "AND a = {a}",
            None => "",
        },
        #b = match b {
            Some(_) => "AND b = {b}",
            None => "",
        },
        #infrastructure_filter = match infrastructure.has_parking {
            true => "AND has_parking = true",
            _ => "", 
        },
        #infrastructure_filter = match infrastructure.is_kindergarden_nearby {
            true => "{#infrastructure_filter} AND is_kindergarden_nearby = true",
            _ => "", 
        },
    )

So, what I wanted to do above to do. I have a single logical step {#infrastructure_filter}, but it by itself could combine several conditions of the different fields of the same struct Infrastructure depending of its fields' values. I don't want to write down the statement in the next way (despite this way to write a statement for the macros look as the only right way):

WHERE
    1 = 1
    {#a}
    {#b}
    {#infrastructure_filter_parking}
    {#infrastructure_filter_kindergarden}
    {#infrastructure_filter_school}
"#

rather, I wanted use the only one compile time bound #infrastructure_filter, and construct it by referencing itself like "{#infrastructure_filter} AND next_condition". So, both the cargo check and cargo sqlx prepare don't throw any errors, but they stuck for infinite time on the step:

    Checking blahblah v0.1.0 (/tmp/blahblah)
    Building [=======================> ] 289/290: blahblah(bin) 

I suppose it will be better to throw an error when encounter such statement

[kyrias]

You can certainly do what you want, you just have to give each version a different name.

They can't refer to themselves as the bindings end up in a binding->literal hashmap that's expanded iteratively in a loop until there's nothing else to expand. It cannot be made to work since a compile-time binding can be used multiple times. It should be pretty easy to catch this and return an error in either the analyze or lower stages though.

---

Edit: Actually, while direct self-references would be easy to catch, something like the following would be less obvious:

#a = "{#b}",
#b = "{#a},

I think instead we should detect this in expand. If the expanded fragment list is unchanged after calling expand_compile_time_bindings we should throw an error.

[kyrias]

Could you test whether this throws the errors that you'd expect here? #26

[mxchist]

Could you test whether this throws the errors that you'd expect here? #26

So, the behavior before using a #catch-compile-time-binding-duplicates-and-cycles version:

    Checking blahblah v0.1.0 (/rust_source_code)
^C  Building [=======================> ] 339/340: blahblah(bin)

(infinite time to complete

    Checking blahblah v0.1.0 (/rust_source_code)
^C  Building [=======================> ] 339/340: blahblah(bin)

(infinite time to complete

I did the next:

• # cargo remove sqlx-conditional-queries
• # cargo add --git https://github.com/kyrias/sqlx-conditional-queries.git --branch catch-compile-time-binding-duplicates-and-cycles -F postgres sqlx-conditional-queries

And when I ran cargo sqlx prepare, I got the duplicate compile-time error, as intended!

error: found duplicate compile-time binding
   --> src/through_conditional.rs:106:14
    |
106 |             #infrastructure_filter = match is_kindergarden_nearby {
    |              ^^^^^^^^^^^^^^^^^^^^^

[kyrias]

Great stuff. I wish we could point to the previous one as well in the error but the API necessary for that is unstable. :(

[mxchist]

Isn't it possible to use nested blocks like a match inside another match?

let infrastructure = Some(
        Infrastructure {
        has_parking: false,
        is_kindergarden_nearby: true,
        is_school_nearby: false,
    }   
);

let row: Vec<Rws> = sqlx_conditional_queries::conditional_query_as!(
    Rws,
    r#" 
        SELECT "some text"
        FROM table1
        INNER JOIN infrastructure USING (infrastructure.id)
        WHERE
            1 = 1 
            {#a}
            {#b}
            {#infrastructure_filter}
    "#, 
    #a = match a { 
        Some(_) => "AND a = {a}",
        None => "", 
    },  
    #b = match b { 
        Some(_) => "AND b = {b}",
        None => "", 
    },  
    #infrastructure_filter = match infrastructure {
        Some(infrastructure) => match infrastructure.has_parking{
            true => "AND has_parking = true",
            false => "", 
        },  
        None => "", 
    },
    ...

Error: expected string literal, only string literals or tuples of string literals are supported in compile-time bindings

[kyrias]

It is not. The way compile-time arguments are implemented is that we loop over all of the bindings and build a vector of (pattern, string) for every match arm. I'm not sure how we'd reasonably be able to handle nested match expressions for this, especially since we in the end need to end up with a simple binding -> string mapping, which needs to be a compile time string literal.

For the specific case you quoted I have a feeling that the simpler option would be implementing support for match arm guards where you would instead phrase that as:

#infrastructure_filter = match infrastructure {
    Some(infrastructure) if infrastructure.has_parking() => "AND has_parking = true",
    _ => "',
},

syn parses this just fine, but the syn::Arm.guard field would need to be threaded through each step and then included in the codegen stage. I don't have the need for this so I don't think I'll get around to implementing it anytime soon, but I'd be happy to assist you in implementing it.

[kyrias]

As a workaround the above could be achieved by something like the following by the way:

let infrastructure_has_parking = infrastructure.map(|infra| infra.has_parking());
...
    r#"
        #infrastructure_filter = match infrastructure_has_parking {
            Some(true) => "AND has_parking = true",
            _ => "",
        },
    "#,

I'd recommend giving implementing guard support a shot though.

And I should note that you might want to be careful if you end up with multiple match infrastructure bindings. The example above would lead to 8 queries being generated. (2 * 2 * 2 match arms.) The query in #25 (comment) would bring you up to 16. A single additional two-arm binding and you're now up to 32 generated versions of the same query. From this macro's point of view that's not an issue, but since it outputs compile-time checked SQLx macro invocations each one ends up needing to talk to the database which could potentially end up having significant compile time performance impact.