Filter result by columns in relations fields

Schema & Data
1

Hi.
I have two related tables: game and game_status. there are related by game.id and game_status.game_id. When I query table with filter by game columns I got correct result with related info. I found in your documentation that I can show only desired fields from relation with uri parameters <relation_name>.fields=x,y.
My question is:
How can I set filter only on relation information. In other words: I want to see data from game table (according to filter on game table), but in relation data I want only records by filter on relation data, for example only where game_status.columnX = ‘XYZ’.

Thank for advices,
Rado.

2

Hi @radodr, here’s a shot at accomplishing what you’re looking for using a server-side script triggered on db.game.get.post_process. Let me know if this works for you.

var parent_key = null, child_key = null;
var child_filter = "";
var child_table = null;
var child_records = null;
var filter_string = "";

if (event && event.request && (query = event.request.query)) {
    
    if (query.hasOwnProperty("parent_key")) {
        parent_key = query.parent_key;
    }
    if (query.hasOwnProperty("child_key")) {
        child_key = query.child_key;
    }
    if (query.hasOwnProperty("child_filter")) {
        child_filter = query.child_filter;
    }
    if (query.hasOwnProperty("child_table")) {
        child_table = query.child_table;
    }
}

if (parent_key && child_key && child_filter && child_table) {
    // build filter for child key
    _.each(event.response.record, function(record) {
        if (filter_string !== "") {
            filter_string += " or ";
        }
        filter_string += child_key + "=" + record[parent_key];
    });
    if (filter_string !== "") {
        // add in child filter
        filter_string = "(" + filter_string + ")";
        filter_string += " and " + child_filter;
        child_records = platform.api.get("db/" + child_table, {"filter": filter_string});
        if (child_records.hasOwnProperty("error")) {
            throw("Error retrieving child records.");
        }
        // add children to parent data
        _.each(event.response.record, function(record) {
            record.children = [];
            _.each(child_records.record, function(child) {
                if (child[child_key] === record[parent_key]) {
                    record.children.push(child);
                }
            });
        });
    }
}

It can be triggered like this:

GET /rest/db/game?filter=name%3D%27pong%27&parent_key=id&child_key=game_id&child_table=game_status&child_filter=columnX%3D%27XYZ%27

Note that, URL-encoded, %3D is ‘=’ and %27 is a single quote.