Duplicate LEFT JOIN when listing members

[ChristophWurst]

How to use GitHub

• Please use the 👍 reaction to show that you are affected by the same issue.
• Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
• Subscribe to receive notifications on status change and new comments.

---

Steps to reproduce

1. ???
2. Look at db logs

Expected behaviour

Efficient queries.

Actual behaviour

...
FROM oc_circles_member c
LEFT JOIN oc_circles_circle c_g ON c_g.unique_id = c.single_id
LEFT JOIN oc_circles_circle c_b ON c_b.unique_id = c.circle_id
LEFT JOIN oc_circles_circle c_o ON c.invited_by = c_o.unique_id
LEFT JOIN oc_circles_member c_b_d ON (c_b_d.circle_id = c_b.unique_id)
AND (c_b_d.level = 9)
LEFT JOIN oc_circles_member c_o_d ON (c_o_d.circle_id = c_o.unique_id)
AND (c_o_d.level = 9)
WHERE (c.circle_id = 'abcdefg')
AND (c.instance = '')
AND (c.level >= 1)
ORDER BY c.level DESC,
c.cached_name ASC

^ oc_circles_member is joined twice.

Server configuration

Web server: Apache/Nginx

Database: MySQL/Maria/SQLite/PostgreSQL

PHP version: 8.1/8.2/8.3

Nextcloud version: (see Nextcloud admin page)

List of activated apps

If you have access to your command line run e.g.:
sudo -u www-data php occ app:list
from within your Nextcloud installation folder

Nextcloud configuration

If you have access to your command line run e.g.:
sudo -u www-data php occ config:list system
from within your Nextcloud installation folder

Browser

Browser name: Firefox/Chrome/Safari/…

Browser version: 124/125/…

Operating system: Windows/Ubuntu/Mac/…

Browser log

Insert your browser log here, this could for example include:
a) The javascript console log
b) The network log
c) ...

[ChristophWurst]

Steps to reproduce

1. ???

2. Look at db logs

As far as I can debug it's the query to list circle members. It looks like this:

