Hierarchical Query

14 Dec 201713 minutes to read

The DataManager contains support to manage the hierarchical query. The hierarchical queries are commonly required when you use foreign key binding. The hierarchical query can be provided using the hierarchical function. This method accepts two parameters such as the query and the selector function.

ForeignKey

The foreignkey method of ej.Query can be used to refer another table fields. The foreignkey method accepts one parameter that is the foreign key value.

The following code example illustrates the hierarchical query and foreignkey method.

HTML

<div id="grid" class="e-grid" style="width:850px" >
        <div class="e-gridheader">
            <table class="e-table">
                <colgroup>
                    <col style="width:25%" />
                    <col style="width:25%"/>
                    <col style="width:25%"/>
                    <col style="width:25%"/>
                </colgroup>
            <thead>
                <tr>
                    <th class="e-headercell"><div class="e-headercelldiv">Order ID</div></th>
                    <th class="e-headercell"><div class="e-headercelldiv">Customer ID</div></th>
                    <th class="e-headercell"><div class="e-headercelldiv">Order Date</div></th>
                    <th class="e-headercell"><div class="e-headercelldiv">Employee</div></th>
                </tr>
            </thead>
            </table>
        </div>
        <div class="e-gridcontent">
            <table class="e-table">
                <tbody>

                </tbody>
            </table>
        </div>
    </div>
    <script type="text/javascript">
        $(function () {
            // DataManager creation
            var dataManger = ej.DataManager({
                url: "http://mvc.syncfusion.com/Services/Northwnd.svc/",
                crossDomain: true
            });

            // Query creation
            var query = ej.Query()
                .from("Orders")
                .search("TM", ["CustomerID", "Employee.FirstName"])
                .page(1, 10)
                .hierarchy(
                    ej.Query()
                        .foreignKey("OrderID")
                        .from("Order_Details")
                        .sortBy("Quantity"),
                    function () {
                        // Selective loading of child elements
                        return [10410, 10492, 10949, 10742, 10975]
                    }
                )
                .select("OrderID", "CustomerID", "OrderDate", "Employee.FirstName")
                .expand("Employee");
            // executing query
            var promise = dataManger.executeQuery(query);
            promise.done(function (e) {
                
                $("#grid tbody").html($("#tableTemplate").render(e.result));
                $("#grid").ejWaitingPopup("hide");
            });

            $("#grid").ejWaitingPopup({ autoDisplay: true });
        });
    </script>

    <script id="hierTemplate" type="text/x-jsrender">
        <tr>
            <td class="e-rowcell">{{>ProductID}}</td>
            <td class="e-rowcell">{{>Quantity}}</td>
            <td class="e-rowcell">{{>UnitPrice}}</td>
        </tr>
    </script>
    <script id="tableTemplate" type="text/x-jsrender">
        <tr>
            <td class="e-rowcell">{{>OrderID}}</td>
            <td class="e-rowcell">{{>CustomerID}}</td>
            <td class="e-rowcell">{{>OrderDate.toDateString}}</td>
            <td class="e-rowcell">{{>Employee.FirstName}}</td>
        </tr>
        
        <tr class="childgrid">
            <td colspan="4">
            <div>
            <table class="e-table">
            <tbody>  
            <tr>
            <td colspan="3">
                <b>Order Details of </b>
                <div class="e-grid">
                    <div class="e-gridheader">
                    <table class="e-table">
                        <thead>
                                <colgroup>
                                <col style="width:33%" />
                                <col style="width:33%"/>
                                <col style="width:33%"/>
                            </colgroup>
                            <tr>
                                <th class="e-headercell"><div class="e-headercelldiv">Product ID</div></th>
                                <th class="e-headercell"><div class="e-headercelldiv">Quantity</div></th>
                                <th class="e-headercell"><div class="e-headercelldiv">Unit Price</div></th>
                            </tr>
                        </thead>
                        </table>  
                    </div>
                    <div class="e-gridcontent"> 
                        <table class="e-table">
                            <tbody>
                            
                            </tbody>
                        </table>
                        </div>
                </div>
                </tr>
                </tbody>  
                </table>
            </td>
            </div>
            </td>
        </tr>
        
    </script>

The result for the above code example is illustrated as follows.