Filtering a database query - need help

Wappler General Need Help
1

I’ve got a query that gets orders from my database.

Each order has ‘bought_products’ attached.

These products have an id.

Now I want to create the following filter:

  • Show me all orders where product 71 is bought
  • Then, exclude orders where product s71 && 72 have been bought

So effectively I’ll get a list of orders where ONLY 71 is bought, and 72 was NOT bought.

Here’s a visualisation:

image
image911×928 45.9 KB

How do I do this?
If I use the .where() formatter to only include orders that have id 71 I’ll not know if that order also had bought product 72.

I think I need the formatter “where does NOT contain”. But that doesn’t exist.
So then I think I need to use the .filter() formatter and create my own filter, but not sure how to use it.
image

If it helps, here’s the db query:

image
image1530×870 41.1 KB

3

I asked GitHub Copilot to solve this for you, the whereNot formatter for this use-case:

// Create a method that receives an array of objects, a property name and the respective value
// and returns all objects that don't have such property's value
exports.whereNot = function (arr, prop, value) {
    return arr.filter((item) => {
        return item[prop] !== value;
    });
}
4

Try

image
image1020×600 19.7 KB

5

@ben my issue with this is that if I apply those in the suitable, it’ll just filter away the sbutable records. But the actual order object will still show up. Just the bought_products array is empty

6

Got something working for now. I think a ‘cleaner’ way would be to improve the SQL query.

But I got this custom formatter.

Credits go to @Apple :smiley:

exports.wherePropertyIncludesAndExcludes = function (arr, property_name, sub_property_name, include, exclude) {
    return arr.filter((item) => {
        let includeMatch = false;
        let excludeMatch = false;
        // Check if any of the properties match the include criteria
        item[property_name].forEach((subElement) => {
            if (include.includes(subElement[sub_property_name])) {
                includeMatch = true;
            }
        });
        // Check if any of the properties match the exclude criteria
        item[property_name].forEach((subElement) => {
            if (exclude.includes(subElement[sub_property_name])) {
                excludeMatch = true;
            }
        });
        // Return true if includeMatch is true and excludeMatch is false
        return includeMatch && !excludeMatch;
    });
}
1 Like