Add support for other database engines besides MySQL for subgroup_group_tree_update operation

Problem/Motivation

The Subgroup module currently encounters issues when performing database updates involving the group__subgroup_right and group__subgroup_tree tables on PostgreSQL (and potentially other non-MySQL database engines). Specifically, the subgroup_group_tree_update operation attempts to execute an UPDATE query with an INNER JOIN syntax that is only compatible with MySQL, leading to a syntax error at or near "INNER" on PostgreSQL.

This issue causes critical database transaction failures, including failed rollbacks, potentially leaving the database in an inconsistent state regarding subgroup tree structures. The error message typically seen is:
Error attempting rollback of 684ff57bcd63f4.81214381\subgroup_group_tree_update. Active stack: 684ff57bb5e6a9.83750840\drupal_transaction
Followed by the specific SQL syntax error:

Error in query (7): ERROR: syntax error at or near "INNER" LINE 2: INNER JOIN group__subgroup_tree t2 ON t1.entity_id = t2.enti...
The root cause is the use of a non-standard SQL UPDATE ... INNER JOIN syntax within a Drupal module that should leverage Drupal's Database API for cross-database compatibility. Drupal's UpdateQuery and DeleteQuery builders do not directly support JOIN clauses, requiring alternative patterns like subqueries to achieve cross-table updates.

Steps to reproduce

1. Set up a Drupal site with PostgreSQL as the database backend.
2. Install and configure the Subgroup module.
3. Ensure the group__subgroup_right and group__subgroup_tree tables are present (e.g., by creating group types or subgroups that trigger their creation/population).
4. Perform an operation that triggers the subgroup_group_tree_update logic within the module. This typically happens when creating group hierarchies, permissions related to subgroups, or similar administrative actions that cause the module to update the right/left values in the subgroup tree. (Specific steps might vary depending on which exact action triggers the update, but any action that manipulates the subgroup tree structure should suffice).
5. Observe the PHP error logs and/or Drupal watchdog logs for the syntax error related to INNER JOIN and the subsequent rollback failure.

Proposed resolution

The proposed resolution is to refactor the SQL UPDATE statement that uses the INNER JOIN into a standard SQL pattern that is compatible with Drupal's Database API and works across different database engines, specifically PostgreSQL. This can be achieved by using a subquery to identify the entity_id values to be updated.

Current (Problematic) SQL Query (MySQL-specific syntax):

UPDATE group__subgroup_right t1
INNER JOIN group__subgroup_tree t2 ON t1.entity_id = t2.entity_id
SET t1.subgroup_right_value = t1.subgroup_right_value + 2
WHERE t1.subgroup_right_value >= 2
AND t2.subgroup_tree_value = 335

Proposed SQL Query (Standard SQL using subquery):

UPDATE group__subgroup_right
SET subgroup_right_value = subgroup_right_value + 2
WHERE subgroup_right_value >= 2
AND entity_id IN (
    SELECT entity_id
    FROM group__subgroup_tree
    WHERE subgroup_tree_value = 335
);

Implementation using Drupal's Database API:

The module's code should be updated to construct this query using the Drupal\Core\Database\Connection::update() and Drupal\Core\Database\Connection::select() methods, leveraging the condition() method with a SelectQuery object for the IN clause.

Example snippet (conceptual, as the exact location in the module's code needs to be identified):

// Assuming $database is an instance of \Drupal\Core\Database\Connection
$subquery = $database->select('group__subgroup_tree', 'gst');
$subquery->fields('gst', ['entity_id']);
$subquery->condition('gst.subgroup_tree_value', 335); // Replace with actual value/variable

$update_query = $database->update('group__subgroup_right');
$update_query->expression('subgroup_right_value', 'subgroup_right_value + :increment', [':increment' => 2]); // Replace 2 with actual increment value/variable
$update_query->condition('subgroup_right_value', 2, '>='); // Replace 2 with actual comparison value/variable
$update_query->condition('entity_id', $subquery, 'IN');
$update_query->execute();

This approach ensures that the module's database operations are compatible with all database engines supported by Drupal's Database API.