What nested filter format Loopback application should support?

Question

In the LoopBack application we need to develop a generic logic to parse the where filter provided to the application to convert that where filter into endpoint supported query format.

We are checking on what complex/nested filter formats loopback supports.

While checking for it we found the Where Builder class available in LoopBack 4. We checked it to know how it prepares where filter for complex/nested filters. However, we couldn't conclude using it. Please find below our understanding and queries on few clauses we tried:

Points to consider when going through our analysis mentioned below:

1. We explored Where Builder to know loopback standards (considering Where builder must be creating complex/nested filter as per LoopBack standards, which loopback app should supports)
2. In each analysis point below in last point which read From this filter query can be formed: we have added representation of condition and not formed the query using properties key and value mentioned in filter and also updated filter generated by where builder with and1, and2, or1, or2 etc to make query readable.
3. In our connector application we get where filter (we test our app using Postman as well), we get whatever filter user provides, but we need to know what complex/nested filter we should support as a standard LoopBack application.

Analysis of different clause we tried using Where Builder:

1. For clause below:

const where = whereBuilderObj
    .eq('a', 1)
    .and({x: 'x'}, {y: {gt: 1}})
    .and({b: 'b'}, {c: {lt: 1}})
    .or({d: 'd'}, {e: {neq: 1}})
    .build();

It created where filter as below:

{
    "and": [
        {
            "a": 1,
            "and1": [
                {
                    "x": "x"
                },
                {
                    "y": {
                        "gt": 1
                    }
                }
            ]
        },
        {
            "and2": [
                {
                    "b": "b"
                },
                {
                    "c": {
                        "lt": 1
                    }
                }
            ]
        }
    ],
    "or": [
        {
            "d": "d"
        },
        {
            "e": {
                "neq": 1
            }
        }
    ]
}

From this filter query can be formed: a === 1 && and1_Condition && and2_Condition || or_Condition

2. For clause below:

const where = whereBuilderObj
  .eq('a', 1)
  .and({x: 'x'}, {y: {gt: 1}})
  .or({l: 'l1'}, {m: {neq: 2}})
  .and({b: 'b'}, {c: {lt: 1}})
  .or({d: 'd'}, {e: {neq: 1}})
  .build();

It created where filter as below:

{
    "and": [
        {
            "a": 1,
            "and1": [
                {
                    "x": "x"
                },
                {
                    "y": {
                        "gt": 1
                    }
                }
            ],
            "or1": [
                {
                    "l": "l1"
                },
                {
                    "m": {
                        "neq": 2
                    }
                }
            ]
        },
        {
            "and2": [
                {
                    "b": "b"
                },
                {
                    "c": {
                        "lt": 1
                    }
                }
            ]
        }
    ],
    "or": [
        {
            "d": "d"
        },
        {
            "e": {
                "neq": 1
            }
        }
    ]
}

From this filter query can be formed: a === 1 && and1_Condition && or1_Condition && and2_Condition || or_Condition

3. For clause below:

const where = whereBuilderObj
  .eq('a', 1)
  .and({x: 'x'}, {y: {gt: 1}})
  .or({l: 'l1'}, {m: {neq: 2}})
  .and({b: 'b'}, {c: {lt: 1}})
  .or({d: 'd'}, {e: {neq: 1}})
  .neq('az', 1)
  .build(); 

It created where filter as below:

{
    "and": [
        {
            "a": 1,
            "and1": [
                {
                    "x": "x"
                },
                {
                    "y": {
                        "gt": 1
                    }
                }
            ],
            "or1": [
                {
                    "l": "l1"
                },
                {
                    "m": {
                        "neq": 2
                    }
                }
            ]
        },
        {
            "and2": [
                {
                    "b": "b"
                },
                {
                    "c": {
                        "lt": 1
                    }
                }
            ]
        }
    ],
    "or": [
        {
            "d": "d"
        },
        {
            "e": {
                "neq": 1
            }
        }
    ],
    "az": {
        "neq": 1
    }
}

What is the correct query should get formed using this above filter? We tried to form like - a ===1 && and1_Condition && or1_Condition && and2_Condition || or_Condition ?? az !=1 but didn't get which filter clause/operator we should use for az property (hence added ??). This query is from analysis we did by using WhereBuilder, but at the end we need to know what complex/nested filter we should support as a standard LoopBack application.

Could someone please help us on our queries. Thank you.

Answer 1

@Yash, I am not sure what you mean when you say we need to know what complex/nested filter we should support as a standard LoopBack application. I imagine that would be an engineering decision made by your team. However, you can see what the queries generated by Loopback by adding the debug string in your environment e.g.

$ DEBUG=loopback:datasource,loopback:connector:* yarn start

This will print the debug strings including the SQL query that was executed.