odata-v4 filter foreign key column

9 posts, 1 answers
  1. Greg
    Greg avatar
    6 posts
    Member since:
    Jan 2014

    Posted 05 Feb 2016 Link to this post

    When trying to filter the ProcessType column in the example below, I receive a 400 error.  If I change the field to ProcessTypeID from ProcessType the filter works but than the add/edit options do not work correctly.  I tried injecting a custom filter and received the same result.  I followed the api documentation to the GitHub Odata 4 with WebAPI controller example, downloaded and ran locally and get the same result.  Can someone point in the right direction on this?

     

    <div id="grid"></div>
     
    <script>
     
        $(function() {
     
            $("#grid").kendoGrid({
                dataSource: {
                    type: "odata-v4",
                    transport: {
                        read: {
                            url: "/odata/Processes",
                            data: {
                                $expand: "ProcessType"
                            }
                        },
                        update: {
                            url: function (data) {
                                return "/odata/Processes(" + data.ProcessID + ")";
                            }
                        },
                        create: {
                            url: "/odata/Processes?$expand=ProcessType"
                        }
                    },
                    schema: {
                        model: {
                            id: "ProcessID",
                            fields: {
                                ProcessID: { type: "number" },
                                Name: { type: "string" },
                                Description: { type: "string" },
                                ProcessTypeID: { type: "number" },
                                Enabled: { type: "boolean", defaultValue: true },
                                DDLFile: { type: "string" },
                                Type: { type: "string" },
                                Method: { type: "string" },
                                Parameter: { type: "string" },
                                ProcessType: { defaultValue: { ProcessTypeID: 1, Name: "Import" } }
                            }
                        }
                    },
                    requestEnd: function(e) {
                        if (e.type == "create") {
                            // Make a read request to expand Category. By default the OData controller doesn't expand on create.
                            this.read();
                        }
                    },
                    change: function(e) {
                        console.log(e.sender);
                    },
                    pageSize: 10,
                    serverFiltering: true,
                    serverPaging: true,
                    serverSorting: true
                },
                height: "485px",
                toolbar: ["create"],
                sortable: {
                    mode: "multiple",
                    allowUnsort: true
                },
                filterable: {
                    mode: "row"
                },
                resizable: true,
                scrollable: true,
                pageable: {
                    pageSizes: [10, 25, 50],
                    buttonCount: 5
                },
                columns: [
                    { command: "edit", width: 200 },
                    { field: "Name", width: 400 },
                    { field: "Description", width: 400 },
                    { field: "ProcessType", width: 300, title: "Process Type", template: "#: ProcessType ? ProcessType.Name : '' #", editor: processTypeEditor },
                    { field: "Enabled", width: 170 },
                    { field: "DLLFile", width: 400 },
                    { field: "Type", width: 400 },
                    { field: "Method", width: 400 },
                    { field: "Parameter", width: 400 }
                ],
                editable: "inline",
                edit: function (e) {
                    if (!e.model.isNew()) {
                        var processType = $(e.container.find('input[data-role="dropdownlist"]')).data("kendoDropDownList");
                        processType.enable(false);
     
                        e.container.find('input[name=Name]').attr('disabled', 'disabled').addClass('k-state-disabled');
                    }
                },
                save: function (e) {
                    e.model.set("ProcessTypeID", e.model.ProcessType.ProcessTypeID);
                }
            });
     
        });
     
        function processTypeEditor(container, options) {
            console.log(options);
            $('<input required data-text-field="Name" data-value-field="ProcessTypeID" data-bind="value:' + options.field + '"/>')
                .appendTo(container)
                .kendoDropDownList({
                    autoBind: false,
                    dataSource: {
                        type: "odata-v4",
                        transport: {
                            read: {
                                url: "/odata/ProcessTypes"
                            }
                        }
                    }
                });
        }
     
    </script>

  2. Greg
    Greg avatar
    6 posts
    Member since:
    Jan 2014

    Posted 05 Feb 2016 in reply to Greg Link to this post

    Should add:

     <package id="Telerik.UI.for.AspNet.Mvc5" version="2016.1.112" targetFramework="net45" />  

     <package id="Microsoft.AspNet.OData" version="5.8.0" targetFramework="net45" />

     <package id="Microsoft.AspNet.Mvc" version="5.2.3" targetFramework="net45" />

    <package id="jQuery" version="2.2.0" targetFramework="net45" />

  3. Greg
    Greg avatar
    6 posts
    Member since:
    Jan 2014

    Posted 08 Feb 2016 in reply to Greg Link to this post

    bump
  4. Answer
    Rosen
    Admin
    Rosen avatar
    3253 posts

    Posted 11 Feb 2016 Link to this post

    Hello Greg,

    Most probably the cause for the server error is that the field on which filtering is made is an object. Instead it should be a plain type, such as number, string, bool etc. In order to correct this you may try binding the column to the 
    ProcessType.Name and modify the template to use ProcessType instead of the field name passed in via the options (which in this configuration will be the ProcessType.Name).

    Something similar to the following:

    { field: "ProcessType.Name", width: 300, title: "Process Type", template: "#: ProcessType ? ProcessType.Name : '' #", editor: processTypeEditor }
    $('<input required data-text-field="Name" data-value-field="ProcessTypeID" data-bind="value:ProcessType"/>')

    Please give it a try and see if it helps.

    Regards,
    Rosen
    Telerik
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
  5. Greg
    Greg avatar
    6 posts
    Member since:
    Jan 2014

    Posted 11 Feb 2016 in reply to Rosen Link to this post

    Hi Rosen,

     

    The creation or updating of records is not the issue.  That works as expected.  The problem is with the filtering of data.  Using 

    filterable: { mode: "row" }

    causes to filter with an autocomplete.  That throws this error as I type: GET http://localhost:30139/odata/Processes?%24format=json&%24expand=ProcessType&%24top=10&%24filter=ProcessType+eq+%27Ex%27&%24count=true 400 (Bad Request) 

    To get the dropdownlist effect, I changed the mode to menu and added 

    function processTypeFilter(element) {
        element.kendoDropDownList({
            dataTextField: "Name",
            dataValueField: "ProcessTypeID",
            dataSource: {
                type: "odata-v4",
                transport: {
                    read: {
                        url: "/odata/ProcessTypes"
                    }
                }
            },
            optionLabel: "Select Type..."
        });
    }

    This returns this error: GET http://localhost:30139/odata/Processes?%24format=json&%24expand=ProcessType&%24top=10&%24filter=ProcessType+eq+2&%24count=true 400 (Bad Request)

    So for create/update to work, I need this: 

    { field: "ProcessType", width: 300, title: "Process Type", template: "#: ProcessType ? ProcessType.Name : '' #", editor: processTypeEditor, filterable: { ui: processTypeFilter } },

     To get the filter to work properly but create and update to not work, this would be the code: 

    { field: "ProcessTypeID", width: 300, title: "Process Type", template: "#: ProcessType ? ProcessType.Name : '' #", editor: processTypeEditor, filterable: { ui: processTypeFilter } },

     

  6. Rosen
    Admin
    Rosen avatar
    3253 posts

    Posted 12 Feb 2016 Link to this post

    Hello Greg,

    As I have mentioned in my previous reply the error during filtering is caused by the fact that set this way it will try to filter on an object instead of its field which will not work. Therefore, did you try any of the suggestions I have made and what was the outcome?

    Regards,
    Rosen
    Telerik
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
  7. Greg
    Greg avatar
    6 posts
    Member since:
    Jan 2014

    Posted 12 Feb 2016 in reply to Rosen Link to this post

    I did try it before, I receive this error for menu mode: GET http://localhost:30139/odata/Processes?%24format=json&%24expand=ProcessType&%24top=10&%24filter=ProcessType+eq+%27Export%27&%24count=true 400 (Bad Request)

    and these errors on row mode: GET http://localhost:30139/odata/Processes?%24format=json&%24expand=ProcessType&%24filter=startswith(tolower(ProcessType)%2C%27ex%27)&%24count=true 400 (Bad Request) as I type and GET http://localhost:30139/odata/Processes?%24format=json&%24expand=ProcessType&%24top=10&%24filter=ProcessType+eq+%27Ex%27&%24count=true 400 (Bad Request) when I hit the enter key.

    This does it for my code as well as the example project on github that telerik provides.

  8. Rosen
    Admin
    Rosen avatar
    3253 posts

    Posted 15 Feb 2016 Link to this post

    Hello Greg,

     

    Please find updated version of the sample project with the suggestions I have made in my initial reply. Could you please take a look, maybe I'm missing something obvious.

     

    Regards,
    Rosen
    Telerik
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
  9. Greg
    Greg avatar
    6 posts
    Member since:
    Jan 2014

    Posted 15 Feb 2016 in reply to Rosen Link to this post

    It was the field: "ProcessType.Name" I was missing or maybe I had a typo when trying it the first time.  The sample project link didn't work for me, was producing the same errors I was receiving but the zip you added at the bottom of the last reply did work.

     

    Thank you for your help!

Back to Top