Closed AdamBuchweitz closed 5 months ago
@AdamBuchweitz hope you had a nice holiday!
This is currently intended functionality. Or, rather, an expected lack of functionality. But looking at the docs, this isn't documented anywhere as far as I see.
Unfortunately, when the ReferenceAttribute
was written inferring nested relationships for inserts and updates was a large undertaking.
The problem being that when performing an insert with related data, the library needs the related keys to perform the inserts (which are usually generated).
So, for your use case, the library would need to translate your model with inserts by creating the Person
model first, store the result, and then make separate call(s) to create each of the Quote
s using the previously inserted Person
model.
It's doable, for sure, but no one has implemented it yet. I'm up to implementing it, but I'm traveling for the next month. So, just being honest, I'll probably be slow implementing it!
Thanks for the issue/enhancement request! Love seeing people using supabase and c#.
Thanks for the reply @acupofjose . Support for inserting/updating referenced data would be really nice, but I'm fine without that. The bug is that the library won't even insert the Person without the referenced data. I have taken to building models with derivatives like this:
[Table("people")]
public class Person : BaseModel
{
[PrimaryKey("id")]
public string id { get; set; }
public string first_name { get; set; }
public string last_name { get; set; }
[Reference(typeof(Quote), ReferenceAttribute.JoinType.Left)]
public List<Quote> quotes { get; set; }
public PersonInsert ToInsert() => new PersonInsert
{
id = id,
first_name = first_name,
last_name = last_name,
};
}
[Table("people")]
public class PersonInsert : Person
{
[JsonIgnore]
public List<Quote> quotes { get; set; }
}
which allows me to insert a Person with the ToInsert()
method:
var person = new Person { first_name = "Leeroy", last_name = "Jenkins"};
await supabaseClient.From<Person>().Insert(person.ToInsert());
Since there is already supposed to be support for ignoreOnInsert
and ignoreOnUpdate
I thought I should be able to annotate the model with that, but it doesn't help any. I think that's the simplest fix here. What do you think?
Ah, yep. I actually noticed that as a bug as I was starting the implementation (it's becoming a rabbit hole lol). But the bug fix for just that is definitely easy enough! Good call.
I'm over here making mountains out of molehills.
Give me a few, I think I can have a patch worked out here for you on this issue.
Your request should be supported in v3.4.0! Thanks for the bug report @AdamBuchweitz, apologies for the confusion on my part.
I updated via Nuget, and it works! Thank you so much! Now I feel silly for spending those days hunting this down and working around it.
I hope you don't mind if I throw more reports your way - I'm working on a project and we're using this library in production.
Looks like I got excited and spoke too soon... I didn't realize that the test I ran, switching an argument from new MyModelInsert
to new MyModel
, just ran ToInsert()
on it further down the line.
~I updated via Nuget, and it works! Thank you so much! Now I feel silly for spending those days hunting this down and working around it.~
I hope you don't mind if I throw more reports your way - I'm working on a project and we're using this library in production.
Please do, always looking to improve the codebase!
Also, to clarify, is this still an issue? Or has it been resolved?
@acupofjose I think this might be still be going on. It was specifically on the INSERT of a many to many link record that I had the issue. I just upgraded the postgrest-csharp version to 3.4.0 to see if the issue is resolved.
I'm currently attempting to debug the issue using an insert model stand-in for that table. Though, I also noticed that the link table just has the two primary keys.
The exception report is rather vexing as well: {}
The insert data looked correct with model definition that had the three primary keys set on the table model (captured with the debug handler on the Postgrest client):
{"table_1_id":16,"table_y_id":"a85b236b-99f5-4284-8e8a-05703df89534"}
That may be a false alarm @acupofjose - I will attempt to confirm. I had a typo in my Table attribute (name was misspelled). However, the "NotFound" reason did not propagate to something meaningful. However, I can file another handling or my own PR for that. If I determine this issue's problem is still present, I'll update this ticket.
Bug report
Describe the bug
Trying to insert new rows into a table with a Reference (many-to-one) relationship throws a
PostgresException
.PostgrestException: {"code":"PGRST204","details":null,"hint":null,"message":"Column '[table]' of relation '[table]' does not exist"}
To Reproduce
Take the following two simple models:
Now try to insert data:
Results in: PostgrestException:
{"code":"PGRST204","details":null,"hint":null,"message":"Column 'quotes' of relation 'people' does not exist"}
Expected behavior
I would expect a row to be inserted as normal.
System information
Additional context
Without the reference, people data inserts fine! It's only after adding the reference that the exception arises. Interestingly, I can't find any examples of one-to-many relationships here. Perhaps it isn't supported?
Here is the schema: