Buckle up - this topic has layers we will address in detail in the future. For now though, the history and nuances don't matter - especially not for new or inexperienced users. Luckily, loadorder is way less of a headache now than it was in older versions of RimWorld. Modern mods auto-enforce rules between each other and if something's wrong with your list, the base game's mod menu (or any solid mod manager) yells at you immediately. With widespread mod manager use proper loadorder has become a rather minor topic compared to the past, just hit "sort" and you're often done. Edge cases still exist of course - but those often have to be addressed and fixed specifically while troubleshooting anyways.
Quick-Start Advice: Grab RimSort (our top pick), set it up properly, build your list, then auto-sort. This alone often works 9/10 times. If not; ask for help!
If for whatever reason you find yourself in a situation to manually sort your loadorder, try to avoid old "load order guides" from RimWorld's early days you can still find online at times. Many of them are based on a situation that just doesn't apply to modern RimWorld-modding anymore. But in general they just offer what is a general order for mods, which is typically the safest for them to work out-of-the-box. This never overrules any individual rule set by mods or the databases most mod managers are using - but just for reference the potentially safest, general order would be something like:
Harmony
CORE
Official DLCs
Important technical mods + key frameworks
Non-vanilla features, UI mods
Content-only mods (no custom code)
Tweaks, patches, addons for categories above, other game-logic changes
Cross-compatibility patches (for mods above)
Pure retextures
"Load last" claimants (often very special cases)
After applying this general logic you often just have to sort mods that are still complaining to be loaded incorrectly by hand, moving them above or below the respective other mod they broker their loadorder with. As said multiple times already though, these days we are expecting most users of any level of experience to use mod managers anyways, so using them correctly should be more of a concern than learning about the intrinsic of a outdated topic - aside from historical value, there's not much to gain from it.