3

I am converting the following simple sql query into tridion content delivery implementation. The query is 

select cm.item_id from custom_meta cm where cm.key_name ='AddToItunes' and 
                        cm.key_string_value='Yes' and cm.publication_id=80

The code for content delivery is as below

 PublicationCriteria pubCriteria = new PublicationCriteria(publicationId);
       CustomMetaKeyCriteria metaKeyCriteria = new CustomMetaKeyCriteria("AddToItunes", Criteria.Equal);
       CustomMetaValueCriteria metaValueCriteria = new CustomMetaValueCriteria("Yes", Criteria.Equal);
       var finalCriteria = new Criteria[] { pubCriteria, metaKeyCriteria, metaValueCriteria };
      Query query= new Query(new AndCriteria(finalCriteria));
var result= query.ExecuteQuery();

The issue is while the result comes same whether executing the raw sql query or using the content delivery api, the order of items is different. Is there any internal mechanism of sorting being used by content delivery as i am not providing any sort parameter.

Improve this question

3 Answers 3

Reset to default
6

When I have a peek in cd_broker.jar, in Query.class I find the following code:

if (this.sorting.getSortingParameters().isEmpty())
      this.sorting.addSortParameter(new SortParameter(SortParameter.ITEMS_ITEM_REFERENCE_ID, SortDirection.ASCENDING));

I'm not 100% sure, but I think that's your default sort parameter. This can be easily changed to whatever you need.

Improve this answer
5

By default, the results are ordered by ITEMS.ITEM_REFERENCE_ID. If you want to define your own sorting, just add a sort parameter. Like this: query.addSorting(new SortParameter(customMetaKeyColumn, SortParameter.DESCENDING);

More info about this you will find here (login: visitor/sdltridion)

Hope this helps.

Improve this answer
2
  • Thanks, yes thats the behavior its showing, but in my scenario i am converting code which was directly using sql queries to fetch the data, i am converting this into content delivery and hence data is not matching. I dont have any parameter to sort. Is there any way to skip sorting
    – rdhaundiyal
    Commented Apr 9, 2013 at 9:12
  • 1
    There is no way to skip sorting. You can define your own sorting but sorting will be defined for you if you do not.
    – Daniel Neagu
    Commented Apr 9, 2013 at 10:07
3

As Raimond said, the Query.class will automatically add a default sort parameter if none has been set.

One way could be to create your own SortParameter and implementing methods by fake code to NOT change the order of returned results.

Improve this answer
6
  • Thanks Sébastien, I didn't get what you mean by fake sortparameter. If i create a sortparameter i will definitely have to provide a sort column and this will change the order of returned result
    – rdhaundiyal
    Commented Apr 9, 2013 at 9:38
  • Can you sort by first published date or something like that? I would assume that's what the direct SQL would do
    – Nuno Linhares ♦
    Commented Apr 9, 2013 at 9:46
  • SortParameter sortParameter =new SortParameter(SortParameter.ItemOwningPublicationId,SortParameter.Descending); I added this parameter as all the items belong to same publication but still the items are coming the same way. i Believe that if there is a tie between the items for sorting, next sorting is done on the basis of item id
    – rdhaundiyal
    Commented Apr 9, 2013 at 9:47
  • Tried all the option - first publish date, last publish date, creation date. last publish date is giving result in correct order to some extent but for some of the items where the difference between the date is quite small in millisecond the order is not coming right
    – rdhaundiyal
    Commented Apr 9, 2013 at 10:27
  • SQL Server would natively sort the items in the order of the table's clustered index (assuming it has one); this is the physical storage order that SQL Server uses. You original query queried only the CUSTOM_META table which has its clustered index on the CUSTOM_META_VALUE_ID column. My guess is your only option is to extend the broker to support this as a custom sort parameter but it's not an easy route to go...
    – Bjørn van Dommelen
    Commented Apr 9, 2013 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.