Autoquery ILeftJoin issue with "TableId" column

ServiceStack Auto Query
1

I have definition of autoquery dto defined as below. The query is executed against postgresql.

[Route("/project/{ProjectId}/contracts/{ContractId}/items")]
    public class QueryContractItem : QueryDb<ContractItem, ContractItemResponse>, 
        ILeftJoin<ContractItem, ContractItemEstimateItem>, 
        ILeftJoin<ContractItemEstimateItem, EstimateItem>,
        ILeftJoin<ContractItemEstimateItem, ContractItemEstimateItemComponent>,
        ILeftJoin<EstimateItem, EstimateComponent>,
        ILeftJoin<EstimateItem, EstimateGroup>
    {
        public int ProjectId { get; set; }
        public int ContractId { get; set; }
    }

and custom response DTO

  public class ContractItemResponse
       {
// ...
        public int ContractItemEstimateItemId { get; set; } // WRONGLY taken from ContractItemEstimateItemComponent.ContractItemEstimateItemId - PROBLEM
        public int ContractItemEstimateItemEstimateItemId { get; set; } //value is taken correctly from ContractItemEstimateItem - CORRECT
        public int ContractItemEstimateItemContractItemId { get; set; } //value is taken correctly from ContractItemEstimateItem - CORRECT
    
       }

Not sure why during query execution the value of ContractItemEstimateItemId is taken from ContractItemEstimateItemComponent.ContractItemEstimateItemId (the value is not yet there).

Expected is that query will take a value for field ContractItemEstimateItemId from ContractItemEstimateItem.Id (there ContractItemEstimateItemComponent correponding row does not exist yet…).

Interesting is that values for ContractItemEstimateItemEstimateItemId and ContractItemEstimateItemContractItemId are taken correctly from ContractItemEstimateItem table.

2

Would need a minimal stand-alone example (e.g. posted on GitHub) we can run locally to repro the issue.

Maybe viewing the generated SQL will provide some insight as to what’s happening:

OrmLiteUtils.PrintSql();
3 
SELECT "contract_item"."id",
       "contract_item"."name",
       "contract_item"."qty",
       "contract_item"."unit_type",
       "contract_item"."unit_price",
       "contract_item"."index",
       "contract_item"."contract_id",
       "contract_item"."qty_precision",
       "contract_item"."total_price",
       "estimate_item"."estimate_group_id",
       "estimate_group"."name" AS "estimate_group_name",
       "estimate_group"."lp" AS "estimate_group_lp",
       "contract_item_estimate_item"."estimate_item_id",
       "estimate_item"."name" AS "estimate_item_name",
       "estimate_item"."lp" AS "estimate_item_lp",
       "contract_item_estimate_item_component"."estimate_component_id",
       "estimate_component"."name" AS "estimate_component_name",
       "estimate_component"."unit_netto" AS "estimate_component_unit_netto",
       "estimate_component"."quantity" AS "estimate_component_quantity",
       "estimate_component"."total_netto_wzn" AS "estimate_component_total_netto_wzn",
       "estimate_component"."unit_type" AS "estimate_component_unit_type",
       "estimate_component"."component_type" AS "estimate_component_component_type",
       "contract_item_estimate_item_component"."selected_qty" AS "contract_item_estimate_item_component_selected_qty"
FROM "contract_item"
LEFT JOIN "contract_item_estimate_item" ON ("contract_item"."id" = "contract_item_estimate_item"."contract_item_id")
LEFT JOIN "estimate_item" ON ("estimate_item"."id" = "contract_item_estimate_item"."estimate_item_id")
LEFT JOIN "estimate_component" ON ("estimate_item"."id" = "estimate_component"."estimate_item_id")
LEFT JOIN "estimate_group" ON ("estimate_group"."id" = "estimate_item"."estimate_group_id")
LEFT JOIN "contract_item_estimate_item_component" ON ("contract_item_estimate_item"."id" = "contract_item_estimate_item_component"."contract_item_estimate_item_id")
WHERE "contract_item"."contract_id" = :0
ORDER BY "contract_item"."id"
LIMIT 50
OFFSET 0

The same issue is with EstimateComponentId (there query tries to get value from contract_item_estimate_item_component )… the EstimateComponentName has proper data source (estimate_component table)

4

You must have aliases or something as the selected columns and your property names don’t match up, but why is it wrongly taken when it’s an exact match?

5

@mythz I do not use any table/field aliases :pleading_face:

6

ok then you must be using postgres. I still don’t understand why it’s supposedly wrongly taken when it matches?

7

for reference diagram

obraz
obraz899×203 5.92 KB

8

And? Exactly which field are you hoping to match on? From your description its property name and column name are an exact match.

9

For field ContractItemEstimateItemId I would like to get data from contract_item_estimate_item.id field (and not from contract_item_estimate_item_component.contract_item_estimate_item_id).

For field EstimateComponentId I would like to get data from estimate_component.id field (not from how it is currently from contract_item_estimate_item_component.estimate_component_id.

10

That’s not an exact match with the column name which has the highest priority, from the docs:

The Request DTO lets us query against fields across the joined tables where each field is matched with the first table containing the field.

11

Is there any option to tell autoquery to use for EstimateComponentId first primary key by convention {TableName}.{Id}? In case of left joins I may not have recodes in foreign tables… This would allow me to use for EstimateComponentId first estimate_component table.

12

No the matching is hard coded, (we’d never consider a breaking change like this) but swapping priorities like this would lead to unintuitive behavior where the last table with a compound name {Table}{Field} would take priority over an exact field match on the first table which makes no sense.

13

ok it is little problematic, because it seems that I can’t really reference the source table id (estimate_component) on left joins until records does not exist in target table (contract_item_estimate_item_component). I was trying to avoid sql views but it seems there is no other option right @mythz?

14

If you want different matching behavior you would need to create a custom autoquery implementation that provides a custom select returning an anonymous object with the fields you want to select. Though a sql view that removes ambiguous columns (or joins multiple tables) might be easier.

15

In ideal scenario I would prefer to use default matching with possibility on DTO response property to set some kind of attribute like [OverrideAutoqueryMatch(MatchBy.TableId)] something like that…

16

Can try to see if the [BelongsTo] attribute helps here? E.g:

public class ContractItemResponse
{
    [BelongTo(typeof(ContractItemEstimateItem))]
    public int ContractItemEstimateItemId { get; set; } 
}