SELECT
    `c`.`circle_id`,
    `c`.`member_id`,
    `c`.`single_id`,
    `c`.`user_id`,
    `c`.`instance`,
    `c`.`user_type`,
    `c`.`level`,
    `c`.`status`,
    `c`.`note`,
    `c`.`contact_id`,
    `c`.`cached_name`,
    `c`.`cached_update`,
    `c`.`contact_meta`,
    `c`.`joined`,
    `c_g`.`unique_id` AS `c_g_unique_id`,
    `c_g`.`name` AS `c_g_name`,
    `c_g`.`display_name` AS `c_g_display_name`,
    `c_g`.`sanitized_name` AS `c_g_sanitized_name`,
    `c_g`.`source` AS `c_g_source`,
    `c_g`.`description` AS `c_g_description`,
    `c_g`.`settings` AS `c_g_settings`,
    `c_g`.`config` AS `c_g_config`,
    `c_g`.`contact_addressbook` AS `c_g_contact_addressbook`,
    `c_g`.`contact_groupname` AS `c_g_contact_groupname`,
    `c_g`.`creation` AS `c_g_creation`,
    `c_g_d`.`circle_id` AS `c_g_d_circle_id`,
    `c_g_d`.`member_id` AS `c_g_d_member_id`,
    `c_g_d`.`single_id` AS `c_g_d_single_id`,
    `c_g_d`.`user_id` AS `c_g_d_user_id`,
    `c_g_d`.`instance` AS `c_g_d_instance`,
    `c_g_d`.`user_type` AS `c_g_d_user_type`,
    `c_g_d`.`level` AS `c_g_d_level`,
    `c_g_d`.`status` AS `c_g_d_status`,
    `c_g_d`.`note` AS `c_g_d_note`,
    `c_g_d`.`contact_id` AS `c_g_d_contact_id`,
    `c_g_d`.`cached_name` AS `c_g_d_cached_name`,
    `c_g_d`.`cached_update` AS `c_g_d_cached_update`,
    `c_g_d`.`contact_meta` AS `c_g_d_contact_meta`,
    `c_g_d`.`joined` AS `c_g_d_joined`,
    `c_b`.`unique_id` AS `c_b_unique_id`,
    `c_b`.`name` AS `c_b_name`,
    `c_b`.`display_name` AS `c_b_display_name`,
    `c_b`.`sanitized_name` AS `c_b_sanitized_name`,
    `c_b`.`source` AS `c_b_source`,
    `c_b`.`description` AS `c_b_description`,
    `c_b`.`settings` AS `c_b_settings`,
    `c_b`.`config` AS `c_b_config`,
    `c_b`.`contact_addressbook` AS `c_b_contact_addressbook`,
    `c_b`.`contact_groupname` AS `c_b_contact_groupname`,
    `c_b`.`creation` AS `c_b_creation`,
    `c_b_i`.`circle_id` AS `c_b_i_circle_id`,
    `c_b_i`.`member_id` AS `c_b_i_member_id`,
    `c_b_i`.`single_id` AS `c_b_i_single_id`,
    `c_b_i`.`user_id` AS `c_b_i_user_id`,
    `c_b_i`.`instance` AS `c_b_i_instance`,
    `c_b_i`.`user_type` AS `c_b_i_user_type`,
    `c_b_i`.`level` AS `c_b_i_level`,
    `c_b_i`.`status` AS `c_b_i_status`,
    `c_b_i`.`note` AS `c_b_i_note`,
    `c_b_i`.`contact_id` AS `c_b_i_contact_id`,
    `c_b_i`.`cached_name` AS `c_b_i_cached_name`,
    `c_b_i`.`cached_update` AS `c_b_i_cached_update`,
    `c_b_i`.`contact_meta` AS `c_b_i_contact_meta`,
    `c_b_i`.`joined` AS `c_b_i_joined`,
    `c_b_h`.`circle_id` AS `c_b_h_circle_id`,
    `c_b_h`.`member_id` AS `c_b_h_member_id`,
    `c_b_h`.`single_id` AS `c_b_h_single_id`,
    `c_b_h`.`user_id` AS `c_b_h_user_id`,
    `c_b_h`.`instance` AS `c_b_h_instance`,
    `c_b_h`.`user_type` AS `c_b_h_user_type`,
    `c_b_h`.`level` AS `c_b_h_level`,
    `c_b_h`.`status` AS `c_b_h_status`,
    `c_b_h`.`note` AS `c_b_h_note`,
    `c_b_h`.`contact_id` AS `c_b_h_contact_id`,
    `c_b_h`.`cached_name` AS `c_b_h_cached_name`,
    `c_b_h`.`cached_update` AS `c_b_h_cached_update`,
    `c_b_h`.`contact_meta` AS `c_b_h_contact_meta`,
    `c_b_h`.`joined` AS `c_b_h_joined`,
    `c_b_h_n`.`circle_id` AS `c_b_h_n_circle_id`,
    `c_b_h_n`.`member_id` AS `c_b_h_n_member_id`,
    `c_b_h_n`.`single_id` AS `c_b_h_n_single_id`,
    `c_b_h_n`.`user_id` AS `c_b_h_n_user_id`,
    `c_b_h_n`.`instance` AS `c_b_h_n_instance`,
    `c_b_h_n`.`user_type` AS `c_b_h_n_user_type`,
    `c_b_h_n`.`level` AS `c_b_h_n_level`,
    `c_b_h_n`.`status` AS `c_b_h_n_status`,
    `c_b_h_n`.`note` AS `c_b_h_n_note`,
    `c_b_h_n`.`contact_id` AS `c_b_h_n_contact_id`,
    `c_b_h_n`.`cached_name` AS `c_b_h_n_cached_name`,
    `c_b_h_n`.`cached_update` AS `c_b_h_n_cached_update`,
    `c_b_h_n`.`contact_meta` AS `c_b_h_n_contact_meta`,
    `c_b_h_n`.`joined` AS `c_b_h_n_joined`,
    `c_b_j`.`single_id` AS `c_b_h_n_j_single_id`,
    `c_b_j`.`circle_id` AS `c_b_h_n_j_circle_id`,
    `c_b_j`.`level` AS `c_b_h_n_j_level`,
    `c_b_j`.`inheritance_first` AS `c_b_h_n_j_inheritance_first`,
    `c_b_j`.`inheritance_last` AS `c_b_h_n_j_inheritance_last`,
    `c_b_j`.`inheritance_path` AS `c_b_h_n_j_inheritance_path`,
    `c_b_j`.`inheritance_depth` AS `c_b_h_n_j_inheritance_depth`,
    `c_b_h_g`.`unique_id` AS `c_b_h_g_unique_id`,
    `c_b_h_g`.`name` AS `c_b_h_g_name`,
    `c_b_h_g`.`display_name` AS `c_b_h_g_display_name`,
    `c_b_h_g`.`sanitized_name` AS `c_b_h_g_sanitized_name`,
    `c_b_h_g`.`source` AS `c_b_h_g_source`,
    `c_b_h_g`.`description` AS `c_b_h_g_description`,
    `c_b_h_g`.`settings` AS `c_b_h_g_settings`,
    `c_b_h_g`.`config` AS `c_b_h_g_config`,
    `c_b_h_g`.`contact_addressbook` AS `c_b_h_g_contact_addressbook`,
    `c_b_h_g`.`contact_groupname` AS `c_b_h_g_contact_groupname`,
    `c_b_h_g`.`creation` AS `c_b_h_g_creation`,
    `c_b_d`.`circle_id` AS `c_b_d_circle_id`,
    `c_b_d`.`member_id` AS `c_b_d_member_id`,
    `c_b_d`.`single_id` AS `c_b_d_single_id`,
    `c_b_d`.`user_id` AS `c_b_d_user_id`,
    `c_b_d`.`instance` AS `c_b_d_instance`,
    `c_b_d`.`user_type` AS `c_b_d_user_type`,
    `c_b_d`.`level` AS `c_b_d_level`,
    `c_b_d`.`status` AS `c_b_d_status`,
    `c_b_d`.`note` AS `c_b_d_note`,
    `c_b_d`.`contact_id` AS `c_b_d_contact_id`,
    `c_b_d`.`cached_name` AS `c_b_d_cached_name`,
    `c_b_d`.`cached_update` AS `c_b_d_cached_update`,
    `c_b_d`.`contact_meta` AS `c_b_d_contact_meta`,
    `c_b_d`.`joined` AS `c_b_d_joined`,
    `c_o`.`unique_id` AS `c_o_unique_id`,
    `c_o`.`name` AS `c_o_name`,
    `c_o`.`display_name` AS `c_o_display_name`,
    `c_o`.`sanitized_name` AS `c_o_sanitized_name`,
    `c_o`.`source` AS `c_o_source`,
    `c_o`.`description` AS `c_o_description`,
    `c_o`.`settings` AS `c_o_settings`,
    `c_o`.`config` AS `c_o_config`,
    `c_o`.`contact_addressbook` AS `c_o_contact_addressbook`,
    `c_o`.`contact_groupname` AS `c_o_contact_groupname`,
    `c_o`.`creation` AS `c_o_creation`,
    `c_o_d`.`circle_id` AS `c_o_d_circle_id`,
    `c_o_d`.`member_id` AS `c_o_d_member_id`,
    `c_o_d`.`single_id` AS `c_o_d_single_id`,
    `c_o_d`.`user_id` AS `c_o_d_user_id`,
    `c_o_d`.`instance` AS `c_o_d_instance`,
    `c_o_d`.`user_type` AS `c_o_d_user_type`,
    `c_o_d`.`level` AS `c_o_d_level`,
    `c_o_d`.`status` AS `c_o_d_status`,
    `c_o_d`.`note` AS `c_o_d_note`,
    `c_o_d`.`contact_id` AS `c_o_d_contact_id`,
    `c_o_d`.`cached_name` AS `c_o_d_cached_name`,
    `c_o_d`.`cached_update` AS `c_o_d_cached_update`,
    `c_o_d`.`contact_meta` AS `c_o_d_contact_meta`,
    `c_o_d`.`joined` AS `c_o_d_joined`
