I am trying to write an OData query something like this:
var queryUrl = serviceURL + 'Thing1Thing2RelationTable?$expand=Thing1($select=ID,DisplayName;$expand=Attribute)&$filter=Thing2ID eq ' + selectedThing2ID + ' and Thing1/Attribute/Type in ('Decimal', 'Float', 'Money', 'BigInt', 'Int', 'SmallInt')&$orderby=Thing1/DisplayName';
This does not work. Something is broken with this part, because if I remove it, the query works:
and Thing1/Attribute/Type in ('Decimal', 'Float', 'Money', 'BigInt', 'Int', 'SmallInt')
I keep getting a syntax error somewhere around the word ‘in’.
(Just to be clear, Attribute is legitimately the name of the property, I’m not accidentally trying to refer to a database column in a funky way.)
To me, this should work, because everything relevant here has a 1:1 relationship: a row in Thing1Thing2RelationTable has only one Thing1, a Thing1 has only one Attribute, and an Attribute has only 1 Type.
Hopefully it’s clear what I expect the result to be here: I expect each returned item to be a relation object that contains a Thing1 object that contains an Attribute with a Type that is one of the specified values.
As I said, if I remove the problematic section of the filter, the query works, but then I need to remove some of the returned values using frontend code, which I would like to avoid.
I have also tried using just AttributeType
instead of Thing1AttributeType
, but that doesn’t work either (same syntax error).
Perhaps there is some twisty workaround here, because I don’t actually need the row from the relation table. I only need to refer to the relation table so that I can filter by selectedThing2ID. Once the filtering is done, the only data I need are a few properties from each Thing1 and a few properties from the Thing1’s corresponding Attribute. (I will refine the query with more selects once I get it working.) If this was SQL, it would be relatively easy to get what I want by leveraging JOIN, but I have no idea how to make this OData query work.
Functioning SQL for what I want, for reference (but as I say, some selects need to be added later for efficiency):
select * from (
Thing1Thing2 t1t2
join (
Thing1 t1
join Attributes a
on t1.AttributeID = a.ID
)
on t1t2.t1ID = t1.ID
) where a.[Type] in ('BigInt','Int','SmallInt','Float','Money')