Query does not fetch data in relation where relation has no data

[thisisthemurph]

Hi all,

I am testing out c-sharp Supabase in a simple console application.

I am trying to fetch all of my UnregisteredParty models and all data from related tables. When I include the UnregisteredPartyInvitationGuest model and add a reference in the UnregisteredPartyInvitation model, it will only retrieve UnregisteredParty data from the query where there are unregistered guests. If I remove this relation, the query works as I would expect.

I thought including shouldFilterTopLevel: false in the reference would change the behaviour, but it seems to make no difference.

Does anyone know why I might be seeing this behaviour and how I can fix the issue?

• Using .NET 7
• Using supabase-csharp 0.12.1
• I have RLS turned off for the moment on all tables as the console application doesn't authenticate.
• All the required relationships exist in the database.

Following is the script I am using to fetch and log the data:

var supabase = new Supabase.Client(supabaseUrl, supabaseKey, new SupabaseOptions
{
    AutoConnectRealtime = true,
});

// Fetch and print a list of all the unregistered_parties

var unregisteredParties = await supabase
    .From<UnregisteredParty>()
    .Get();

foreach (var party in unregisteredParties.Models)
{
    Console.WriteLine(party.Name);
    Console.WriteLine($"Invitation created: {party.Invitation.Created.ToString()}");
    Console.WriteLine($"Event name: {party.Invitation.Event.Name}");
    Console.WriteLine($"Guest count: {party.Invitation.Guests.Count}");
    Console.WriteLine("----------------------------------------");
}

Following are the models I am using:

[Table("unregistered_parties")]
public class UnregisteredParty : BaseModel
{
    [PrimaryKey("id")]
    public int Id { get; set; }

    [Column("name")] 
    public string Name { get; set; } = string.Empty;
    
    [Column("code")] 
    public string Code { get; set; } = string.Empty;

    [Reference(typeof(UnregisteredPartyInvitation))]
    public UnregisteredPartyInvitation Invitation { get; set; } = new();
}

[Table("unregistered_party_invitations")]
public class UnregisteredPartyInvitation : BaseModel
{
    [PrimaryKey("id")]
    public int Id { get; set; }
    
    [Column("created")]
    public DateTimeOffset Created { get; set; }

    [Reference(typeof(Event), shouldFilterTopLevel: false)] 
    public Event Event { get; set; } = new();

    [Reference(typeof(UnregisteredPartyInvitationGuest), shouldFilterTopLevel: false)]
    public List<UnregisteredPartyInvitationGuest> Guests { get; set; } = new();
}

[Table("unregistered_party_invitation_guests")]
public class UnregisteredPartyInvitationGuest : BaseModel
{
    [PrimaryKey("id")]
    public int Id { get; set; }
    
    [Column("name")] 
    public string Name { get; set; } = string.Empty;
}

Following is a database diagram I have created:

[thisisthemurph]

I don't have time to look at it now (got to go to work) but my guess is that it is defaulting to an INNER JOIN and I need to make it do a LEFT JOIN somehow.

[thisisthemurph]

So, I figured out what my issue was.

I needed to have shouldFilterTopLevel: false on the UnregisteredParty.Invitation reference. I am unsure why this impacted, but I guess there is something I don't understand about how Supabase works under the hood.

Before:

[Reference(typeof(UnregisteredPartyInvitation))]
public UnregisteredPartyInvitation Invitation { get; set; } = new();

After:

[Reference(typeof(UnregisteredPartyInvitation), shouldFilterTopLevel: false)]
public UnregisteredPartyInvitation Invitation { get; set; } = new();

[acupofjose]

I suppose that flag should be renamed... it was based on the postgrest API. But it essentially changes between a LEFT join and an INNER join. Would useLeftJoin be clearer?

[nove1398]

I think that useLeftJoin would be clearer, or even better still an Enumeration. However, that is just my opinion, not sure how it would impact the entire flow.

[thisisthemurph]

Yeah, I think useLeftJoin would be much clearer. Thanks.

[acupofjose]

This functionality is now included in >= [email protected]. It will be wrapped into the main supabase-csharp lib in its next release. Thank y'all for the suggestions!

public class Movie : BaseModel
{
   // .....
    [Reference(typeof(Person), ReferenceAttribute.JoinType.Left)]
    public List<Person> People { get; set; } = new();
}

[nove1398]

Love that the team is taking suggestions and really making the product better. Awesome! Looking forward to using the update. Thanks