Hi,
We have two document types (Content and ContentVersions). Each content will have multiple versions. Please refer documents below:
- Content
{ "LatestVersionId": "Article_2", "LatestPublishedVersionId": "Article_1", "Versions": [ { "Id": "Article_1", "PublishedDate": "2021-03-18T09:59:00Z" }, { "Id": "Article_2", "PublishedDate": "2021-03-18T10:59:00Z" } ], "IsSpiked": false, "Id": "Content_1", "Type": "Content", "Deleted": false }
2) Content Version
Article_1 :
{
"ContentId": "Content_1",
"Id": "Article_1",
"Type": "Article",
"Body": "<p>Test</p>",
"ContentType": 0,
"Status": 1,
"DeliveryCodes": [
"deliveryCode_1",
"deliveryCode_2"
],
"Title": "Test Title",
"Authors": [
{
"Id": "Author_User1",
"Name": "User 1"
}
],
"PublishedDate": "2021-03-18T09:59:00Z",
"Deleted": false
}
Article_2 :
{
"ContentId": "Content_1",
"Id": "Article_2",
"Type": "Article",
"Body": "<p>Test</p>",
"ContentType": 0,
"Status": 0,
"DeliveryCodes": [
"deliveryCode_1",
"deliveryCode_2"
],
"Title": "Test Title",
"Authors": [
{
"Id": "Author_User1",
"Name": "User 1"
}
],
"PublishedDate": null,
"Deleted": false
}
We are trying to get the right indexes for the below query (join between two documents). We tried the advice index recommendations but didn’t help.
Could you please help us in getting the right index/ rewriting the below query to handle multiple document types in a single query?
SELECT T1.Authors,
T1.Id,
T1.Title,
T1.DeliveryTargetCodes,
T1.PublishedDate,
FROM `Dev-Bucket` AS T1
INNER JOIN `Dev-Bucket` AS T2 ON T2.LatestPublishedVersionId = META(T1).id
WHERE
T2.Deleted = false
AND T2.IsSpiked = false
AND T1.ContentType IN [0,1]
AND T1.Status = 1
AND (ANY wrd IN T1.DeliveryCodes SATISFIES wrd IN ['deliveryCode_1'] END)
ORDER BY T1.PublishedDate DESC
LIMIT 100
OFFSET 0