function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Aleksey SheldagaevAleksey Sheldagaev 

How to get KnowledgeArticleVersion rows ordered by ViewStat in SOQL

How can I order by KnowledgeArticleViewStat.NormalizedScore when getting rows from KnowledgeArticleVersion table

My SOQL query looks like this:
SELECT Id, KnowledgeArticleId, ArticleType, LastPublishedDate 
FROM KnowledgeArticleVersion 
WHERE PublishStatus = 'online' AND Language = 'en_US' AND ArticleType IN ('...')
LIMIT 5

 
Best Answer chosen by Aleksey Sheldagaev
Aleksey SheldagaevAleksey Sheldagaev
Thank you, Shashikant.

But actually it's not exactly what I need, because query should returns only first 5 rows from KnowledgeArticleVersion which satisfies a condition.

I've found a way to make it:
SELECT ParentId FROM KnowledgeArticleViewStat 
WHERE ParentId IN (
      SELECT KnowledgeArticleId FROM KnowledgeArticleVersion
      WHERE PublishStatus = 'online' AND Language = 'en_US' AND ArticleType IN (...)
    ) 
ORDER BY NormalizedScore DESC
LIMIT 5


 

All Answers

Shashikant SharmaShashikant Sharma
I think you can not achieve it with this query, for this you have to first find the 
 
SELECT Id, NormalizedScore, Parent.Id 
FROM KnowledgeArticleViewStat where Channel = 'App' 
ORDER BY NormalizedScore

Then use custom sorting on the Implementing Comparable Interface : https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_comparable.htm

Thanks
Shashikant
Aleksey SheldagaevAleksey Sheldagaev
Thank you, Shashikant.

But actually it's not exactly what I need, because query should returns only first 5 rows from KnowledgeArticleVersion which satisfies a condition.

I've found a way to make it:
SELECT ParentId FROM KnowledgeArticleViewStat 
WHERE ParentId IN (
      SELECT KnowledgeArticleId FROM KnowledgeArticleVersion
      WHERE PublishStatus = 'online' AND Language = 'en_US' AND ArticleType IN (...)
    ) 
ORDER BY NormalizedScore DESC
LIMIT 5


 
This was selected as the best answer