Help with using postgrest attribute reference

[infraction]

I am running into issues when trying to mock up my database to in classes and have reference attributes between related tables and am confused by how I am suppose to implement this. I keep getting various errors when I try and adjust how I have it structured. I am confused by how the example mock up is shown for this in the readme. The class values with the reference attribute aren't given a column name or match a column name in the example schema. How does it then get mapped during requests to and from

[acupofjose]

Sure! What specifically are you struggling with - the Getting Started section ought to be pretty clear I think?

[infraction]

Thanks for the response. Sorry my question wasn't super detailed. I think this is probably me just misunderstanding something along the way. I have a schema like this

I created c# classes for each of them like this

[Table("users")]
    public class SbUser : BaseModel
    {
        [PrimaryKey("id",false)]
        public Guid Id { get; set; }
        [Column("email")]
        public string email { get; set; }
        
        [Reference(typeof(SbRating))]
        public List<SbRating> ratings { get; set; }

        [Reference(typeof(SbGroupMember))]
        public List<SbGroupMember> membershipInGroups { get; set; }
    }

[Table("ratings")]
    public class SbRating : BaseModel
    {
        [Reference(typeof(SbUser))]
        [Column("user_id")]
        public SbUser User { get; set; }

        [Reference(typeof(SbMovie))]
        [Column("movie_id")]
        public SbMovie Movie { get; set; }

        [Column("rating")]
        public decimal Rating { get; set; }

        [Column("text_review")]
        public string TextReview { get; set; }

        [Column("watched_at")]
        public DateOnly WatchedAt { get; set; }

        [PrimaryKey("id")]
        public Guid id { get; set; }
    }

 [Table("movies")]
    public class SbMovie : BaseModel
    {
        [PrimaryKey("id")]
        public Guid? id { get; set; }
        public string title { get; set; }
        public DateTime? release_date { get; set; }
        public int? tmdb_id { get; set; }
        public string? imdb_id { get; set; }
        public string? overview { get; set; }
        public DateTime? created_at { get; set; }
        public string? poster { get; set; }
	}

[Table("group_movie_ratings")]
    public class SbGroupMovieRating : BaseModel
    {
        [PrimaryKey("id",false)]
        public Guid id { get; set; }

        [Reference(typeof(SbRating))]
        [Column("rating_id")]
        public SbRating rating { get; set; }

        [Reference(typeof(SbGroup))]
        [Column("group_id")]
        public SbGroup group_id { get; set;}

        [Column("watched_together")]
        public bool WatchedTogether { get; set; }

        [Column("watched_at")]
        public DateOnly WatchedAt { get; set; }
    }

[Table("group_members")]
    public class SbGroupMember : BaseModel
    {
        [PrimaryKey("id",false)]
        public Guid Id { get; set; }

        [Reference(typeof(SbUser))]
        [Column("user_id")]
        public SbUser SbUser { get; set; }

        [Reference(typeof(SbGroup))]
        [Column("group_id")]
        public SbGroup SbGroup { get; set; }
    }

[Table("groups")]
    public class SbGroup : BaseModel
    {
        [Column("name")]
        public string Name { get; set; }

        [Reference(typeof(SbUser))]
        [Column("owner_id")]
        public SbUser User { get; set; }

        [PrimaryKey("id",false)]
        public Guid? GroupId { get; set; }

        [Reference(typeof(SbGroupMember))]
        public List<SbGroupMember> GroupMembers { get; set; }

        [Reference(typeof(SbRating))]
        public List<SbRating> Ratings { get; set; }
    }

I tried a couple different variations of this trying to get it to work but didn't have any luck. I am sure it is something simple I am missing but wasn't able to figure out where I was going wrong. Thanks again for the assistance.

[acupofjose]

Awesome, thanks for the clarifications! The relationships are inferred by PostgREST via the foreign keys present between tables on the schema, so provided the foreign key relationships are appropriately set up, the column names shouldn't be necessary on the class for the lookups.

What kind of errors are you getting?

[infraction]

For full disclosure I originally was trying to get this to work over the weekend. Gave up and did it without using the reference attribute but then decided I should probably do it the right way instead of the very jank way I was doing it that was only gonna cause more problems down the line.

I originally didn't have the column names included. When I first tried it I tried doing an insert from the rating table and all the relational keys were just null in the database. Then when I changed something (apologies that I can't remember the exact change) I got runtime errors about not being able to serialize the data if I recall correctly. Now with the column names removed and just trying to call
var test = await supabase.From<SbGroup>().Select("*").Get();
I get this error Uncaught (in promise) RangeError: Maximum call stack size exceeded which I hadn't even been getting previously.

[acupofjose]

Ha! Understood. That's frustration I can get behind.

