Filtering by calculated column values within a row?

[ksaadDE]

I just stumbled over a interesting issue and use-case, which I'd like to share and hear your suggestions how to implement it properly.

The table has the following pseudo SQL + Diesel structure:

struct myObj:
  ...
  a:bool
  b:bool
  c:datetime

then I am running that tiny set of funcs doing this:

use chrono::{Utc, NaiveDateTime};

pub fn get_current_naive_date_time() -> NaiveDateTime
{
    Utc::now().naive_utc()
}

fn __has_c_reached (row: &myObj) -> bool
{
    get_current_naive_date_time() >=  row.c
}

fn __short_before_c_reached (row: &myObj) -> bool
{
    get_current_naive_date_time() >=  row.c - Duration::days(7)
}

// this is the calculation !!
fn __get_row_calcfield_numeric (row: &myObj) -> i32
{
        match (row.a, row.b,  __short_before_c_reached(&row),  __has_c_reached(&row) )
        {
            (_,_,true,false)    => 1,   // __short_before_c_reached (c1)
            (_, _,_,true)       => 6 ,  // __has_c_reached (c2)
            (true, true,_,_)    => 2,   // a && b
            (true, false,_,_)   => 0,   // a && !b
            (false, true,_,_)   => 4,   // !a && b
            (false, false,_,_)  => 5    // !a && !a
        }
}

So what I am doing basically is calculating an new i32 out of a,b,c. It shows it is really dependent on the values a,b and c. Of course, I could write that using filters and select. So calculating based on those values, which however does not return me the "calculated" numbers, which I need in the rest of the test project.

Tiny result table for __get_row_calcfield_numeric (kinda like a truth table just with numeric results)

A	B	C1	C2	R
0	0	0	0	5
0	0	0	1	6
0	0	1	1	6
0	1	1	1	4
1	1	1	0	1
1	1	0	0	2
1	0	0	0	0
1	1	1	1	2

As far as I know that would really suck hard when implementing it in SQL. Further implementing it three times at different places is probably not A+ level coding.

My first thought was to add a new field to the structure and make it nullable, and update it when the app is updating or pulling the rows. The problem is, this leads to untracked changes to a,b,c and an outdated new field. So this is not a solution for me.

My second thought was to use triggers and procedures, due to SQLite this is not only not feasible, the documentation is also kinda rare in this regard, and I do not want to make a whole research project out of this xD

Another connected approach using SQL if-clauses and virt fields results in something like that:

• https://stackoverflow.com/questions/75553721/calculate-a-value-with-multiple-columns-in-a-each-record
• https://stackoverflow.com/questions/30571528/calculated-column-in-sql-with-if-elif-else-condition

SELECT 
             CASE 
                  WHEN a = 0 AND b =0
                     THEN '5' 
                  WHEN NOW() => c 
                     THEN '6'
              END as a,b,c
FROM myobjs

I do not implement all of it here - just an example.
How could I implement that in diesel.rs without getting headaches ?

My third thought was to add some custom filter to the query to achieve the same I would on DB level. However, after searching a bit and looking into possible places in the docs, I have absolutely no clue how I could implement that. Another issue is: I need the resulting value, does not seem very feasible.

My fear is keeping it like above, so querying all rows of the table and "post-processing" them being kinda resource exhaustive. Although Rust is really fast. If I have 1000 entries and 10 users I would query them all (1000*10*req_amount) and post-process them in the Rust App instead of calculating the entries "on DB level". Though I think there is no other way to achieve it atm.

Ideally I would have an extra virtual field that I could just query to do a "give me all with calcVal = 2" like

SELECT xyz FROM myobjs WHERE calcVal=2

just with myjobs.filter(...).select(...) in diesl.rs

In other frameworks we have the possibility to use virtual fields or lets say custom fields, to process and store those specific calculations and apply them in filters e.g.:

pub fn myfilterfunc (row: &myObj) -> bool
{
    (__get_row_calcfield_numeric (&row)) == 6) // if calcfield_numeric returns 6 -> return true
}

Ok::<Vec<Task>, Error>(
 myjobs.filter(
     deleted.eq(false)
  ).customFilter(myfilterfunc).select(myObj::as_select()).load(con).unwrap()
)

This would do the post-processing much easier and at one place using one-time coded funcs. However, this is obviously not the ideal solution either.

Keep in mind it: While could be certainly a business project, as I am pretty sure this is a common use-case when implementing business-logic, this is a playground project - still trying to get used to diesel.rs's workflow.

Any ideas? How would you guys solve this? Has Diesel.rs some utilities or a specific methodology to solve issues like that much easier? I would be wondering if nobody thought about this before, as I have seen that a lot out there.

[weiznich]

I'm sorry, but I cannot follow what you exactly want to achieve. It's really hard to follow your question as it mixes quite a lot of stuff. Try to focus on one question at the time and try to provide specific example of the problem you want to solve. Do not try to build a generic example, just use your real code as that usually makes it much easier to follow such questions.

[ksaadDE]

Eh, interesting reaction! How does it feel when being faced with oneself?

spend a similar amount of effort for answering a question

Clearly not, please look at my initial question and take a step back.

I also indicate which parts of the documentation sound helpful for my problem and I include information about what I've already tried so far if I ask question

Thank you for being honest! So why are you trying to tell me to read it full, when you are not doing it either? We both are doing "selective reading" for efficiency reasons and if we can not find what we need, we'd rather say that the docs are troublesome.

if you do not provide code

I see 6 complete examples

Very complete! ;)

payed consulting instead

Why should I? You do not provide reasons to hire or pay you. First of all it is still my hobby project for getting used to Diesel, trying to see if it is worth using in production, which I am questioning for now. Your continuous insults are a no-no for business. Further you are not showing excellency, instead you are just reverting to insults and "read the docs" comments. Last but not least you are ignoring the effort that has been put into documenting the problem, use-case and my structured approach to it.

Are you living the community spirit or are you just trying to pressure us into your consultancy service? 🤔

[ksaadDE]

I actually expected some indication of what direction I could go or what you are absolutely not recommending. I mean you are using Diesel yourself, you probably have solved that issue yourself multiple times, as it's really basic and common.

[weiznich]

I recommend that you take a step back and reconsider your tone. After that reread my response again, I already provided these directions by linking the relevant documentation.

Also a word of warning: If you continue this demanding tone here and continue to insult me I will just ban you from interacting with this project. Consider this as last warning.

[ksaadDE]

@weiznich If you accuse me of having a demanding tone, please prove it. In my opinion I am very soft and friendly towards you, despite your reply was just "read the docs", "I do not understand", and "buy my service [if you are too stupid to fix it alone]". Then I tried to kindly explain why I am not willing to give you my money and pointing out the community spirit. Yet you threatening me again. It looks like you are arrogant, aggressive, degrading and threatening when confronted with valid and sound critique. I am surprised that you did not at the beginning because your tone was always very rude and very unwelcoming.

Just let me know if you are ready to focus the initial question instead of your problems.

[weiznich]

I'm not interested in continuing this discussion, as it's not productive. I will lock this thread now. Feel free to open a new discussion if you feel that your question is not answered yet, but keep in mind to include at least the following information:

• A minimal reproducible example of the code in question
• A list of documentation you read
• General information about your setup (database system, operating system, rust version, …)