FROM
    `*PREFIX*circles_member` `c`
        LEFT JOIN `*PREFIX*circles_circle` `c_g` ON `c_g`.`unique_id` = `c`.`single_id`
        LEFT JOIN `*PREFIX*circles_circle` `c_b` ON `c_b`.`unique_id` = `c`.`circle_id`
        LEFT JOIN `*PREFIX*circles_circle` `c_o` ON `c`.`invited_by` = `c_o`.`unique_id`
        LEFT JOIN `*PREFIX*circles_membership` `c_g_j` ON (`c_g_j`.`single_id` = :dcValue1)
        AND (`c_g_j`.`circle_id` = `c_g`.`unique_id`)
        LEFT JOIN `*PREFIX*circles_member` `c_g_d` ON (`c_g_d`.`circle_id` = `c_g`.`unique_id`)
        AND (`c_g_d`.`level` = :dcValue2)
        LEFT JOIN `*PREFIX*circles_membership` `c_b_j` ON (`c_b_j`.`single_id` = :dcValue4)
        AND (`c_b_j`.`circle_id` = `c_b`.`unique_id`)
        LEFT JOIN `*PREFIX*circles_member` `c_b_i` ON (`c_b_i`.`single_id` = :dcValue5)
        AND (`c_b_i`.`circle_id` = `c_b`.`unique_id`)
        LEFT JOIN `*PREFIX*circles_member` `c_b_d` ON (`c_b_d`.`circle_id` = `c_b`.`unique_id`)
        AND (`c_b_d`.`level` = :dcValue14)
        LEFT JOIN `*PREFIX*circles_circle` `c_b_j_k` ON (`c_b_j`.`circle_id` = `c_b_j_k`.`unique_id`)
        OR (`c_b_i`.`circle_id` = `c_b_j_k`.`unique_id`)
        LEFT JOIN `*PREFIX*circles_member` `c_b_h` ON (
                                                          `c_b_j`.`inheritance_first` = `c_b_h`.`single_id`
                                                          )
        AND (`c_b_j`.`circle_id` = `c_b_h`.`circle_id`)
        LEFT JOIN `*PREFIX*circles_member` `c_b_h_n` ON (`c_b_j`.`single_id` = `c_b_h_n`.`single_id`)
        AND (
                                                            `c_b_j`.`inheritance_last` = `c_b_h_n`.`circle_id`
                                                            )
        LEFT JOIN `*PREFIX*circles_circle` `c_b_h_g` ON `c_b_h_g`.`unique_id` = `c_b_h`.`single_id`
        LEFT JOIN `*PREFIX*circles_member` `c_o_d` ON (`c_o_d`.`circle_id` = `c_o`.`unique_id`)
        AND (`c_o_d`.`level` = :dcValue15)
WHERE
    (`c`.`circle_id` = :dcValue3)
  AND (
    (
        ((`c_b_j_k`.`config` & 2) > :dcValue6)
            AND (`c_b_j`.`level` = :dcValue7)
        )
        OR (
        (
            (`c_b_j`.`level` >= :dcValue8)
                OR (`c_b_i`.`level` >= :dcValue9)
            )
            AND ((`c_b_j_k`.`config` & 2) = :dcValue10)
        )
        OR (
        ((`c_b`.`config` & 16) > :dcValue11)
            AND ((`c_b`.`config` & 8) > :dcValue12)
            AND ((`c_b`.`config` & 64) = :dcValue13)
        )
    )
ORDER BY
    `c`.`level` desc,
    `c`.`cached_name` asc

\OCA\Circles\Db\MemberRequest::getMembers calls \OCA\Circles\Db\MemberRequestBuilder::getMemberSelectSql, which joins the members alreay, then \OCA\Circles\Db\CoreQueryBuilder::leftJoinCircle joins them again on the identical condition.