Accessing other tables in column definitions

[si2030]

Sorry to bother..

Had a look through the wiki and its not obvious how to request information in other related tables. I figure its to do with field (I could be wrong here) in the column definitions and it uses dot notation however in my example I have a client table and when it brings the data back on OData it brings back the "suburbId" = 6123.

The suburb table for suburbs has "Id", "SuburbName", "Postcode" as the name of columns in it and so on.

In the column definitions I put for this column:

{ id: 'suburbId', name: 'Suburb Name', field: 'suburbId.suburb.suburbName', sortable: true, type: FieldType.string, filterable: true, filter: { model: Filters.compoundInput } },

Simply how to you structure the field to access, in this case, the suburbName.

[ghiscoding]

Sorry but I really don't understand your question, are you saying the OData query is incorrect or are you saying the data is there but you don't know how to read it? If it's the latter, you can use the complexObject Formatter

[si2030]

Ah.. sorry for not being more explicit. Essentially I am working with the client table but the database is normalised to a degree and there is an entity relationship with the suburb table. I've identify the suburb in the client table via the suburbs id.

Instead, I would like to access the suburb table via this id.. I suppose if you have a client it'd be nice to display the address and this number represents the suburb and of course the suburb name in the suburb table.

Of course I could display the number (thats easy).. its in the client table which OData has already bought down however I suspect OData can access and link multiple tables to, say, show all the client's details including the address (suburb name) from the suburb table and say the number of jobs say in the jobs table and so on. I also reckon slickgrid can make OData pull information from all the linked tables to the client table via the respective ids.. but I havent found it as yet... sure its there and thats what I tried to do via the field setting in the columns definitions.. thought the dot notation would do it.

So, I have a client and instead of displaying the suburb id in a column I want to display the suburb name which is in the suburb table linked by the id.

How do you set up Slickgrid to get information for columns out of other linked 1:1 tables etc?

Hope this makes more sense.. :) Simon

PS and on the issue of TotalItemCount... I had the idea of adding the totalItemCount to the data at the controller point before sending the lot down to browser.. seems simple and I dont have do much really to do this. Also, I suspect the Asp.net Core team will have finally put OData in the upcoming 3.0 package making my life alot easier which they havent yet. Once they do and I have reconfirmed you still cant get the total item count (its supplies the count for the filtered set.. at least for me) I will ask them about it.. thats a whiles off though..

[ghiscoding]

Hmm if I understand correctly, I would say that the library is made for simple and flat tables. However in our project, we use GraphQL and to circumvent this kind of issues, we just create VIEWs and link to the VIEW instead of the table. You can put whatever you want in the VIEW. The other option might be to preload all the suburb data (if it's not too big) and then with a Custom Formatter show the suburb info you want by doing a find or a map with the preloaded data.

So I'll say it again on the library side, the query that will be created by this grid, will not be (and cannot be) super complex. It will never produce a query to pull complex data from another table or complex object or anything like that, the library is just not designed to do that. In theory, the lib was created to just display data, OData and GraphQL were added later, but again they are made in a flat way, nothing too complex (for example, the lib will never produce a query that filters by City of the Location object, in theory that would look like $filter=contains(Location/Address, 'San Francisco'), but that is far too complex for my liking and my time. I basically wrote all the OData query builder myself by hand and it would be far too much time to invest to do more than just flat table & result querying.

PS and on the issue of TotalItemCount... I had the idea of adding the totalItemCount to the data at the controller point before sending the lot down to browser.. seems simple and I dont have do much really to do this.

At least you seemed to have resolved the issue with the grid not showing data... However, about the totalItemCount, you should still return an object from your OData query (instead of the usual OData result). It's not that hard to just return an object, you can do that by creating a dynamic JSON output, see this SO (which is actually my answer, so you can up vote it too lol). Basically I wrote the GridODataService with the expectation that it does return the object, if you aren't returning the object then you're looking for trouble (you'll get all the console errors that you asked about earlier and some incorrect behaviors).

Also, I suspect the Asp.net Core team will have finally put OData in the upcoming 3.0 package making my life alot easier which they havent yet. Once they do and I have reconfirmed you still cant get the total item count (its supplies the count for the filtered set.. at least for me) I will ask them about it.

I doubt it will be v3, instead I assume they will release with OData v4, that later version is out since last year as can be seen here and which is also why I'm reworking the OData query output (which a guy opened an issue in Angular-Slickgrid about it)... and for the total count, again it's not and will not be part of the OData itself, you have to create a JSON object that returns this total and the array of items, this is required by the grid for the pagination to work properly. Basically I ask this to be an object to avoid calling 2 queries (1 for the total and 1 for the items), which is why you have to create the object within your controller (in your return).

[ghiscoding]

You can now see the new release out, version 2.13.5, some improvements to OData, new Range Filters and finally I18N is now optional.

The OData Example was also updated to show the differences between OData v2 and v4

[si2030]

So I got tired of waiting for OData to be released in dotnet core 3.0 and so my purposes I added a generic extension method with dynamic lambda that does the filtering and sorting using an expression tree. Works for multiple tables etc and I can now pass through the total items because I now know it.. Took a long time. Im no brilliant programmer :( I used your latest release though.. so thankyou very much..

[ghiscoding]

Side note, .Net Core 3 will be released next week at the .Net Conf, you can see the official post which mentions that.