Duplicate entities in odata response

[nyordanoff]

Hello,

We have Bundle entity in our domain which has ManyToMany relation to both API and Destination entities:

• Bundle

@Entity(name = "consumptionBundle")
@Table(name = "tenants_bundles")
public class BundleEntity {
    ...
    @ManyToMany(mappedBy = "consumptionBundles", fetch = FetchType.LAZY)
    private Set<APIEntity> apis;

    @ManyToMany(mappedBy = "consumptionBundles", fetch = FetchType.LAZY)
    private Set<DestinationEntity> destinations;
    ...
}

• API

@Entity(name = "api")
@Table(name = "tenants_apis")
public class APIEntity {
    ...
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(
            name = "tenants_api_bundle_reference",
            joinColumns = {
                    @JoinColumn(name = "api_definition_id", referencedColumnName = "id"),
                    @JoinColumn(name = "formation_id", referencedColumnName = "formation_id"),
            },
            inverseJoinColumns = {
                    @JoinColumn(name = "bundle_id", referencedColumnName = "id"),
                    @JoinColumn(name = "formation_id", referencedColumnName = "formation_id"),
            }
    )
    private Set<BundleEntity> consumptionBundles;
   ...
}

• Destination

@Entity(name = "destination")
@Table(name = "tenants_destinations")
public class DestinationEntity {
    ...
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(
            name = "tenants_destinations",
            joinColumns = {
                    @JoinColumn(name = "id", referencedColumnName = "id"),
                    @JoinColumn(name = "formation_id", referencedColumnName = "formation_id"),
            },
            inverseJoinColumns = {
                    @JoinColumn(name = "bundle_id", referencedColumnName = "id"),
                    @JoinColumn(name = "formation_id", referencedColumnName = "formation_id"),
            }
    )
    private Set<BundleEntity> consumptionBundles;
    ...
}

For reproducing purposes, lets assume that we have 2 APIs pointing to 1 Bundle which Bundle has 1 Destination.

If we call /v0/consumptionBundles?$expand=destinations everything is OK -> we receive 1 Bundle with 1 Destination.

However, if we call /v0/apis?$expand=consumptionBundles($expand=destinations) there are duplicate Destinations in the response -> we receive 2 APIs (OK) with 1 Bundle in each (OK) with 2 same Destinations in each Bundle (should be 1 Dest):

{
    "@odata.context": "$metadata#apis(consumptionBundles(destinations()))",
    "value": [
        {
            "title": "foo-api-1",
            "id": "349af461-f94f-45c4-b4df-0a97513a5211",
            "consumptionBundles": [
                {
                    "title": "foo",
                    "id": "96fbf863-93ad-4c59-9464-61753e212e77",
                    "destinations": [
                        {
                            "name": "dest-foo",
                            "id": "22028605-5435-45b3-99bb-ae9a54cd9174"
                        },
                        {
                            "name": "dest-foo",
                            "id": "22028605-5435-45b3-99bb-ae9a54cd9174"
                        }
                    ]
                }
            ]
        },
        {
            "title": "bar-api-1",
            "id": "e710fee0-d954-476b-96d3-4e1b9e6007d1",
            "consumptionBundles": [
                {
                    "title": "foo",
                    "id": "96fbf863-93ad-4c59-9464-61753e212e77",
                    "destinations": [
                        {
                            "name": "dest-foo",
                            "id": "22028605-5435-45b3-99bb-ae9a54cd9174"                            
                        },
                        {
                            "name": "dest-foo",
                            "id": "22028605-5435-45b3-99bb-ae9a54cd9174"
                        }
                    ]
                }
            ]
        }
    ]
}

To locally reproduce this issue, you can:

• checkout the duplicate-destinations-issue-setup branch and start the service locally from it
• execute these insert statements so that you have the required bare minimum of entities needed

INSERT INTO public.business_tenant_mappings (id, external_name, external_tenant, provider_name, status, type) VALUES ('3e64ebae-38b5-46a0-b1ed-9ccee153a0ae', 'default', '3e64ebae-38b5-46a0-b1ed-9ccee153a0ae', 'Compass', 'Active', 'account') ON CONFLICT DO NOTHING;
INSERT INTO public.business_tenant_mappings (id, external_name, external_tenant, provider_name, status, type) VALUES ('1eba80dd-8ff6-54ee-be4d-77944d17b10b', 'foo', '1eba80dd-8ff6-54ee-be4d-77944d17b10b', 'Compass', 'Active', 'account') ON CONFLICT DO NOTHING;
INSERT INTO public.business_tenant_mappings (id, external_name, external_tenant, provider_name, status, type) VALUES ('af9f84a9-1d3a-4d9f-ae0c-94f883b33b6e', 'bar', 'af9f84a9-1d3a-4d9f-ae0c-94f883b33b6e', 'Compass', 'Active', 'account') ON CONFLICT DO NOTHING;

