How can I switch mindsets from ORMs or query builders to projects like this?

[frederikhors]

I'm really fascinated by this idea you had to generate Rust code from sql files. Great!

What I can't understand after years of using "Query builders" and ORMs is how to get the same queries I'm getting using this pattern:

pub struct PlayerFilter {
    pub id: Option<IdFilter>,
    pub created_at: Option<DateTimeFilter>,
    pub created_by: Option<IdFilter>,
    pub state: Option<State>,
    pub number: Option<NumberFilter<i64>>,
    pub loading_date: Option<DateTimeFilter>,
    pub is_ok_playing_with_others: Option<BoolFilter>,
    pub for_teams: Option<BoolFilter>,
    pub weekend: Option<BoolFilter>,
    pub client_ref: Option<StringFilter>,
    pub amount: Option<DecimalFilter>,
    pub amount_with_surplus: Option<DecimalFilter>,
    pub tax_amount: Option<DecimalFilter>,
    pub profile_id: Option<IdFilter>,
    pub sale_id: Option<IdFilter>,
    pub player_type_id: Option<IdFilter>,
    pub and: Vec<Self>,
    pub or: Vec<Self>,
    pub not: Option<Box<Self>>,
    pub with_team: Option<TeamFilter>,
    pub with_coach: Option<CoachFilter>,
    // and many more
}

impl PlayerFilter {
    pub fn to_sql<'a>(
        &'a self,
        mut query: QueryBuilder<'a, sqlx::Postgres>,
    ) -> QueryBuilder<'a, sqlx::Postgres> {
        if let Some(id) = &self.id {
            id.to_sql("id", query);
        }

        if let Some(created_at) = &self.created_at {
            created_at.to_sql("created_at", query);
        }

        if let Some(created_by) = &self.created_by {
            created_by.to_sql("created_by", query);
        }

        // and many more
    }
}

this creates a big and complex and dynamic CTE (Common Table Expressions) query like this:

SELECT
  "player".*
FROM
  "player"
WHERE
  (
    "id" IN (SELECT "player_id" FROM "client_ref" WHERE "some_id" = $1)
    OR "id" IN (SELECT "player_id" FROM "skill" WHERE "some_id" = $2)
  ) AND (/* some other filter here */)
PLAYER BY
  "player".created_at DESC
  LIMIT 50 OFFSET 3

How can I switch from sqlx::QueryBuilder to clorinde?

I can't always know in advance which filters I'll need.

[dlight]

SQL is indeed anti-modular and non composable in a sense, which sucks. Generating strings with string interpolation might not be the best approach, and in any case it's not supported by Cornucopia.

However, SQL has some modularity in that it allows you to factor out common parts of queries into their own functions (and define those functions as part of your migrations). In special you could have functions that returns some condition that is put at an where clause, like this

--! my_query(param)
SELECT * 
FROM player 
WHERE something(onething, otherthing, :param)

And then, you define the function something using CASE.

What SQL doesn't have is a way to dispatch over multiple queries (building queries with string concatenation inside procedural function is terrible and doesn't count). So you really need to define all those functions and do dispatch on Rust side. Which is unfortunate. Really the library should help with this.

Note: while this project is inactive (last commit 2 years ago), development is continued on a fork, Clorinde.