Solved

OData query; expand multiple tables/references

  • 7 December 2022
  • 9 replies
  • 1417 views
Alex Kerschkamp
Rank
Userlevel 2
Badge +3

Hello everyone,

We're experimenting with the API functionality and its OData query possibilities. We have a situation that we can't seem to get "right" though.

 

Let's consider the following situation:
Table "car"
Table "driver"
Table "car_driver"
Car_driver obviously is the linking table between car and driver. To query that, we need to "expand" the relation, as explained in the documentation.

 

So, we can do like this:
https://our-fabulous.thinkwise-app.net/indicium/iam/our-app-id/car?$expand=detail_ref_car_car_driver
to obtain the car and all ID's of the drivers, or
https://our-fabulous.thinkwise-app.net/indicium/iam/our-app-id/driver?$expand=detail_ref_driver_car_driver
to obtain a driver and all the car ID's he or she drives. Both queries can use an addition filter and other goodies.


So, how do we "expand" our data model, so that it gives a car with its drivers in one go, or all drivers with their cars in one go? We can expand one level, but not beyond that. What do we need to do, to hop over all three the tables in one go? Yes, we could call the API twice; call #1 for cars + driver ID's from the linking table, or drivers + car ID's from the linking table, and an additional call #2 to get the actual entities by their ID's. But somehow that doesn't seem like an elegant solution.

Any ideas?

Best regards,
-Alex.

icon

Best answer by Vincent Doppenberg 7 December 2022, 14:39

View original
This topic has been closed for comments

9 replies

V
Rank
Userlevel 6
Badge +4

Hello Alex,

We can expand one level, but not beyond that.

You can actually expand as many levels as you'd like, until you run into some kind of technical limitation like a request timeout, memory or recursion depth.

So for cars + drivers, you can do this, assuming that driver is a detail of car_driver and the reference ID is ref_car_driver_driver:

https://our-fabulous.thinkwise-app.net/indicium/iam/our-app-id/car?$expand=detail_ref_car_car_driver($expand=detail_ref_car_driver_driver)

If driver is not a detail of car_driver, but it is a lookup column in the car_driver entity with the column name ‘driver_id’, then you can still do it like this:

https://our-fabulous.thinkwise-app.net/indicium/iam/our-app-id/car?$expand=detail_ref_car_car_driver($expand=lookup_driver_id)

And the same applies for drivers + cars, I think the concept is clear.

Note that inside of the parentheses of an $expand parameter, you can not only use another $expand parameter, but you can in fact use most OData query parameters, separated by semi-colons. For example:

https://our-fabulous.thinkwise-app.net/indicium/iam/our-app-id/car?$expand=detail_ref_car_car_driver($expand=detail_ref_car_driver_driver;$select=driver_id, driver_name)

I hope this helps.

Alex Kerschkamp
Rank
Userlevel 2
Badge +3

Marvelous, Vincent! Many thanks! This works fabulously!

An additional question; how do we set a filter on the "lowest" level? We only seem to be able to filter top-level nodes, not the nodes that are retrieved from references. Suppose that the lookup table driver contains a driver_full_name column. And we use this query:
https://our-fabulous.thinkwise-app.net/indicium/iam/our-app-id/car?$expand=detail_ref_car_car_driver($expand=lookup_driver_id)
The filter &$filter=contains(driver_full_name,'firstname')
or
&$filter=contains(detail_ref_car_car_driver/lookup_driver_id/driver_full_name,'firstname')
do not work. Can we get it to work like this? Or isn't it supposed to work like this?

Best regards,
-Alex.

V
Rank
Userlevel 6
Badge +4

Hello Alex,

Whenever you add an $expand parameter, let's say:

/x?$expand=y

You can add parentheses behind the expanded navigation property (y in this example is called a navigation property in OData). Inside of these parentheses you can add most of the regular OData parameters that you are familiar with, such as $select and $filter, separated by semicolons. These OData parameters will be applied to the expanded entity. So for example:

