Mutiple relations and join of the same table on multiple columns break aliasing and mapping

[Miljan445]

Describe the bug
I have created a realtions ( two to be precise) on a table "orderProducts" with another table "Addresses".
The relations are of the same type "Addresses" and both have respective aliases ( "billingAddress" and "shippingAddress" ).

The "Addresss" have similar relations ( again two to be precise ) to a table "AddressPhoneNumbers"
Again the relations are of the same type "AddressPhoneNumbers" and both have respective aliases ( "billingAddrNumbers" and "shippingAddrNumbers" ).

I am creating a dynamic query which will simply have only the "billingAddress" field in "orderProducts" ,"shippingAddress" field or both if needed.

Environment (please complete the following information):

• OS: macosx
• Database: postgres
• Jet version v2.13.0

Code snippet

	var orderProduct = &m.OrderProducts{}

         OrderProductIncludes := struct {
		Address         bool
		ShippingAddress bool
		PersonalPickup  bool
	}{
		Address:         true,
		ShippingAddress: true,
		PersonalPickup:  false,
	}

	var selectStmt []Projection
	var fromStmt ReadableTable = t.OrderProducts
	var whereStmt = t.OrderProducts.ID.EQ(UUID(orderID))

	selectStmt = append(selectStmt, t.OrderProducts.AllColumns)

	if OrderProductIncludes.Address {
		billingAddrNumbers := SELECT(t.AddressPhoneNumbers.AllColumns, t.OrderProducts.AllColumns).
			FROM(t.AddressPhoneNumbers.
				LEFT_JOIN(t.OrderProducts,
					t.AddressPhoneNumbers.Address.EQ(t.OrderProducts.Address))).
			WHERE(t.OrderProducts.ID.EQ(UUID(orderID))).
			AsTable("billingAddrNumbers")

		selectStmt = append(selectStmt,
			t.AddressPhoneNumbers.AllColumns.From(billingAddrNumbers).As("billingAddrNumbers"),
			t.Addresses.AS("billingAddress").AllColumns,
		)

		fromStmt = fromStmt.LEFT_JOIN(
			billingAddrNumbers,
			t.OrderProducts.Address.EQ(t.AddressPhoneNumbers.Address.From(billingAddrNumbers)),
		).
			LEFT_JOIN(t.Addresses.AS("billingAddress"),
				t.OrderProducts.Address.EQ(t.Addresses.AS("billingAddress").ID))
	}

	if OrderProductIncludes.ShippingAddress {

		shippingAddrNumbers := SELECT(t.AddressPhoneNumbers.AllColumns, t.OrderProducts.AllColumns).
			FROM(t.AddressPhoneNumbers.
				LEFT_JOIN(t.OrderProducts,
					t.AddressPhoneNumbers.Address.EQ(t.OrderProducts.ShippingAddress))).
			WHERE(t.OrderProducts.ID.EQ(UUID(orderID))).
			AsTable("shippingAddrNumbers")

		selectStmt = append(selectStmt,
			t.AddressPhoneNumbers.AllColumns.From(shippingAddrNumbers).As("shippingAddrNumbers"),
			t.Addresses.AS("shippingAddress").AllColumns,
		)

		fromStmt = fromStmt.LEFT_JOIN(
			shippingAddrNumbers,
			t.OrderProducts.ShippingAddress.EQ(t.AddressPhoneNumbers.Address.From(shippingAddrNumbers)),
		).
			LEFT_JOIN(t.Addresses.AS("shippingAddress"),
				t.OrderProducts.ShippingAddress.EQ(t.Addresses.AS("shippingAddress").ID))
	}

	if OrderProductIncludes.PersonalPickup {

		storePhoneNumbers := SELECT(
			t.AddressPhoneNumbers.AllColumns,
			t.Store.AllColumns,
			t.OrderProducts.AllColumns,
		).FROM(t.AddressPhoneNumbers.
			LEFT_JOIN(
				t.Store,
				t.AddressPhoneNumbers.Address.EQ(t.Store.Address),
			).LEFT_JOIN(
			t.OrderProducts,
			t.Store.ID.EQ(t.OrderProducts.PersonalPickup),
		),
		).WHERE(t.OrderProducts.ID.EQ(UUID(orderID))).AsTable("storePhoneNumbers")

		selectStmt = append(selectStmt,
			t.Store.AS("personalPickup").AllColumns,
			t.AddressPhoneNumbers.AllColumns.From(storePhoneNumbers).As("storePhoneNumbers"),
			t.Addresses.AS("storeAddress").AllColumns,
		)

		fromStmt = fromStmt.
			LEFT_JOIN(t.Store.AS("personalPickup"),
				t.OrderProducts.PersonalPickup.EQ(t.Store.AS("personalPickup").ID),
			).
			LEFT_JOIN(
				storePhoneNumbers,
				t.Store.AS("personalPickup").Address.EQ(t.AddressPhoneNumbers.Address.From(storePhoneNumbers)),
			).
			LEFT_JOIN(t.Addresses.AS("storeAddress"),
				t.Store.AS("personalPickup").Address.EQ(t.Addresses.AS("storeAddress").ID),
			)
	}

	q := SELECT(t.OrderProducts.AllColumns, selectStmt...).
		FROM(fromStmt)

	q = q.WHERE(whereStmt)
	/*
		Test the behaviour of order products -> shipping address:
		when you remove the addressPhoneNumber table relations in the Addressess table, it will not exists.

		Upon adding the addressPhoneNumber table relation in the Addressess table:
		it will exists (shippingAddress) but will set the fields to their nil values.

		Also in the substructure phoneNumbers of shippingAddress, the phone numbers of billing address will appear!
	*/

