Fix formatting inconsistency in organization detail subquery annotation

Break the long .annotate() line across multiple lines to match the style used in mixin.py.

.github/workflows/spec-sync-on-merge.yml

@@ -113,11 +113,19 @@ jobs:
         env:
           GH_TOKEN: ${{ secrets.OPENAPI_SPEC_SYNC_TOKEN }}
           COMMIT_MESSAGE: ${{ github.event.head_commit.message }}
-          SPEC_REPO: ansible-automation-platform/aap-openapi-specs
         run: |
+          # Configure git
+          git config user.name "github-actions[bot]"
+          git config user.email "github-actions[bot]@users.noreply.github.com"
+
+          # Create branch for PR
           SHORT_SHA="${{ github.sha }}"
           SHORT_SHA="${SHORT_SHA:0:7}"
           BRANCH_NAME="update-Controller-${{ github.ref_name }}-${SHORT_SHA}"
+          git checkout -b "$BRANCH_NAME"
+
+          # Add and commit changes
+          git add "controller.json"
 
           if [ "${{ steps.compare.outputs.is_new_file }}" == "true" ]; then
             COMMIT_MSG="Add Controller OpenAPI spec for ${{ github.ref_name }}"
@@ -125,38 +133,15 @@ jobs:
             COMMIT_MSG="Update Controller OpenAPI spec for ${{ github.ref_name }}"
           fi
 
-          COMMIT_MSG="${COMMIT_MSG}
+          git commit -m "$COMMIT_MSG
 
           Synced from ${{ github.repository }}@${{ github.sha }}
-          Source branch: ${{ github.ref_name }}"
+          Source branch: ${{ github.ref_name }}
 
-          # Create branch via API
-          BASE_SHA=$(gh api "repos/${SPEC_REPO}/git/ref/heads/${{ github.ref_name }}" --jq '.object.sha')
-          gh api "repos/${SPEC_REPO}/git/refs" \
-            -f "ref=refs/heads/${BRANCH_NAME}" \
-            -f "sha=${BASE_SHA}"
+          Co-Authored-By: github-actions[bot] <github-actions[bot]@users.noreply.github.com>"
 
-          # Create blob and commit via API (commits created through the API are automatically signed by GitHub)
-          BLOB_SHA=$(gh api "repos/${SPEC_REPO}/git/blobs" \
-            -f "content=$(base64 -w 0 controller.json)" \
-            -f "encoding=base64" \
-            --jq '.sha')
-
-          TREE_SHA=$(gh api "repos/${SPEC_REPO}/git/trees" \
-            -f "base_tree=${BASE_SHA}" \
-            --input <(jq -n --arg blob "$BLOB_SHA" '{tree: [{path: "controller.json", mode: "100644", type: "blob", sha: $blob}]}') \
-            --jq '.sha')
-
-          NEW_COMMIT_SHA=$(gh api "repos/${SPEC_REPO}/git/commits" \
-            -f "message=${COMMIT_MSG}" \
-            -f "tree=${TREE_SHA}" \
-            -f "parents[]=${BASE_SHA}" \
-            --jq '.sha')
-
-          # Update branch ref to point to the new signed commit
-          gh api "repos/${SPEC_REPO}/git/refs/heads/${BRANCH_NAME}" \
-            -X PATCH \
-            -f "sha=${NEW_COMMIT_SHA}"
+          # Push branch
+          git push origin "$BRANCH_NAME"
 
           # Create PR
           PR_TITLE="[${{ github.ref_name }}] Update Controller spec from merged commit"
@@ -180,7 +165,6 @@ jobs:
           🤖 This PR was automatically generated by the OpenAPI spec sync workflow."
 
           gh pr create \
-            --repo "${SPEC_REPO}" \
             --title "$PR_TITLE" \
             --body "$PR_BODY" \
             --base "${{ github.ref_name }}" \

awx/api/views/mixin.py

@@ -4,7 +4,8 @@
 import dateutil
 import logging
 
-from django.db.models import Count
+from django.db.models import Count, IntegerField, OuterRef, Subquery
+from django.db.models.functions import Coalesce
 from django.db import transaction
 from django.shortcuts import get_object_or_404
 from django.utils.timezone import now
@@ -15,7 +16,7 @@ from rest_framework.response import Response
 from rest_framework import status
 
 from awx.main.constants import ACTIVE_STATES
-from awx.main.models import Organization
+from awx.main.models import Organization, Role
 from awx.main.utils import get_object_or_400
 from awx.main.models.ha import Instance, InstanceGroup, schedule_policy_task
 from awx.main.models.organization import Team
@@ -178,9 +179,28 @@ class OrganizationCountsMixin(object):
         db_results['projects'] = project_qs.values('organization').annotate(Count('organization')).order_by('organization')
 
         # Other members and admins of organization are always viewable
-        db_results['users'] = org_qs.annotate(users=Count('member_role__members', distinct=True), admins=Count('admin_role__members', distinct=True)).values(
-            'id', 'users', 'admins'
+        #
+        # Use independent subqueries instead of double-JOIN Count to avoid
+        # cartesian product.
+        role_members_through = Role.members.through
+        member_count = Subquery(
+            role_members_through.objects.filter(role_id=OuterRef('member_role_id'))
+            .values('role_id')
+            .annotate(cnt=Count('user_id', distinct=True))
+            .values('cnt'),
+            output_field=IntegerField(),
         )
+        admin_count = Subquery(
+            role_members_through.objects.filter(role_id=OuterRef('admin_role_id'))
+            .values('role_id')
+            .annotate(cnt=Count('user_id', distinct=True))
+            .values('cnt'),
+            output_field=IntegerField(),
+        )
+        db_results['users'] = org_qs.annotate(
+            users=Coalesce(member_count, 0),
+            admins=Coalesce(admin_count, 0),
+        ).values('id', 'users', 'admins')
 
         count_context = {}
         for org in org_id_list:

awx/api/views/organization.py

@@ -5,7 +5,8 @@
 import logging
 
 # Django
-from django.db.models import Count
+from django.db.models import Count, IntegerField, OuterRef, Subquery
+from django.db.models.functions import Coalesce
 from django.contrib.contenttypes.models import ContentType
 from django.utils.translation import gettext_lazy as _
 
@@ -77,9 +78,29 @@ class OrganizationDetail(RelatedJobsPreventDeleteMixin, RetrieveUpdateDestroyAPI
 
         org_counts = {}
         access_kwargs = {'accessor': self.request.user, 'role_field': 'read_role'}
+        # Use independent subqueries instead of double-JOIN Count to avoid
+        # cartesian product.
+        role_members_through = Role.members.through
+        member_count = Subquery(
+            role_members_through.objects.filter(role_id=OuterRef('member_role_id'))
+            .values('role_id')
+            .annotate(cnt=Count('user_id', distinct=True))
+            .values('cnt'),
+            output_field=IntegerField(),
+        )
+        admin_count = Subquery(
+            role_members_through.objects.filter(role_id=OuterRef('admin_role_id'))
+            .values('role_id')
+            .annotate(cnt=Count('user_id', distinct=True))
+            .values('cnt'),
+            output_field=IntegerField(),
+        )
         direct_counts = (
             Organization.objects.filter(id=org_id)
-            .annotate(users=Count('member_role__members', distinct=True), admins=Count('admin_role__members', distinct=True))
+            .annotate(
+                users=Coalesce(member_count, 0),
+                admins=Coalesce(admin_count, 0),
+            )
             .values('users', 'admins')
         )