--

INSERT INTO public.applications (id, name, description, status_condition, status_timestamp, healthcheck_url, integration_system_id, provider_name, base_url, labels, ready, created_at, updated_at, deleted_at, error, app_template_id, correlation_ids, system_number, documentation_labels, system_status, local_tenant_id, application_namespace, tags) VALUES ('4c63e3b2-3301-4796-bc95-9fb5b2780342', 'system-instance-name', null, 'INITIAL', '2024-02-28 11:56:35.483467', null, null, 'compass', null, null, true, '2024-02-28 11:56:35.483562', null, null, null, null, null, null, null, null, null, null, null);

--

INSERT INTO public.tenant_applications (tenant_id, id, owner, source) VALUES ('3e64ebae-38b5-46a0-b1ed-9ccee153a0ae', '4c63e3b2-3301-4796-bc95-9fb5b2780342', true, '3e64ebae-38b5-46a0-b1ed-9ccee153a0ae');

--

INSERT INTO public.bundles (id, app_id, name, description, instance_auth_request_json_schema, default_instance_auth, ord_id, short_description, links, labels, credential_exchange_strategies, ready, created_at, updated_at, deleted_at, error, correlation_ids, documentation_labels, tags, version, resource_hash, local_tenant_id, app_template_version_id, last_update) VALUES ('96fbf863-93ad-4c59-9464-61753e212e77', '4c63e3b2-3301-4796-bc95-9fb5b2780342', 'foo', 'Foo bar', null, null, null, null, null, null, null, true, '2024-02-28 11:56:35.551719', null, null, null, null, null, null, null, null, null, null, null);

---

INSERT INTO public.app_templates (id, name, description, application_input, placeholders, access_level, application_namespace, created_at, updated_at) VALUES ('49d9b48b-efea-4935-a7f3-c0ca61223206', 'SAP app-template-name', 'app-template-desc', '{"name": "{{name}}", "labels": {"a": ["b", "c"], "d": ["e", "f"], "applicationType": "SAP app-template-name"}, "baseUrl": null, "bundles": null, "webhooks": [{"url": "http://url.com", "auth": null, "mode": null, "type": "CONFIGURATION_CHANGED", "timeout": null, "version": null, "urlTemplate": null, "inputTemplate": null, "retryInterval": null, "headerTemplate": null, "outputTemplate": null, "statusTemplate": null, "correlationIdKey": null}], "description": "test {{display-name}}", "providerName": "compass-tests", "localTenantID": null, "healthCheckURL": "http://url.valid", "statusCondition": null, "integrationSystemID": null, "applicationNamespace": null}', '[{"Name": "name", "JSONPath": "new-placeholder-name-json-path", "Optional": null, "Description": "app"}, {"Name": "display-name", "JSONPath": "new-placeholder-display-name-json-path", "Optional": null, "Description": "new-placeholder-display-name"}]', 'GLOBAL', null, '2024-03-07 17:39:49.711056', '2024-03-07 17:39:49.711056');

---

INSERT INTO public.app_template_versions (id, app_template_id, version, title, correlation_ids, release_date, created_at) VALUES ('54d9b48b-efea-4935-a7f3-c0ca61223206', '49d9b48b-efea-4935-a7f3-c0ca61223206', '1', null, null, null, '2024-03-07 17:40:12.000000');

---

INSERT INTO public.vendors (ord_id, app_id, title, labels, partners, id, documentation_labels, tags, app_template_version_id) VALUES ('vendor:ord.id', '4c63e3b2-3301-4796-bc95-9fb5b2780342', 'vendor-1', null, null, '2c23e3b2-3301-4796-bc95-9fb5b2780342', null, null, '54d9b48b-efea-4935-a7f3-c0ca61223206');
INSERT INTO public.vendors (ord_id, app_id, title, labels, partners, id, documentation_labels, tags, app_template_version_id) VALUES ('globalvendor:ord.id', null, 'global-vendor-1', null, null, '6c23e3b2-3301-4796-bc95-9fb5b2780342', null, null, '54d9b48b-efea-4935-a7f3-c0ca61223206');

---

INSERT INTO public.products (ord_id, app_id, title, short_description, vendor, parent, labels, correlation_ids, id, documentation_labels, tags, app_template_version_id, description) VALUES ('ord:product.id', '4c63e3b2-3301-4796-bc95-9fb5b2780342', 'product-1', 'short desc', 'vendor:ord.id', null, null, null, '1c23e3b2-3301-4796-bc95-9fb5b2780342', null, null, '54d9b48b-efea-4935-a7f3-c0ca61223206', 'desc');

---

