question

NiteshKumar-6978 avatar image
0 Votes"
NiteshKumar-6978 asked JanardhanaVedham-MSFT commented

Graph API to filter results on 'from' and 'subject' and order by results in DESCENDING 'receivedDateTime'

Subject

Graph Api to filter email messages from a given folder such as inbox,etc and filter them on 'from' and 'subject' clause and finally order the results by DESCENDING order of 'receivedDateTime'



Our aim is to fetch emails from outlook with below criteria:

Fetch emails from a given folder such as 'INBOX', 'SENT', etc

Fetch emails with a filter clause on 'from' and 'subject'

Fetch the results in DESCENDING sorted order of 'receivedDateTime' (The most recent receivedDateTime should be at the top )

So we tried with all your concerned graph Apis but each of them seems to be have their own limitations.

We tried with the below three Apis:



a.) FILTER parameter api



https://graph.microsoft.com/v1.0/me/mailFolders/inbox/messages?$select=id,receivedDateTime,subject,from&$filter=from/emailAddress/address eq 'nitesh.kumar@franconnect.com'



However the search results are in the ASCENDING order of 'receivedDateTime' whereas we need it in DESCENDING order of 'receivedDateTime'.



We even added an '$orderBy' query parameter to the the above URL but to no avail:



https://graph.microsoft.com/v1.0/me/mailFolders/inbox/messages?$select=id,receivedDateTime,subject,from&$filter=from/emailAddress/address eq 'nitesh.kumar@franconnect.com'&$orderBy=receivedDateTime DESC



However the response says

{
"error": {
"code": "InefficientFilter",
"message": "The restriction or sort order is too complex for this operation.",
"innerError": {
"date": "2021-12-08T10:29:39",
"request-id": "fc09dfbb-76b9-4c86-8b25-babd66594751",
"client-request-id": "75af9f90-9e78-14c8-72af-316ccee83b71"
}
}
}



It looks like ‘Order By’ clause is not supported by your $filter api.



Question : Can you please elaborate on how to order by ‘receivedDateTime’ ?



b.) To get past the above limitation we experimented with your GET search api.



https://graph.microsoft.com/v1.0/me/mailFolders/inbox/messages?$search="from:tribhuwan.negi@franconnect.com"&$select=id,receivedDateTime,subject,from&$top=300



Though this api fetches results in the DESCENDING order of 'receivedDateTime' but However there seems to be a limitation on the maximum number of records fetched by it.



The above search Api FETCHES ONLY A MAXIMUM OF 250 RECORDS !!


Also it does not support a $skip parameter ( Error message: "The query parameter '$skip' is not supported with '$search' )



Question: Please elaborate on how can I get records beyond 250 ?



c.) We also tried your generic search API



POST https://graph.microsoft.com/v1.0/search/query

{
"requests": [
{
"entityTypes": [
"message"
],
"query": {
"queryString": "from:tribhuwan.negi@franconnect.com"
},
"from": 0,
"size": 25
}
]
}



Even this API has it own limitations.



The above Api DOES NOT fetch results from a 'GIVEN FOLDER'.


Rather the search results are spread across all folders.



Question: How can I get search results within a given folder ?



As you can see that all the above APIs do not satisfy our business criteria, namely :

Fetch emails from a given folder such as 'INBOX', 'SENT', etc

Fetch emails with a filter clause on 'from' and 'subject'

Fetch the results in DESCENDING sorted order of 'receivedDateTime' (The most recent receivedDateTime should be at the top )

We are writing to you to help us point if we have missed anything in our analysis.

Also please point to us the right API to solve our problem.


microsoft-graph-mailmicrosoft-graph-search
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GlenScales-6756 avatar image
1 Vote"
GlenScales-6756 answered GlenScales-6756 commented

You can workaround the filter limitation by using the PidTagSenderSmtpAddress extended property https://docs.microsoft.com/en-us/office/client-developer/outlook/mapi/pidtagsendersmtpaddress-canonical-property eg

 https://graph.microsoft.com/v1.0/me/mailFolders/inbox/messages?$select=id,receivedDateTime,subject,from&$filter=singleValueExtendedProperties/any(ep:ep/id eq 'String 0x5D01' and ep/value eq 'gscales@blahblah.com')&$orderby=receivedDateTime DESC

should work okay