I believe that issue is due to circular references that I haven't figured out how to handle with references. You are correct, this is totally unclear/unaddressed in the readme. Basically, your SbGroup model is referencing SbMember which references SbGroup again (etc. etc.) - hence the stack overflow. The workaround is to have define Join models that only reference foreign models that have not been previously referenced.

i.e.

    [Table("group_members")]
    public class SbGroupMemberJoinModel : BaseModel
    {
        [PrimaryKey("id",false)]
        public Guid Id { get; set; }

        [Column("user_id")]
        public string SbUser { get; set; }

        [Column("group_id")]
        public string SbGroup { get; set; }
    }

[Table("groups")]
    public class SbGroup : BaseModel
    {
        [Column("name")]
        public string Name { get; set; }

        [Reference(typeof(SbUser))]
        [Column("owner_id")]
        public SbUser User { get; set; }

        [PrimaryKey("id",false)]
        public Guid? GroupId { get; set; }

        [Reference(typeof(SbGroupMember))]
        public List<SbGroupMemberJoinModel> GroupMembers { get; set; }

        [Reference(typeof(SbRating))]
        public List<SbRating> Ratings { get; set; }
    }

This is the only workaround I know of right now. But I'm up to take this problem up as a present issue if you open it on postgrest-csharp! I've had other more pressing issues crop up over on realtime that I've been taking care of!

[infraction]

So basically I should make a separate join model class for the referenced classes and use those as the typeof? Is there any reason you switched the Guid to strings in the joinmodel class?

[acupofjose]

Yep! Oh, that was me typing, Guids should work fine, sorry about that!

[infraction]

All good. Thanks for the assistance. I will open up an issue on postgrest-csharp about it and give that suggestion a try.

[infraction]

Ok so I updated the class and am still getting the same error when I try to just do
From<SbGroup>.Select("*).Get()

I updated the class to look like this

    [Table("groups")]
    public class SbGroup : BaseModel
    {
        [Column("name")]
        public string Name { get; set; }

        //[Reference(typeof(SbUser))]

        //public SbUser User { get; set; }

        [PrimaryKey("id",false)]
        public Guid? GroupId { get; set; }

        [Reference(typeof(SbGroupMember))]
        public List<SbGroupMemberJoinModel> GroupMembers { get; set; }

        //[Reference(typeof(SbRating))]
        //public List<SbRating> Ratings { get; set; }
    }

    [Table("group_members")]
    public class SbGroupMemberJoinModel : BaseModel
    {
        [PrimaryKey("id", false)]
        public Guid Id { get; set; }

        [Column("user_id")]
        public Guid SbUser { get; set; }

        [Column("group_id")]
        public Guid SbGroup { get; set; }
    }

[acupofjose]

I've opened an issue here: supabase-community/postgrest-csharp#67 to follow this!

[infraction]

Sorry to keep asking questions but I was messing around with it and got the other error I was running into. The error is
"Unhandled exception rendering component: Cannot deserialize the current JSON object (e.g. {"name":"value"}) into type 'System.Collections.Generic.List`1[MovieTrackerCloudflarepages.Models.Supabase_Tables.SbUser]' because the type requires a JSON array (e.g. [1,2,3]) to deserialize correctly.
To fix this error either change the JSON to a JSON array (e.g. [1,2,3]) or change the deserialized type so that it is a normal .NET type (e.g. not a primitive type like integer, not a collection type like an array or List) that can be deserialized from a JSON object. JsonObjectAttribute can also be added to the type to force it to deserialize from a JSON object."

This comes from trying to query the groups table like so
var test = await supabase.From<SbGroup>().Select("*").Get();

This is the updated groups table

[Table("groups")]
    public class SbGroup : BaseModel
    {
        [Column("name")]
        public string Name { get; set; }


        [PrimaryKey("id",false)]
        public Guid? GroupId { get; set; }

        [Reference(typeof(SbUser))]
        public List<SbUser> users { get; set; }

    }

While thinking about it I also realized another question related to this. How would I handle using additional columns in the join table like in the group_movie_rating one?

[acupofjose]

I've got some commits in for the next version of postgrest-csharp that ought to address all of these issues (including the circular references)!

The example models are here and the example usage is as so:
https://github.com/supabase-community/postgrest-csharp/blob/1b3813e0be8808e8d77d01d347c82056b9142719/PostgrestTests/ReferenceTests.cs#L48-L95

[acupofjose]

@infraction these changes are available in v0.11.0, could you try now and see if that helps? Thanks!

[infraction]

Yes it is working. I am running into a new probably unrelated issue still because my group table directly references an owner id to the user table as well as the relationship with it through the join table. But the rating table is working as expected now so the initial issue is resolved. Thank you for all the help.