4

I am building a feature which will conditionally need to output entity data. The entity type is a custom one I created, which is complex and implements multiple nested field collections.

My issue is that looping through entities using Entity::loadMultiple() is very slow, as each entity needs to use FieldCollectionItem::load() multiple times to get to the data it needs to output. Over 1000 entity instances this uses up a lot of resources and takes a long time to load.

I am trying to circumvent this by retrieving the data via a SQL query and joining tables together. I'm hitting a road block when retrieving data from field collection items with long machine names, as the data appears to be stored in tables with names like field_collection_item__5d28be6634, basically just "field_collection_item__" with a 10-character hexadecimal hash added to it.

This would not theoretically be a big issue, except it looks like all records for the same field collection item seem to be split up across many of these tables with no pattern or connection that I can discern.

How do I know which tables to retrieve my data from for any given parent field collection instance?

Edit: I have revisited this recently and thought some of these tables might be revision tables, but none of them include the "_r_" in the table name. I still cannot determine which tables to use as there seem to be multiple tables (not including revision tables) for one field collection item. Looping through entities and loading nested field collections is taking upwards of 5 minutes which is just too long for my users.

1 Answer 1

3
+50

In Drupal 8 the field tables are created as [ENTITY_TYPE]__[FIELD_NAME] and [ENTITY_TYPE]_revision__[FIELD_NAME] in database. The [ENTITY_TYPE]__[FIELD_NAME] is a main field table where latest revision data of entity field is stored. Where as [ENTITY_TYPE]_revision__[FIELD_NAME] is field revision table where all revision data is stored for a field.

In your case I think your Drupal/Database has some issue, because of which the table name has random character (10 characters) hexadecimal in it. I'm not sure of exact reason of this, but I too have notice this sometime in my Drupal Site (only twice one in D7 and one in D8) not every time.

In such case, where you're unsure of which table is used for which field, you can use name and value column in key_value table to get field table mapping. The name column contains, the entity and field name and value column contains serialized array of field schema stored in blob type. The name column format is as below:

[ENTITY_TYPE].field_schema_data.[FIELD_NAME]

So if I've field named as field_test in field_collection_item entity, then the mapping will be stored as field_collection_item.field_schema_data.field_test in key_value table. Check the screenshot below:

Screenshot 1: key_value table showing record of field_test field. key_value table

Screenshot 2: value column data from key_value table for field_test field. value column data from key_value table

I know this is not a better solution but, if table name doesn't has field name in it you can use data in value column in key_value table, you can find which table is used for which field.

Hope this helps.

4
  • Amazing! I was able to use this information to construct an associative array of field names and their respective database tables, which I was then able to use to put a complex query together. Thank you so much for the great post, this is exactly what I needed. This has reduced processing time from 5 minutes to 30 seconds.
    – saramm1
    Nov 25, 2016 at 18:27
  • Great, 30 sec from 5 minutes is too great.
    – Yogesh
    Nov 26, 2016 at 10:46
  • I think this is the reason, drupal.org/project/drupal/issues/1709960, limit on length of field names
    – troseman
    Mar 16, 2018 at 17:57
  • This was a lifesaver, much thanks. I now need to figure out how to add the field name from the blog in my sql query 🤔
    – frazras
    Sep 18, 2019 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.