Change where custom fields are stored

[brianteeman]

Today

The current database structure for custom fields is

• The structure of the custom field is stored in #__fields
• The content of the field is stored in #__field_values

#__field_values has three fields
field_id ->what type of field is it links to #__fields
item_id->which article is this for
value -> content of the field

This has several well known limitations

• no versioning for the fields
• no search (might be solved now)
• no inclusion of the field in the list views
• complex searches to build the complete content item

Tomorrow

This idea is just an idea. I don't know if its feasible but it sounds like it should be.

When you create a field is would still save the field structure in #__fields
BUT and this is the critical part
When you create the field it also adds a field to the #__content table

So now the field data is stored inside the content item. This would immediately resolve the 4 limitations above.

Backwards compatibility

Probably zero
but you could easily have a script that would add the fields to #__content and then move the data from #__field_values

[brianteeman]

An alternative would be to add an additional field to the #__content table at install/upgrade and to store all the field data as json in that field

[denysdesign]

Nice idea

[b2z]

Historically the content table is designed for articles. Now you want to place there the content from all other entities including extensions that support custom fields. Does not sound so good.

[b2z]

So it is a b/c then.

[brianteeman]

was I not clear

[b2z]

Not really. Still require effort from 3rd party extensions, because in one moment fields will be broken.

[brianteeman]

I honestly dont know how I could have made it more clear

[brianteeman]

Not really. Still require effort from 3rd party extensions, because in one moment fields will be broken.

only if it was forced on everyone without notice or options to optin etc etc

please dont treat me like an idiot

[sergeytolkachyov]

You need to remember that Joomla is updated separately, and third - party extensions are updated separately. If the change happens in Joomla, and the third - party extension developers do not make changes to their extensions in advance, then there will be a lot of negativity.
There are things that are done very easily if done in a timely manner. And people who know what they're doing. Often sites are administered by people who can only click with the mouse and nothing more. To make changes that break backward compatibility, you need to advance for a long time (several months) and notify developers and site administrators more than once. To do this, use system messages in the admin panel, notifications to the site administrator's email. Then there is a chance that this really important notification will attract attention among the tons of information pouring out on us every minute.
And those who do not know how to work with the code will find those who can in advance. This will allow you to take care of users, because this is the ultimate goal of any business.

[brianteeman]

Please dont treat me like an idiot!!

[sergeytolkachyov]

Nobody takes anyone for an idiot. Idiots can't work with Joomla. And even quite mediocre people can't work, because you need to think and think well with her. Someone has 2 fields on the site for the entire project, and someone may have several thousand fields (catalogs of cars, car parts, apartments, etc.). You need to assume a variety of implementation scenarios and how this could potentially affect the final owners of the site.

[brianteeman]

of course.

maybe I should have assumed the people reading this were not very clever and written a ten thousand word proposal to explain a simple idea and address every little thing at once. but then no one would have read it.

dont teach me to suck eggs

[Fedik]

Sorry Brian, this is a bad idea, it will give a lot of headaches for maintain CMS, I mean maintaining DB structure. This is the reason why other CMS use dedicated table for custom fields.

[brianteeman]

looking at drupal they have a table for each field and a second table to the revisions
creating a new field creates a new pair of tables

[Fedik]

Yeah, they have a very complex structure :)

[Fedik]

There, if you have 100 extra fields there, you will have 100 extra SQL JOIN, to load them all.

[Fedik]

Hm, or we probably can do #__field_values_history, kind of.
Not a simple task, but shold be possible also.

[brianteeman]

exactly what drupal does

[Septdir]

It would be a great idea to get rid of EAV, and it would give a huge performance boost, but this is only possible if the field values are stored in JSON. And in order to work normally with JSON, you need to increase the requirements for databases to Mysql 8.0.21

Well, as for compatibility with the old structure, this is solved with the help of legacy and the update script. But no one listens to me anyway, so this is just my opinion.

[richard67]

And in order to work normally with JSON, you need to increase the requirements for databases to Mysql 8.0.21

What do you mean with that? The JSON data type? If so: Joomla cannot use that because Joomla has to work with MariaDB, too, and MariaDB does not have the JSON data type.

[richard67]

See https://mariadb.com/kb/en/json-data-type/ for the details.

[Septdir]

And in order to work normally with JSON, you need to increase the requirements for databases to Mysql 8.0.21

What do you mean with that? The JSON data type? If so: Joomla cannot use that because Joomla has to work with MariaDB, too, and MariaDB does not have the JSON data type.

Maria works great with json and has all the features you need. I use MariaDB myself, Maria 10.4.1 will be required to work correctly

[progreccor]

See https://mariadb.com/kb/en/json-data-type/ for the details.

actual version of MariaDB is 10.6 and it works with JSON like a charm

[alikon]

joomla already manage JSON "in some way" from the core
https://github.com/joomla/joomla-cms/blob/4.2-dev/installation/sql/mysql/base.sql#L16
as a silly example

so imho it's not matter of WHERE

much more of "WHAT" we need to store/retriieve

[jeckodevelopment]

Using your reasoning, we should store field content to "destination tables", so if it's a field linked to Category, you have the field content in the categories table, if you have a field for contacts, you have the content stored in the contact table.
Wouldn't be better to solve the issues that you pointed out keeping the current database structure? (i'm also wondering the impact on third-party extensions that implemented fields).