inconsistant behaviour between element query selectors like groupBy() vs orderBy()

[bencresty]

Description

After a great tip from Steve Rowling steverowling on the discord I finally figured out why a groupBy() selector wasn't working and kept telling the field wasn't known. Instead of orderBy(), groupBy() (and some other selectors like select()) doesn't work with the field handles (like myFieldHandle), but with the actual hard db column names instead (like field_myFieldHandle_dkfjdkjf).

AFAIK this big difference in selector behaviour can't be found anywhere in the docs. I couldn't find it in the YII docs either.

I would expect all query selectors to work with fieldHANDLES instead of their database column names, which normally are only under water as we're using activeRecords etc.

This is prone to errors, as the selectors not using handles, but column names, don't work anymore when db column names 'randomly' or intentionally change. Like spoken about in the issue here; #6922

Like I'm not sure if these db-tablenames are actually the same after deploying/installing/updating projects on different environments from yaml files. When searching in the project yaml-files I can't find these db-column-names of these fields, so they seem to be created by code after processing yaml files. Not sure if the random bit on the end will be the samen on all environments for instance.

BTW I've been looking everywhere in both the Craft as well as the Yii docs about this, but AFAIK it doesn't mention these differences between selectors. So even if this is somehow known behaviour, but not yet fixed, it would be welcome to read about that difference in the docs, so maybe highlighted notes about this can be added to docs about these selectors?! This took me quite a while to figure out as other selectors do work as expected.

Please change this to all selectors behave the same, using field handles, which are intended for that!

Thanks in advance!

Steps to reproduce

1. This does NOT work (unknown field 'myField1Handle')

$entries = Entry::find()
   ->section('mySectionHandle')
   ->groupBy('myField1Handle')
   ->orderBy('myField1Handle')
   ->all();

2. This DOES work:

$entries = Entry::find()
   ->section('mySectionHandle')
   ->groupBy('field_myField1Handle_dfkjdfkd')
   ->orderBy('myField1Handle')
   ->all();

Additional info

• Craft version: 3.7.13
• PHP version: 7.4.9
• Database driver & version: MySql 5.5.5