Create complex queries in datagrid columns templates

[Nikolay]

Hello, everyone. I’m quite new in all of this, and have a question about templates behind columns in data grids. Currently one may have a simple request to another table to retrieve the data like this:

${data.AnotherTable?.another_property}

It’s equvalent to SQL:

SELECT another_property FROM AnotherTable
WHERE AnotherTable.ForegnKey IS data

(Correct me if I’m wrong)

How can I traverse further through related tables? E.g. something like this:

${x?.FinalTable.required_property where x=data.IntermediateTable?.IntermediateForegnKey}

[enchev]

Hi Nikolay,

All related tables are available as properties, arrays or instance. Related properties are populated with $expand or with separate data-source invoke:

You can also invoke custom method and return custom data using this approach:

Invoke custom server-side method

Best Regards,
Vladimir

[Nikolay]

I’m afraid, I don’t understand the answer… I have a “parent” table for which I generate a grid. Tables, directly related to it, are available without any additional code as in screenshots above - I just ask them as “properties” of my “data” object:
data.RelatedTable?.related_property
My problem is that I need to go further, to a table not related directly to the “parent” one. In SQL I do it with JOIN statement as far as I need. But I don’t understand how can I write it in a template…
You suggest to change the template for the whole grid, but I need to change only one column.

[enchev]

Hi,

You can display joined tables with custom server method invoke approach or using stored procedure.

Best Regards,
Vladimir

[enchev]

Hi,

You can also use nested expands (converted to JOINS in SQL) to present many-to-many like Orders -> OrderDetails -> Products:

Here is the application.

Best Regards,
Vladimir

[Nikolay]

So, after some exercises…
The ASP.NET documentation lies. The only way to make these deep queries work is to include explicit ($expand=TableName) in page load (as on the screenshot above) and in data grid “load” events. Otherwise they will disappear after switching between pages of the table.

[Nikolay]

Quick question: in your example you use data list template to show products, how to:

• show values in-line and not as a list
• limit the size of a field for the template and make it scrollable (paging is not an option - too much space wasted)

[enchev]

You can define any Angular template for DataGrid column cell template. For example:

You can use the template also to define height, width, overflow, etc.