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

Question

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.

Answer 1

Hi @Nitesh Kumar ,

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://learn.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".

Answer 2

You can workaround the filter limitation by using the PidTagSenderSmtpAddress extended property https://learn.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://learn.microsoft.com/en-us/graph/api/resources/mailsearchfolder?view=graph-rest-1.0 (note this is how the favourites feature works in Outlook)

Answer 3

Hi @Nitesh Kumar ,

I have recently worked with this API and have the answer for you for section A in your question above.

Here is an example of how we are successfully using the Graph API with Outlook to filter and orderby:
https://graph.microsoft.com/v1.0/users/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX/messages?$filter=from/emailAddress/address eq '{sender_email}'&$orderby=from/emailAddress/address,receivedDateTime desc

Thanks!