INSERT INTO public.packages (id, ord_id, title, short_description, description, version, package_links, links, licence_type, tags, countries, labels, policy_level, app_id, custom_policy_level, vendor, part_of_products, line_of_business, industry, resource_hash, documentation_labels, support_info, app_template_version_id, runtime_restriction) VALUES ('fd5e9820-a8ae-4748-bf3f-049ef91804fa', ':package:manuallyAddedIntegrationDependencies:v1', 'Integration Dependencies package', 'Manually added package', 'This package contains manually added integration dependencies', '1.0.0', null, null, null, null, null, null, 'sap:core:v1', '4c63e3b2-3301-4796-bc95-9fb5b2780342', null, 'vendor:ord.id', '["ord:product.id"]', null, null, null, null, null, null, null);

---

INSERT INTO public.api_definitions (id, app_id, name, description, group_name, default_auth, version_value, version_deprecated, version_deprecated_since, version_for_removal, ord_id, short_description, system_instance_aware, api_protocol, tags, countries, links, api_resource_links, release_status, sunset_date, changelog_entries, labels, package_id, visibility, disabled, part_of_products, line_of_business, industry, ready, created_at, updated_at, deleted_at, error, implementation_standard, custom_implementation_standard, custom_implementation_standard_description, target_urls, extensible, successors, resource_hash, documentation_labels, policy_level, custom_policy_level, supported_use_cases, local_tenant_id, app_template_version_id, correlation_ids, direction, last_update, deprecation_date, responsible, usage) VALUES ('e710fee0-d954-476b-96d3-4e1b9e6007d1', '4c63e3b2-3301-4796-bc95-9fb5b2780342', 'bar-api-1', null, null, null, null, null, null, null, null, null, null, 'rest', '["tag1", "tag2"]', null, null, null, 'active', null, null, null, 'fd5e9820-a8ae-4748-bf3f-049ef91804fa', 'public', null, '["ord:product.id"]', null, null, true, '2024-03-05 13:39:30.737075', null, null, null, null, null, null, '["https://target.url"]', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
INSERT INTO public.api_definitions (id, app_id, name, description, group_name, default_auth, version_value, version_deprecated, version_deprecated_since, version_for_removal, ord_id, short_description, system_instance_aware, api_protocol, tags, countries, links, api_resource_links, release_status, sunset_date, changelog_entries, labels, package_id, visibility, disabled, part_of_products, line_of_business, industry, ready, created_at, updated_at, deleted_at, error, implementation_standard, custom_implementation_standard, custom_implementation_standard_description, target_urls, extensible, successors, resource_hash, documentation_labels, policy_level, custom_policy_level, supported_use_cases, local_tenant_id, app_template_version_id, correlation_ids, direction, last_update, deprecation_date, responsible, usage) VALUES ('349af461-f94f-45c4-b4df-0a97513a5211', '4c63e3b2-3301-4796-bc95-9fb5b2780342', 'foo-api-1', null, null, null, null, null, null, null, null, null, null, 'rest', '["tag1", "tag2"]', null, null, null, 'active', null, null, null, 'fd5e9820-a8ae-4748-bf3f-049ef91804fa', 'public', null, '["ord:product.id"]', null, null, true, '2024-03-05 13:40:01.554421', null, null, null, null, null, null, '["https://target.url"]', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);

---

INSERT INTO public.bundle_references (api_def_id, event_def_id, bundle_id, api_def_url, id, is_default_bundle) VALUES ('e710fee0-d954-476b-96d3-4e1b9e6007d1', null, '96fbf863-93ad-4c59-9464-61753e212e77', 'https://target.url', 'e8cb92c5-e8bb-46b3-b0a4-7a15b9164ac1', null);
INSERT INTO public.bundle_references (api_def_id, event_def_id, bundle_id, api_def_url, id, is_default_bundle) VALUES ('349af461-f94f-45c4-b4df-0a97513a5211', null, '96fbf863-93ad-4c59-9464-61753e212e77', 'https://target.url', 'f70ae64a-27a1-4b39-9dec-afb1349362a4', null);

---

INSERT INTO public.destinations (id, name, type, url, authentication, bundle_id, tenant_id, revision, formation_assignment_id, instance_id) VALUES ('22028605-5435-45b3-99bb-ae9a54cd9174', 'dest-foo', 'basic', 'http://test.com/v2', 'basic', '96fbf863-93ad-4c59-9464-61753e212e77', '3e64ebae-38b5-46a0-b1ed-9ccee153a0ae', '3e24ebae-38b5-46a0-b1ed-9ccee153a0ae', null, null);

Note: this issue may sound similar to this one I recently opened, but there the problem was that we had missing JOIN columns which affected the uniqueness in the response which is not the case here.

[wog48]

Hello,

the issue shall be solved with version 2.1.1. In case the problem still exists, please reopen the issue.