@xuyuanme it is indeed expected to be a "AND". We need to come up with a new syntax for the "OR" in my opinion, otherwise we risk some breaking changes

Well received! I think we do need an "OR" syntax, otherwise in case described in #1257, it results a bug of empty data frame and chart.

@xuyuanme agreed.

This is the currently accepted value:

"where": [
            # this is optional and filters the data to
            # reduce the size of the dataframe
            ["loan_status", "=", "PAIDOFF"],
],

I suggest to add an option to accept an object in the where clause, something like this:

"where": {
    "operator": "AND",
    "conditions": [
        {"column": "loan_status", "operator": "=", "value": "PAIDOFF"},
        {
            "operator": "OR",
            "conditions": [
                {"column": "loan_amount", "operator": ">", "value": 3000},
                {"column": "loan_amount", "operator": "<", "value": 1500}
            ]
        }
    ]
}

Of course we should keep supporting the old way as well.

What's your take? Any alternative syntax?

Thanks @gventuri . I think the syntax itself should work. One problem is, the conditions (in #1257) are generated from self._additional_filters, instead of self.config.where.

self._additional_filters are created by function _extract_filters() in code_execution.py, which returns:

        Returns:
            dict: The dictionary containing all filters parsed from
                the passed code. The dictionary has the following structure:
                {
                    "<df_number>": [
                        ("<left_operand>", "<operator>", "<right_operand>")
                    ]
                }

This code does not read user config to generate the sql. So it does not have a place to config whether it's "AND" or "OR". Any thought on how this part should be handled?

@xuyuanme I recommend in that case to pass in the configs "direct_sql": True.
In this way, PandasAI will generate directly the SQL query with the more appropriate OR!

Ok let me do more tests against it. Thanks for the guide!

Close this PR.