/x?$expand=y($select=id,name;$filter=name eq 'test';$expand=z)

So the $select, $filter and $expand parameters in my example above are applied to the entity that is referenced by the navigation property y. Similarly, you can add parentheses to the navigation property z as well, in which you can define OData parameters for that referenced entity.

I hope this explains how it works.

Alex Kerschkamp
Rank
Userlevel 2
Badge +3

Thanks Vincent! Exactly what we needed 😃 Brilliant!

S
Rank
Userlevel 1
Badge

Hi Vincent,

I’ve also been trying this, but can’t really get it to work.

this is the expansion i want to achieve: transl_carriage_id($expand=transl_object_id), but all I’m able to get is a single level. when trying to expand a 2nd time it does not return any data.

accessing transl_object_id from the carriage table (from here it’s 1 level) does yield results.

 

I’d love any insight on how i can get this to work.

V
Rank
Userlevel 6
Badge +4

Hello ssital,

It is hard to answer this question without knowing your goal or your data model, so this is going to be a bit of a shot in the dark.

I suspect that the issue here might be caused by a misunderstanding of how the transl_ navigation property works. The transl navigation property is used to expand lookup display values and for this reason it does not necessarily refer to the direct lookup entity (1 level), but instead it refers to the entity that contains the display value of the lookup (N levels). By contrast, the lookup_ navigation property does always refer to the direct lookup entity (1 level).

I'll try to illustrate this with a table.

A B C
my_lookup_id my_lookup_id my_lookup_id
    my_display_value

 

Consider these three tables and imagine that they have references between them on my_lookup_id. When expanding transl_my_lookup_id from entity A, you are not expanding A → B, you are expanding A → C, because C is the entity that contains the display value of the lookup.

So if your intention was to expand entity C in your result for entity A, then you have two options:

A → C directly:

/A?$expand=transl_my_lookup_id($select=my_display_value)

A → B → C using the lookup_ navigation property:

/A?$expand=lookup_my_lookup_id($expand=lookup_my_lookup_id($select=my_display_value))

 

If this does not help you, then please supply some more information on what you are trying to achieve, including a picture of the relevant entities and references in your data model.

S
Rank
Userlevel 1
Badge

Hi,

So let’s say i have 3 tables:

SHIPMENTS CARRIAGES SHIPS
shipment_id carriage_id ship_id
carriage_id ship_id data_i_want

 

 

So I’m starting in table shipment and need to make my way down to the “SHIPS” table.

Doing an expand on “CARRIAGES” using lookup returns the all the data of that carriage (as it should).

But now i want to go a level lower using the “ship_id” that’s available in “CARRIAGE” but i haven’t been able to get that to work:

$expand=lookup_carriage_id($expand=lookup_ship_id)
$expand=transl_carriage_id($expand=transl_ship_id)

I’ve tried both lookup & transl navigation properties.

 

 

V
Rank
Userlevel 6
Badge +4

Hello ssital,

It looks like Carriages → Ships is a detail reference, correct?

Try to expand from Carriage to Ships by using the detail_ navigation property. The naming convention for a detail navigation property is detail_<ref_id> where <ref_id> is the ID of the reference between Carriage and Ships in the SF.

So for example:

/shipments?$expand=lookup_carriage_id($expand=detail_carriages_ships($select=ship_id, data_i_want))

As a rule of thumb, if you’re navigating up, use lookup_ and if you're navigation down, use detail_. If you're trying to find the display value of a lookup property, use transl_.

We are working on some more documentation regarding this by the way, I am aware that not all of this is thoroughly explained in our docs.

I hope this helps.

V
Rank
Userlevel 6
Badge +4

Hello ssital,

As an addition to the above, it looks like Shipments → Carriages is actually a detail reference as well, in this context. If so then the same logic applies there and it would be more like this:

/shipments?$expand=detail_shipments_carriages($expand=detail_carriages_ships($select=ship_id, data_i_want))

 

Terms & ConditionsCookie settings