Commerce relation(ship), a story – Quan Mai's blog (vimvq1987.com) this might be helpful. TL;DR: you can't set the sort order or drag-and-drop the nodes to change their order, just yet.

But we can apparently set the correct order when we initially create the categories (they are created/updated with an import job from another PIM). Are you saying that there is no way to change the sort order? And just to be clear, we don't need to be able to do it in the user interface but through code using the episerver api.

You can, in theory, set the SortOrder of the NodeRelation (as it inherits from Relation), then update it using IRelationRepository. I don't think it will reflect to what is shown in the Catalog UI, but my memory is rusty so I can be wrong. 

"You can, in theory, set the SortOrder of the NodeRelation (as it inherits from Relation), then update it using IRelationRepository. I don't think it will reflect to what is shown in the Catalog UI, but my memory is rusty so I can be wrong."

That's exactly what we're doing, and the SortOrder column is updated in the database but it doesn't affect neither the item list in the admin ui nor the published lists on the website. So I gather we either need to change the logic that fetches categories on the website (and not care about how it looks in the admin ui) and use the NodeRelation SortOrder or figure out how to update the actual sort order being used. 

But I still don't understand what determines the sort order of categories in the catalog, is it maybe the SortOrder column in the CatalogNode table?

Edit: Yes, it seems like it's the SortOrder column in the CatalogNode table. Do you know how to update that programmatically?

That is strange, when you create a new node that SortOrder is set to 0. Only the SortOrder in NodeRelation (which reflects in CatalogNodeRelation) is updated 

Not sure I follow you.

We are updating existing categories/relations, not creating new. 

Is there an episerver api interface for updating the SortOrder column in the CatalogNode table?

Unfortunately no. Not with the content api at least. You can use the ICatalogSystem/CatalogNodeDto approach, but it's not recommended. 

Why is it not recommend?

Is there any documenation or examples on how to use it anywhere?

It is old API which will interfere with versions etc. No example that I am aware of, but you can try using this API https://world.optimizely.com/csclasslibraries/commerce/Mediachase.Commerce.Catalog.ICatalogSystem?version=10#Mediachase_Commerce_Catalog_ICatalogSystem_SaveCatalogRelationDto_Mediachase_Commerce_Catalog_Dto_CatalogRelationDto_ 

Thanks! I'm not sure though how to get the CatalogNodeDto instance. I have an EntityId (266277) which correlates with the Code column in the CatalogNode table. 

ICatalogSystem has GetCatalogNodeDto if IIRC (same link as above just scroll up/down). It can take id or code. You get a CatalogNodeDto which then you can access to the CatalogNode table to set SortOrder.

As I said, this is not recommended 

OK, thanks! Yeah you get a CatalogNodeDto but I can't see how to to set the SortOrder, need to dig some more. Eventually we should call catalogContext.SaveCatalogNode(node) I guess.

I understand that it's not recommended but from what I can tell there is no other way, right?

CatalogNodeDto.CatalogNode[0].SortOrder would give you that.

Yes there is no other way. As I said I am not quite sure how this SortOrder works for you, but well ... :)

Maybe I'm missing something fundamental, like I said I'm new both to Episerver and this application.

Would you recommend us using the SortOrder of the NodeRelation instead? The most important thing is that the sort order is represented correctly on the website. If it's incorrect in the admin ui we can live with that.

But then we'd need to change the code where we fetch the categories instead. Today we do something like this. It seems like we should be able to add an OrderBy() but I can't see how to access the SortOrder of the NodeRelation here.

_contentLoader.GetChildren<DefaultCategory>(root.ContentLink).Where(cat => cat.VisibleInMenu)

you are correct, it is not possible to use the NodeRelation here (As it's for linked nodes only).

One better solution (maybe) would be adding a property to your node content, something like "OrderInList", and then you can directly update that in the Catalog UI, and sort by it using Linq

_contentLoader.GetChildren<DefaultCategory>(root.ContentLink).Where(cat => cat.VisibleInMenu).OrderBy(x=>x.OrderInList);

I circled back to your previous suggestion "CatalogNodeDto.CatalogNode[0].SortOrder would give you that."

My CatalogNodeDto.CatalogNode is empty, what do I need to do to load its data? 

var catalogNodeDto = CatalogContext.Current.GetCatalogNodeDto(link.TargetEntityId, new CatalogNodeResponseGroup(CatalogNodeResponseGroup.ResponseGroup.CatalogNodeInfo));

what is the value of link.TargetEntityId

For example 266257 which is the value of a Code column in the CatalogNode table.

was it passed down as 266257 (integer), or "266257" (string)? you might want to convert to string 

Thank you! Who knew! Like I said I'm new to Episerver :)