If your queries are going to be somewhat static then it maybe a SearchFolder is a good idea https://docs.microsoft.com/en-us/graph/api/resources/mailsearchfolder?view=graph-rest-1.0 (note this is how the favourites feature works in Outlook)




· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks. 0x5D01 works ok for 'from'.

But additionally Filters are also required for 'cc' and 'to, and 'subject'. I have tried a few such 0x0076, 0x007C, and few others, But it does not fetch data.
Can you please provide me urls for filters on CC, TO and SUBJECT

0 Votes 0 ·

For the recipient collection properties there aren't any properties that will work, for those types of queries they get real expensive for the server so I would look to use the search endpoint and the participants keywords that includes To,CC,From eg

 {
     "requests": [
         {
             "entityTypes": [
                 "message"
             ],
             "query": {
                 "queryString": "participants:garthf@contoso.co"
             }
         }
     ]
 }

I know you can't refine that to a Folder (I don't quite understand why the folderId: predict doesn't work in the graph where it does in a eDiscovery) but you can do a client side filter by using the ParentFolderId in the result set and drop the result that don't match your target folder, if these are dynamic queries that generally gives good/reliable performance even when the ItemCount in the underlying folder is large vs doing a dynamic filter where the performance will degrade inline with the ItemCount. The difference is a Search is a content Index query and filter is just a MAPI restriction, if they aren't dynamic queries eg you doing something similar to the favourites feature in Outlook then a SearchFolder can be more useful.


0 Votes 0 ·
JanardhanaVedham-MSFT avatar image
3 Votes"
JanardhanaVedham-MSFT answered JanardhanaVedham-MSFT commented

Hi @NiteshKumar-6978,

As mentioned in Micrsoft Graph API - List Messages documentation :

When using $filter and $orderby in the same query to get messages, please ensure to specify properties in the following ways:

1.Properties that appear in $orderby must also appear in $filter.

Example :
API : GET https://graph.microsoft.com/v1.0/me/mailFolders/inbox/messages?$orderby=receivedDateTime DESC&$filter=receivedDateTime ge 2016-01-01T00:00:00Z and from/emailAddress/address eq 'o365mc@microsoft.com'&$select=receivedDateTime,from,subject,hasAttachments,bodyPreview

2.Properties that appear in $orderby are in the same order as in $filter.

Example :
API: GET https://graph.microsoft.com/v1.0/me/mailFolders/inbox/messages?$orderby=receivedDateTime DESC,from/emailAddress/address&$filter=receivedDateTime ge 2016-01-01T00:00:00Z and from/emailAddress/address eq 'o365mc@microsoft.com'&$select=receivedDateTime,from,subject,hasAttachments,bodyPreview

3.Properties that are present in $orderby appear in $filter before any properties that aren't.

Example :
API : GET https://graph.microsoft.com/v1.0/me/mailFolders/inbox/messages?$orderby=from/emailAddress/address&$filter=from/emailAddress/address eq 'o365mc@microsoft.com' and receivedDateTime ge 2016-01-01T00:00:00Z&$select=receivedDateTime,from,subject,hasAttachments,bodyPreview

Below are the additional examples on From ,Subject properties and using $filter and $orderby DESC in the same query to get the messages :

API : GET https://graph.microsoft.com/v1.0/me/mailFolders/inbox/messages?$select=id,receivedDateTime,subject,from&$orderBy=receivedDateTime DESC&$filter=receivedDateTime ge 2016-01-01T00:00:00Z and from/emailAddress/address eq 'o365mc@microsoft.com'

API : GET https://graph.microsoft.com/v1.0/me/mailFolders/inbox/messages?$select=id,receivedDateTime,subject,from&$orderBy=receivedDateTime DESC&$filter=receivedDateTime ge 2016-01-01T00:00:00Z and from/emailAddress/address eq 'o365mc@microsoft.com' and subject eq 'Major update from Message center'

Please refer below documentation for more information on this :

https://devblogs.microsoft.com/microsoft365dev/update-to-filtering-and-sorting-rest-api/

https://docs.microsoft.com/en-us/graph/api/user-list-messages?view=graph-rest-1.0&tabs=http#using-filter-and-orderby-in-the-same-query

Hope this helps.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @NiteshKumar-6978,
Have you got a chance to review this answer / resoultion. If the answer is helpful, please click "Accept Answer". If you have any further questions about this answer then please let me know.

0 Votes 0 ·