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

[brandonkelly]

Can look into adding field handle support to groupBy down the road, but for now this is working as expected. We added field handle support to orderBy since it is much much more commonly needed than groupBy. I’d consider groupBy to be a bit more of an advanced feature that requires a stronger understanding of how SQL works, whereas orderBy is more accessible to more developers.

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.

To ease your concerns here, column names will be consistent across all environments, and will not change over time, except in one single scenario: when you have a field that was created before Craft 3.7 was released, and which changes from being a single-column field to a multi-column field. (The only known example of a multi-column field currently is a Date field with the “Show Time Zone” setting enabled.)

[bencresty]

@brandonkelly Thanks for your response and first of all I understand you guys are always busy improving and even during weekends active, so thanks!

That said, I think there are three things playing here:

1. Selectors have inconsistant behaviour
2. It is not clear what selector expects a handle and what selector expects a db-column name
3. There are worries about being able to rely on the db-column name to be always the exact same

Just walk through them one by one below:

1) Selectors have inconsistant behaviour

IMO all selectors should be working exactly the same, so best would be every selector to use the field handle as Craft obviously is made to work that way. If we'd like to use db-column names we're missing part of the benefit of using an 'upper' framework IMO as that's a job we might expect Craft/YII to handle for us. I'm surprised you have a different opinion about that, reasoning that so called 'advanced users' would want to treat fields differently for one selector over the other. To me that doesn't make sense. Also I'd say that groupBy is not an advanced feature, it's just an extension of a sql command, just like orderBy is.

2) It is not clear what selector expects a handle and what selector expects a db-column name

Still I might understand where you're coming from, but please then at least change the name of the selector so we at least know we can expect this what you call 'advanced' behaviour from it, so it expects a db-column-name instead of a handle.
Right now there's no way we could know by the selector name.

I'd say keep the normal selectors as using handles and rename the selectors (for the time being) which are using db-column-names to something like groupByColName or whatever. But still I think that's not a great idea and I don't get why it's that hard to just use handles here too. I might be missing something, but isn't it just a simple conversion between column name to handle, as Craft already knows it? Than it would be consistent throughout and we don't have all the confusion.

Also, even if you don't have the time to change this or there's a compatibility issue users will face when changing, IMO at least the documentation could show a warning about these different field behaviours. Like Caution!! This is considered an advanced selector, don't use handles here, but db-column-names instead. Look into your sql table to find out their names and check them once in a while to be absolutely sure they are still the same, as for instance, databases, created with Craft version prior to 3.7 might mess up with the postfixes in the db-field name, causing the selector to break.

3) There are worries about being able to rely on the db-column name to be always the exact same

I appreciate you give more information about it now. But this really doesn't ease my concern really as some projects were created before Craft 3.7 and I still have the feeling we can't be absolutely sure this will never change either.

To be honest, it buggles me why this issue is moved to the discussion 'forum'. To me it's a nr 1 developer-rule that methods and behaviours should always be consistent and I wouldn't call that debatable. And even if they should not be consistant IMO we should at least notice it from its selector name. It seems like an easy conversion to me to add to these functions too, but I might be missing something.

Don't get me wrong, I understand you guys are busy, but moving stuff to the discussion forum to get rid of an issue doesn't seem like the best thing to do for such a thing. My 2cts.

How to get the db fieldname by handle in code?

The fieldname must be stored by craft somewhere. To at least ease my concern about the db-column-names might change: how can we request what db-field-name is used under water from a handle? Than we can make that conversion ourselves for now.

Thanks in advance!

[brandonkelly]

I'm surprised you have a different opinion about that, reasoning that so called 'advanced users' would want to treat fields differently for one selector over the other. To me that doesn't make sense. Also I'd say that groupBy is not an advanced feature, it's just an extension of a sql command, just like orderBy is.

Well, we don’t list groupBy(), etc., in any of the normal element query param lists (e.g. Querying Entries). The only place those methods are mentioned is under the Advanced Element Queries section of the element query docs.

I just added a tip about how custom fields must be referenced by their full column name to that page.

How to get the db fieldname by handle in code?

You can use craft\helpers\ElementHelper::fieldColumnFromField(), however it is only reliable for global fields where the field’s column prefix is going to be field_. For other fields such as Matrix sub-fields, you will need to ensure the field’s fieldPrefix property has been set properly first.