How to handle 2 ForeignKeys to the same Table?

[Morethna]

Hey,
i am new to supabase and the c# client of it and i have a problem to get a result of a table with 2 ForeignKeys on the same table (2 different uuids).
Table runs

    constraint runs_pkey primary key (id),
    constraint runs_runner_id_fkey foreign key (runner_id) references runners (id),
    constraint runs_verifier_id_fkey foreign key (verifier_id) references runners (id) on update cascade on delete set default

Class Run

 [Table("runs")]
  public class Run : BaseModel
  {
    [PrimaryKey("id", false)]
    public string Id { get; set; } = string.Empty;

    [Reference(typeof(Runner))]
    public Runner? Runner { get; set; }

    [Reference(typeof(Runner))]
    public Runner? Verifier { get; set; }

Select Statement

          var result = await _client.From<Run>()
        .Select("*")
        .Filter("games.name", Operator.Equals, gName)
        .Filter("categories.name", Operator.Equals, cName)
        .Limit(100)
        .Get(cancellationToken);
      return result.Models;

Error Message from Supabase API

{
  "type": "https://tools.ietf.org/html/rfc7231#section-6.6.1",
  "title": "An error occurred while processing your request.",
  "status": 500,
  "detail": "{\"code\":\"PGRST201\",\"details\":[{\"cardinality\":\"many-to-one\",\"embedding\":\"runs with runners\",\"relationship\":\"runs_runner_id_fkey using runs(runner_id) and runners(id)\"},{\"cardinality\":\"many-to-one\",\"embedding\":\"runs with runners\",\"relationship\":\"runs_verifier_id_fkey using runs(verifier_id) and runners(id)\"}],\"hint\":\"Try changing 'runners' to one of the following: 'runners!runs_runner_id_fkey', 'runners!runs_verifier_id_fkey'. Find the desired relationship in the 'details' key.\",\"message\":\"Could not embed because more than one relationship was found for 'runs' and 'runners'\"}"
}

Is this even possible or what am I doing wrong?

[acupofjose]

Try adjusting your select to:

.Select("*, runners!runs_runner_id_fkey:Runner, runners!runs_verifier_id_fkey:Verifier`)

And see if that works.

If so, we should probably add a parameter to the Reference attribute that specifies the foreign key it's related to.

[Morethna]

Sadly this doesn't work neither.
Now I'll get this error

{
  "type": "https://tools.ietf.org/html/rfc7231#section-6.6.1",
  "title": "An error occurred while processing your request.",
  "status": 500,
  "detail": "{\"code\":\"PGRST100\",\"details\":\"unexpected '!' expecting letter, digit, \\\"-\\\", \\\"->>\\\", \\\"->\\\", \\\"::\\\", \\\")\\\", \\\",\\\" or end of input\",\"hint\":null,\"message\":\"\\\"failed to parse select parameter (*,runners!runs_runner_id_fkey:Runner,runners!runs_verifier_id_fkey:Verifier,runners!inner(id,name,country,user_id,socials,roles!inner(id,role_text,order_number)),runners!inner(id,name,country,user_id,socials,roles!inner(id,role_text,order_number)),games!inner(id,order_number,name,groups!inner(id,name,order_number),runners!inner(id,name,country,user_id,socials,roles(id,role_text,order_number)),categories!inner(id,name,description,order_number)),categories!inner(id,name,description,order_number),verification_status!inner(id,status,order_number))\\\" (line 1, column 10)\"}"
}

[Cegoco11]

Hello,

We've also tried to use the propertyName attribute in Reference but it's not working neither.

[Reference(typeof(Runner), propertyName: "runners!runs_runner_id_fkey")]
public Runner? Runner { get; set; }

We have inspected the ReferenceAttribute class, and it appears that the property is not being used in the ParseRelationships method. This seems to be causing the bug when trying to use two different fields related to the same table

private void ParseRelationships(List<ReferenceAttribute> seenRefs)
        {
            foreach (var property in Model.GetProperties())
            {
                var attrs = property.GetCustomAttributes(true);

                foreach (var attr in attrs)
                {
                    if (attr is not ReferenceAttribute { IncludeInQuery: true } refAttr) continue;

                    if (seenRefs.FirstOrDefault(r => r.Equals(refAttr)) != null) continue;

                    seenRefs.Add(refAttr);
                    refAttr.ParseProperties(seenRefs);

                    Columns.Add(UseInnerJoin
                        ? $"{refAttr.TableName}!inner({string.Join(",", refAttr.Columns.ToArray())})"
                        : $"{refAttr.TableName}({string.Join(",", refAttr.Columns.ToArray())})");
                }
            }
        }

We are using supabase-csharp 0.13.3

I hope you find this useful,
Thanks!

[acupofjose]

@Cegoco11 it was useful indeed! Thanks for diving into that.

@Morethna - This should be supported in postgrest-csharp v3.2.8 - waiting on the nuget publish to complete.

You can see how relationships are defined here:
https://github.com/supabase-community/postgrest-csharp/blob/99af1c74715f0fc3738e85ffbda9abef583dfd14/PostgrestTests/Models/ForeignKeyTestModel.cs#L1-L18