Help with converting a Complex SQL query to Diesel

[Mohana-Manikandan]

For the past 4 hours, i have been trying to convert this my-sql query into diesel query

SELECT * FROM product_variant 
WHERE product_name="VU"
 AND ((var_type=1 and var_code="109") or (var_type=2 and var_code="55"));

The problem is var_code is being retrieved from another table and its an Option and the number of OR conditions is dynamic

I tried this one

    let product_code = String::from("VU");
    let mut query = product_variant::table.into_boxed().filter(product_variant::dsl::product_name.eq(product_code));
    let field_mappings = [
        (Some("109"), 1),
        (Some("55"), 2)
        ];

For the OR Conditions i know that a list of the following has to be added to an or_filter and add the resultant to filter

product_variant::dsl::var_type.eq(var_code)
.and(product_variant::dsl::var_code.eq(var_type))

But i can't seem to figure it out correctly.

[weiznich]

Could you add what you already tried, because everything in the code you showed looks correct so far and I would expect that to work.

[Mohana-Manikandan]

I gave this a try

let mut query = product_variant::table.into_boxed()
        .filter(product_variant::dsl::product_name.eq(product_name));
        
// This removes all the None values from the mappings
let field_mappings: Vec<(String, u16)> = field_mappings
        .iter()
        .filter(|(var_code, _)| var_code.is_some())
        .map(|(var_code, var_type)| (var_code.clone().unwrap(), *var_type))
        .collect();

// This gets all the individual Conditions 
// (var_type=1 and var_code="109")
// (var_type=2 and var_code="55")
let conditions = field_mappings
        .into_iter()
        .map(|(var_code, var_type)| {
            product_variant::dsl::var_code
                .eq(var_code)
                .and(product_variant::dsl::var_type.eq(var_type))
        })
        .collect::<Vec<_>>();

Now I am not sure how to flatten conditions with OR clauses in between & then to add it to the query above.

[weiznich]

The only thing that is missing is actually adding the conditions to the query. You can do that like this:

let mut query = product_variant::table.into_boxed()
        .filter(product_variant::dsl::product_name.eq(product_name));
        

query = field_mappings
        .iter()
         // This removes all the None values from the mappings
        .filter(|(var_code, _)| var_code.is_some())
        .map(|(var_code, var_type)| (var_code.clone().unwrap(), *var_type))
        // append each condition to the query
        .fold(query, |query, (var_code, var_type)| {
            query.or_filter(product_variant::dsl::var_code
                .eq(var_code)
                .and(product_variant::dsl::var_type.eq(var_type)))
        });

// do something with your query, e.g:

let res = query.load::<YourTargetType>(conn)?;

[Mohana-Manikandan]

@weiznich I tried this earlier but the conditions were being applied in a different manner which is logically correct.

Instead of this,

SELECT * FROM product_variant 
WHERE product_name="VU"
 AND ((var_type=1 and var_code="109") or (var_type=2 and var_code="55"));

I think we are applying like this

SELECT * FROM product_variant 
WHERE product_name="VU"
 OR ((var_type=1 and var_code="109") or (var_type=2 and var_code="55"));

Essentially, first line in the snippet you shared above, adds a product_name filter and then the iterations are adding an OR filter. I am stuck in figuring out how to wrap all the OR filters together with an AND filter.

[weiznich]

In that case you want to use BoxableExpression in combination with BoolExpressionMethods::or and QueryDsl::filter. You likely need to use an Option of the boxed type to guard against no elements.

I'm on currently writing this from my mobile, so it's hard to adjust the example, but hopefully it's clear from the documentation to get this working.

[Mohana-Manikandan]

Thank you. I was able to implement it this way.

let mut query = product_variant::table
    .into_boxed()
    .filter(product_variant::dsl::product_name.eq(product_name));

let inner_query: Option<Box<
    dyn BoxableExpression<product_variant::table, diesel::mysql::Mysql, SqlType = Bool>,
>> = field_mappings.iter().fold(None, |inner_query, (var_code, var_type)| {
    let condition = product_variant::dsl::var_code
        .eq(var_code.to_owned())
        .and(product_variant::dsl::var_type.eq(var_type));

    match inner_query {
        Some(query) => Some(Box::new(query.or(condition))),
        None => Some(Box::new(condition)),
    }
});

if let Some(inner_query) = inner_query {
    query = query.filter(inner_query);
}