Query the DB Dynamically

General Q&A
1

Hi Guys

I have multiple potential filters I need to apply to a query, but any one, multiple, all or none of the filters can be active at any point in time. This means that I have several permutations of potentials DB queries I could have to run depending on which filters the user actually has active at any point in time.

Is there a way I can dynamically create the my DB Query without physically having to hardcode every possible permutation?

1 Like
2

Hi

Yes, this is possible. You can dynamically generate the 2 parts of the query syntax and then combine them at the end.

DB query syntax is basically as follows DB.model.where(queryString, queryParameters), where the queryString is the query string (yo dawg) and the query parameters is a list / collection of parameters that are going to be passed into the query string in order. For example DB.asset.where('model = ? and status = ?', 'ProMatrix', 'Available'), where queryString: 'model = ? and status = ?' and queryParameters: 'ProMatrix', 'Available'.

So, the way you can generate and apply them dynamically is as follows

function searchAssets() {
    if (!view.selected_district && !view.selected_product && !view.selected_size && !view.status) {
        // You can update the below logic to handle this case as well, but I just decided to have a separate query for querying with no filters
        searchAll();
    } else {
        var queryString = "";
        var queryParams = [];
        if (view.selected_district) {
            queryString += "location_district_id = ?"
            queryParams.push(view.selected_district.id.toString());
        }
        if (view.selected_product) {
            queryString = queryString ? queryString + " and " : queryString
            queryString += "mstr_product_id = ?";
            queryParams.push(view.selected_product.id.toString());
        }
        if (view.selected_size) {
            queryString = queryString ? queryString + " and " : queryString
            queryString += "mstr_size_id = ?";
            queryParams.push(view.selected_size.id.toString());
        }
        if (view.status) {
            queryString = queryString ? queryString + " and " : queryString
            queryString += "statusID = ?";
            queryParams.push(view.status);
        }
        var args = [];
        args.push(queryString);
        args = args.concat(queryParams);
        view.filtered_assets = DB.asset.where.apply(DB.asset, args);
    }
}

function searchAll() {
    if (confirmDialog("Show All Assets?", "Are you sure you want to see all assets?", "YES", "NO")) {
        notification.info("Fetching all assets. Please wait ...");
        view.filtered_assets = DB.asset.all();
    }
}

In TS Apps and in CloudCode, where ES6 is supported, you can also make use of the spread operator (...) to simplify the syntax slightly in the final step and omit the apply() statement.

function searchAssetsTS() {
        var queryString = "";
        var queryParams = [];
        if (view.selected_district) {
            queryString += "location_district_id = ?"
            queryParams.push(view.selected_district.id.toString());
        }
        if (view.selected_product) {
            queryString = queryString ? queryString + " and " : queryString
            queryString += "mstr_product_id = ?";
            queryParams.push(view.selected_product.id.toString());
        }
        if (view.selected_size) {
            queryString = queryString ? queryString + " and " : queryString
            queryString += "mstr_size_id = ?";
            queryParams.push(view.selected_size.id.toString());
        }
        if (view.status) {
            queryString = queryString ? queryString + " and " : queryString
            queryString += "statusID = ?";
            queryParams.push(view.status);
        }
        view.filtered_assets = DB.asset.where(queryString, ...queryParams);
    }
}

Feel free to generate the queryString and queryParams variables as you see fit

1 Like