Add a index table for group to group content relationship

joelpittet → credited claudiu.cristea → .

Created a schema first crack as a WIP/PoC

@joelpittet thanks. What is the real-world use case for this? Do you have multiple OG reference fields because each one is attached to a different subscription type (e.g., "normal" subscription vs. "premium" one)?

@amitaibu thanks for chiming in! I hope this helps illustrate, let me know if it's not clear (or you have a better suggestion)

We are in the process of migrating a complex Drupal 7 intranet, and one of the challenges we’re facing involves a “documentation” (group content) node bundle with multiple group references. Specifically, this bundle has three separate entity reference fields for groups:

1. Committee: og_group_ref referencing committees
2. Also allow access by: og_group_ref_also referencing user groups like Students or Faculty members
3. Responsible staff: field_user_groups referencing groups of staff

To help illustrate this, here are screenshots:
→

→

In Drupal 7, we were able to present a View that lists group content for users by passing group IDs (associated with the user) as arguments to a contextual filter. This setup allowed us to display the latest group content for all groups a user belongs to.

However, in Drupal 8/9, the relationships between group content and groups are stored in separate entity reference fields. This makes it difficult to achieve the same functionality without creating multiple contextual filters, one for each field, and handling the joins manually. The previous og_membership table was effectively a implicit taxonomy_index table as all the relationships were in one table, and thus setup with one relationship/join.

Additionally, we’ve encountered other similar requirements, such as:
For Events, where an event can belong to a “Committee” group or an “Event Series” group, each managed through separate entity reference fields.

This migration has highlighted the need for a more efficient way to consolidate these relationships, potentially through a flattened structure like a helper table/index proposed.

Thanks, the use case is clear now.

> The previous og_membership table was effectively a implicit taxonomy_index table

We're still using `og_membership` - couldn't that be used?

https://git.drupalcode.org/project/og/-/blob/dc7f4e138e90d4fa234894b3ec6...

Sorry, the {og_membership} table does exist, but doesn't help you in your use case. Since Og membership is now only for users (I now understand what you meant by "previous" 😊)

I have one more question before proceeding with a og_group_index table. Let's say these three fields were standard entity reference fields, not linked to OG. You would still require a helper table to help with the Views/ query. I'm curious if a solution for this might already exist.

Let's say these three fields were standard entity reference fields, not linked to OG. You would still require a helper table to help with the Views/ query. I'm curious if a solution for this might already exist.

We can easily create one Contextual Filter for one entity reference field (since OG 8.x group reference fields aka og_standard_reference essentially function as standard entity reference fields).

The problem arises when you need to filter “Group Content” that belongs to a “Group,” but each Group Content entity has a different set of entity reference fields. To handle this, you would need to add all the fields to the View's Contextual filters and use something like the views_contextual_filters_or → module to check across all the fields.

This approach becomes cumbersome and inefficient as the number of entity reference fields increases, which is why a index table (or an equivalent consolidated structure) could simplify the process. And of course I am stealing how they do this in Views core with the "Has taxonomy term ID" Contextual filter. public/core/modules/taxonomy/src/TermViewsData.php:173

Also this comment in core outlines why it's done:
public/core/modules/taxonomy/taxonomy.module:180

* Functions to maintain taxonomy indexing.
*
* Taxonomy uses default field storage to store canonical relationships
* between terms and fieldable entities. However its most common use case
* requires listing all content associated with a term or group of terms
* sorted by creation date. To avoid slow queries due to joining across
* multiple node and field tables with various conditions and order by criteria,
* we maintain a denormalized table with all relationships between terms,
* published nodes and common sort criteria such as status, sticky and created.
* When using other field storage engines or alternative methods of
* denormalizing this data you should set the
* taxonomy.settings:maintain_index_table to '0' to avoid unnecessary writes in
* SQL.

I believe a generic Entity reference solution might have been helpful, such as a module that normalizes the ER references to a single table. However, I couldn't find one available, and "Drupal core to handle this" will likely not happen.

So, let's continue with your MR; thank you 🙏

Some remarks in the MR. Also needs tests.

More considerations:

• Probably og_group_index_id argument plugin should expose an "entity type" configurable option. Only the Group ID is not enough (it is when you have only nodes).
• If the storage of group content entity or the configured group entity type is not implementing SqlEntityStorageInterface, there's nothing we can do... just don't support this argument plugin. Exit here.
• Otherwise, iterate over all og_standard_reference field storage definitions of the configured entities and collect a list of tables and group reference columns. E.g., an entry in this list could be node__og_audience => og_audience_target_id. We can do this by using probably SqlEntityStorageInterface::getTableMapping()
• Having this list, in og_group_index_id argument plugin, create an UNION query between all these tables. It shouldn’t be difficult because they share the same structure only with different table/column names. The query should filter on Group ID and return a list of group content IDs.
• Use the list to add a new WHERE to the Views query and limit the results

Not against the proposed solution but maintaining redundant data is something I’m not big fan of.