Expected behavior
The behaviour i am expecting to get is when i exclude, let's say "shippingAddress" from my query, to only get the "billingAddress" field with it's relation of "billingAddrNumbers" data set ( shippingAddrNumbers should be emtpy
event if it exists ).

The behaviour i am getting is a bit different...
When i exclude the "shippingAddress" i am getting "billingAddress" field with it's relation of "billingAddrNumbers" as expected, but also a "shippingAddress" with its empty values and also "billingAddrNumbers" copied from the "billingAddress"

NOTE: This applies to the "personalPickup" relation as well.

example:
expected response :

{
    "order_product": {
        fieldA,
        fieldB,
        fieldC
        ....
        "billing_address": {
            "phone_numbers": [
                {
                    "phone_number": "00000000"
                },
                {
                    "phone_number": "11111111"
                }
            ],
            "shipping_addr_numbers": [],
           .....
        },
        "shipping_address": null
        },
    }
}

response i get :

{
    "order_product": {
        fieldA
        fieldB
        fieldC
        "billing_address": {
            "phone_numbers": [
                {
                    "phone_number": "00000000"
                },
                {
                    "phone_number": "11111111"
                }
            ],
            "shipping_addr_numbers": [],
            .....
        }
        "shipping_address": {
            ....
            fieldA:"",
            fieldB:""
            "phone_numbers": [
                {
                    "phone_number": "00000000"
                },
                {
                    "phone_number": "11111111"
                }
            ],
            "shipping_addr_numbers": [],
        },
    }
}

address_phone_numbers.txt
addresses.txt
order_products.txt

Here are the models for the tables as well.

[go-jet]

Your issue seems to be related with this discussion. QRM maps the columns to corresponds to model type name.field name. If the same type appears in different subtrees of the destination type, both instances will be filled.

The solution in such cases is to define two distinct types.

But, a cleaner and significantly more performant approach is to use the new SELECT_JSON functionality for handling queries like this.

For example, assuming:

• OrderProducts -> has many -> Addresses -> has many -> AddressPhoneNumbers
• OrderProducts -> has many -> ShippingAddress -> has many -> AddressPhoneNumbers

stmt := SELECT(
	t.OrderProducts.AllColumns,

	SELECT_JSON_ARR(
		t.Addresses.AllColumns,

		SELECT_JSON_ARR(t.AddressPhoneNumbers.AllColumns).
		FROM(t.AddressPhoneNumbers).
		WHERE(t.Addresses.Address.EQ(t.AddressPhoneNumbers.ID)).AS("PhoneNumbers")
		
	).FROM(
		t.Addresses,
	).WHERE(
		Bool(OrderProductIncludes.Address).
			AND(t.OrderProducts.Address.EQ(t.Addresses.ID)),
	).AS("BillingAddress"),

	SELECT_JSON_ARR(
		t.Addresses.AllColumns,

		SELECT_JSON_ARR(t.AddressPhoneNumbers.AllColumns).
		FROM(t.AddressPhoneNumbers).
		WHERE(t.Addresses.Address.EQ(t.AddressPhoneNumbers.ID)).AS("PhoneNumbers")
		
	).FROM(
		t.Addresses,
	).WHERE(
		Bool(OrderProductIncludes.ShippingAddress).
			AND(t.OrderProducts.ShippingAddress.EQ(t.Addresses.ID)),
	).AS("ShippingAddress"),

	// ... the same for PersonalPickup

).FROM(
	t.OrderProducts
)

Alternatively, you can remove Bool(OrderProductIncludes.Address) and Bool(OrderProductIncludes.ShippingAddress) and build projection dynamically, if there are any performance differences.

And, the destination sample using generated model types:

type Address struct {
	model.Addresses

	PhoneNumbers []model.AddressPhoneNumbers
}

var dest []struct{
	model.OrderProducts

	BillingAddress  []Address `json_column:"BillingAddress"`
	ShippingAddress []Address `json_column:"ShippingAddress"`

	// ... the same for PersonalPickup
}

err := stmt.Query(db, &dest)