Filtering array type columns

[Brian Richardson]

Hi,

Is it possible to filter columns of array type? I can get them to display using a valueAccessor function, but I can't filter them in any way. Ideally I'd like to be able to specify the custom filter, then use that custom filter value to execute a custom query against the DataManager. DataManager seems to have this functionality (executeQuery()), but it doesn't seem that Filter.filterByColumn() provides anything more than primitive value types.

Example, since this probably isn't very clear:

Article has many Tags. Tag has Id and Name properties. Tags are displayed in a single table cell by using a valueAccessor to comma-separate them.

I'd like to be able to define a column filter on Tags using a DropDownList, such that an Article is included in the filter if any of the Tags have the Id selected in the DropDownList.

Thanks,

Brian

[Pavithra Subramaniyam]

Hi Brian, 

 

In Array type of columns the data Source for that columns will be in array of object format ( ex: 'Name': [{}, {}] ).  Could you please confirm whether you are using object data in the array like 'Name': [{ firstName:{…}}, {LastName:{…}}] for the array type of column in your filtering sample or provide more information on your requirement that will be helpful for us to check feasibilty. 

 

Regards, 

Pavithra S. 

[Brian Richardson]

Yes, I can confirm that this is the format of the Tags as returned from my OData service. It is a C# ICollection<Tag> that has been JSON-serialized.

[Pavithra Subramaniyam]

Hi Brian, 

Thanks for your update. We are analyzing your requirement(“Filtering support for columns of array type”) to provide solution. Could you please update following details that would be helpful for us to check the feasibility of your requirement? 

1. Share your oData version
2. Share your Web API version you are using.
3. Which type of filtering you are using for example filter bar, filter menu and Check box filter. Demo

Regards, 

Pavithra S. 

[Brian Richardson]

Hi Pavithra,

To answer your questions:

1. I am using OData v4

2. I am using WebAPI v2.2 for the ODataControllers. Additionally, I am using ASP.NET 4.7/MVC 5 for the Angular 2 application.

3. I am using menu filter option.

Regards,

Brian

[Pavithra Subramaniyam]

Hi Brian, 

We have considered your requirement “Filtering support for columns of array type ” as a custom sample and we will provide the sample on January 16, 2018. 

Regards, 

Pavithra S. 

[Brian Richardson]

I've worked a bit more on this and finally managed to get an OData "any" query to work. It's quite the hack, since I'm constrained by the existing design:

// ExtendedODataV4Adaptor

import { ODataV4Adaptor, DataUtil, Predicate, Query } from '@syncfusion/ej2-data';

export class ExtendedODataV4Adaptor extends ODataV4Adaptor {
    onEachWhere(predicate: Predicate, query: Query, requiresCast?: boolean): string {
        if (predicate.isComplex) {
            var child = predicate.predicates[0];
            // we've stuffed the collection/field combination into a pair of nested predicates.
            // it's totally wrong, but there's no other way to get through the SyncFusion code
            var operator = DataUtil.odBiOperator[predicate.operator];
            return predicate.field + '/any(x: x/' + child.field + operator + predicate.value + ')';
        }
        return this.onPredicate(predicate, query, requiresCast);
    }
}

// ExtendedQuery

import { Query, Predicate } from '@syncfusion/ej2-data';

export class ExtendedQuery extends Query {
    constructor(from: string) {
        super(from);
    }

    whereAny(collection: string, field: string, operator: string, value: string | number | boolean | Predicate | Date | Predicate[]) : ExtendedQuery {
        operator = operator.toLowerCase();
        var predicate = new Predicate(field, operator, value);
        var container = new Predicate(collection, operator, value);
        container.isComplex = true;
        container.predicates = [predicate];
        this.queries.push({ fn: 'onWhere', e: container });
        return this;
    }
}

// use it

this.query = new ExtendedQuery('Articles').whereAny('Tags', 'Id', 'equal', +this.tagId);

If this were to be implemented, I would suggest putting adding the whereAny() method to Query (and a corresponding whereAll()), and update ODataV4Adaptor to accept a Predicate that can generate the any/all queries. I've done my best given the existing constraints, and I'd rather not use this code since it's quite ugly. Please let me know if this is something you're likely to add in the future.

Thanks,

Brian

[Isuriya Rajan]

Hi Brian, 

This is the solution for your custom sample. Also We will consider this as feature “Filtering support for columns of array type” .This feature may be considered for any of the upcoming releases. 

Regards, 

Isuriya R 

[Michael Mairegger]

Thanks for this solution. It works. Are there any news about the state of including this feature?

[Thiyagu Subramani]

Hi Michael, 

Thanks for your update. 

By default in EJ2 Grid column only supports number, string, date, dateTime, Boolean, checkbox type values, and it will not the supports array type value. Refer to the below documentation.  

UG: https://ej2.syncfusion.com/documentation/api/grid/columnType/  

We can show array values in the Grid column using the valueAccessor API. If you are using columTemplate feature , we can iterating the array value from dataSource, then bind the value into the columnTemplate .   

But, the valueAccessor API only for the display purpose we cannot perform Grid actions like Filtering, Searching, Grouping, Sorting, etc., to this column(when you are display the custom text through valueAccessor ).   

Because, the Grid can perform the actions (like sorting, grouping ,filtering) based on its dataSource value. This is the behavior of EJ2 Grid.  But by default we can filter multiple values in single column in current Syncfusion version. 

Link : https://ej2.syncfusion.com/demos/#/material/grid/filter-menu.html 

Please get back to us, if you need any further assistance.  

Regards, 

Thiyagu S