Referencing same table in two fileds

[andresud]

I have a table structure that have two fields that reference the same table:

create table
  public.transaction (
    id bigint generated by default as identity,
    created_at timestamp with time zone null default now(),
    changed_at timestamp with time zone null,
    date timestamp with time zone null,
    total numeric null,
    description text null,
    **account1 bigint null,
    account2 bigint null,**
    parcel bigint null,
    category bigint null,
    subcategory bigint null,
    type bigint null,
    projectid bigint null,
    image text null,
    constraint transactions_pkey primary key (id),
    **constraint transaction_account1_fkey foreign key (account1) references account (id),
    constraint transaction_account2_fkey foreign key (account2) references account (id),**
    constraint transaction_category_fkey foreign key (category) references category (id),
    constraint transaction_parcel_fkey foreign key (parcel) references parcel (id),
    constraint transaction_subcategory_fkey foreign key (subcategory) references category (id),
    constraint transaction_type_fkey foreign key (
      type
    ) references transactiontype (id),
    constraint transaction_projectid_fkey foreign key (projectid) references project (id)
  ) tablespace pg_default;

When I create the DTO:

using Postgrest.Attributes;
using System.ComponentModel.DataAnnotations;

namespace iQGestor.Dtos
{

    [Table("transaction")]
    public class iQTransaction : BaseModelApp
    {
        [Required(ErrorMessage = "Description is required")]
        [Column("description")]
        public string? Description { get; set; }
        [Column("total")]
        public Decimal? Total { get; set; }
        [Column("date")]
        public DateTime? Date { get; set; }
        //1 credito 2 debito
        [Column("type")]
        public int? Type { get; set; }
        [Column("image")]
        public string? Image { get; set; }
        [Column("account1")]
        public int? AccountId { get; set; }
        [Reference(typeof(Account), ReferenceAttribute.JoinType.Left, true, true, true, "account!transaction_account1_fkey")]
        public Account? Account { get; set; }
        [Column("account2")]
        public int? CounterAccountId { get; set; }
        [Reference(typeof(Account), ReferenceAttribute.JoinType.Left, true, true, true, "account!transaction_account2_fkey")]
        public Account? CounterAccount { get; set; }
        [Column("parcel")]
        public int? ParcelId { get; set; }
        [Reference(typeof(Parcel))]
        public Parcel? Parcel { get; set; }
        [Column("category")]
        public int? CategoryId { get; set; }
        [Reference(typeof(Category), ReferenceAttribute.JoinType.Left, true, true, true, "category!transaction_category_fkey")]
        public Category? Category { get; set; }
        [Column("subcategory")]
        public int? SubcategoryId { get; set; }
        [Reference(typeof(Category), ReferenceAttribute.JoinType.Left, true, true, true, "category!transaction_subcategory_fkey")]
        public Category? SubCategory { get; set; }
        [Column("projectid")]
        public int? Projectid { get; set; }
        [Reference(typeof(Project))]
        public Project? Project { get; set; }
    }
}

When I access the List of Transactions I get the error:

blazor.webassembly.js:1 crit: Microsoft.AspNetCore.Components.WebAssembly.Rendering.WebAssemblyRenderer[100]
      Unhandled exception rendering component: {"code":"PGRST201","details":[{"cardinality":"many-to-one","embedding":"transaction with account","relationship":"transaction_account1_fkey using transaction(account1) and account(id)"},{"cardinality":"many-to-one","embedding":"transaction with account","relationship":"transaction_account2_fkey using transaction(account2) and account(id)"}],"hint":"Try changing 'account' to one of the following: 'account!transaction_account_fkey', 'account!transaction_caccount_fkey'. Find the desired relationship in the 'details' key.","message":"Could not embed because more than one relationship was found for 'transaction' and 'account'"}
Postgrest.Exceptions.PostgrestException: {"code":"PGRST201","details":[{"cardinality":"many-to-one","embedding":"transaction with account","relationship":"transaction_account1_fkey using transaction(account) and account(id)"},{"cardinality":"many-to-one","embedding":"transaction with account","relationship":"transaction_account2_fkey using transaction(account2) and account(id)"}],"hint":"Try changing 'account' to one of the following: 'account!transaction_account1_fkey', 'account!transaction_account2_fkey'. Find the desired relationship in the 'details' key.","message":"Could not embed because more than one relationship was found for 'transaction' and 'account'"}
   at Postgrest.Helpers.MakeRequest(ClientOptions clientOptions, HttpMethod method, String url, JsonSerializerSettings serializerSettings, Object data, Dictionary`2 headers, CancellationToken cancellationToken)
   at Postgrest.Helpers.<MakeRequest>d__2`1[[iQGestor.Dtos.iQTransaction, iQGestor, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]].MoveNext()
   at BlazorWebAssemblySupabaseTemplate.Services.DatabaseService.<From>d__12`1[[iQGestor.Dtos.iQTransaction, iQGestor, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]].MoveNext() in C:\src\iQGestor2\iQGestor\Services\DatabaseService.cs:line 109
   at iQGestor.Pages.Transactions.TransactionList.GetTable() in C:\src\iQGestor2\iQGestor\Pages\Transactions\TransactionList.razor:line 125
   at iQGestor.Pages.Transactions.TransactionList.OnInitializedAsync() in C:\src\iQGestor2\iQGestor\Pages\Transactions\TransactionList.razor:line 114
   at Microsoft.AspNetCore.Components.ComponentBase.RunInitAndSetParametersAsync()
   at Microsoft.AspNetCore.Components.RenderTree.Renderer.GetErrorHandledTask(Task taskToHandle, ComponentState owningComponentState)

Any Ideas are welcome.

[Cegoco11]

Hello,

I saw this issue the other day, we got a similar problem and they already fixed it!

You can check the answer in this discussion:
#115 (reply in thread)

I hope you find this useful, see you!