Conditionally ignore column on insert or update?

[bdcodestuff]

Hi,

Thanks for the great library, I'm enjoying using it.
I'm struggling with something that seems like it should have an obvious answer.
When I define a model describing a one-to-many relationship through a foreign-key, in the parent class I include a column definition to catch all the children items in a List structure. So imagine backing models like this:

[Table("user")]
public class User : BaseModel
{
    [PrimaryKey("id", false)]
    public int Id { get; set; }

    [Column("name")]
    public string Name { get; set; }

    [Column("created_at")]
    public DateTime CreatedAt { get; set; }

    [Column("intervention")]
    public List<Intervention> Interventions { get; set; }
}

[Table("intervention")]
public class Intervention : BaseModel
{
    [PrimaryKey("id",false)]
    public int Id { get; set; }

    [Column("description")]
    public string Description { get; set; }

    [Column("created_at")]
    public DateTime CreatedAt { get; set; }
}

Querying the user table using :

var users = await client.Table<User>() .Select("*,intervention(*)") .Get();

This works great with anticipated automatic population of the User's "Interventions".

The problem is that if I try to Insert a new User or Update an existing User with these models it complains that there is no column matching "intervention".

So I'm wondering if it's possible to have something similar to how the PrimaryKey attribute works, but broaden it so that any Column attribute includes the option to specify shouldInsert and shouldUpdate boolean flags. If those are set to false the column is ignored when writing to the DB, but included in the model during reads.

There may also be a better solution that I'm missing entirely. Thoughts?

[acupofjose]

Great question! You’re thinking something that implements this? (https://postgrest.org/en/stable/api.html#specifying-columns)

[bdcodestuff]

Yes, that looks like it would solve the problem I'm describing.

I was actually just trying to find a way to do select queries as shown here but using the benefits of strong typing that your library offers:

https://supabase.com/docs/reference/javascript/select#query-foreign-tables

[acupofjose]

Ah! I see, thanks for clarifying! Maybe this will help!
supabase-community/supabase-csharp#31 (comment)

[bdcodestuff]

Thanks for the quick responses!

That comment is helpful for querying tables with an inner join, but I don't think it would solve my problem. If you look at the postgrest api for embedding resources in a one-to-many relationship it might be helpful:

https://postgrest.org/en/stable/api.html#one-to-many-relationships, it describes what I'm hoping to be able to model.

In that example, to read all directors into c# objects WITH their child films (handled automatically by postgrest due to the foreign key connection) I believe we would need to model the "directors" as having a column named "films" that is a List of some "film" type that is connected to a director through a foreign key in the database. Queries would work, but if you ever tried to insert a new director (using Instance.From().Insert(new Director()) ) it would fail because the "films" column (that exists in the Director model) doesn't actually have a real column in the database.

The solution that I was thinking might work was to have a way to conditionally set if a specific column is recognized during inserts, updates, selects etc. I think that's exactly what you were showing me here:

https://postgrest.org/en/stable/api.html#specifying-columns

[acupofjose]

Okay then, agreed! This is some functionality that will need to be implemented. Appreciate you being so clear on what you're trying to do here, that helps a lot. I'll get to work on it!

The library as a whole is actually lacking quite a bit when it comes to relational transactions - something that I'd be happy to expand upon! If you're looking to contribute, please feel free, otherwise I'll see what I can get done this week on it.

[bdcodestuff]

That's amazing, thanks so much for taking this on!

As an alternative to implementing the "specifying columns" option in the postgrest API, it might be simpler to just use flags on the column attribute to determine how the object gets serialized to JSON before being sent off to an endpoint. For instance if a hypothetical shouldUpdate flag is false on a given column then when the object is being updated, the column is ignored by the json serializer. Initially I thought [JsonIgnore] would be the same solution but it causes the column to be ignored all the time, including during queries, which is not the behavior I'm after.

[acupofjose]

@bdcodestuff phew, I think I have it implemented!

In short, you can now specify (including nested!) models that are query-able but not (by default) included in the insert/upsert/update interactions.

I have also added a Table<T>.Columns(string[] columns) method which will allow you to access that specific Postgrest method you mentioned in the above comments.

For example, given the following 3 models:

  [Table("movie")]
    public class Movie : BaseModel
    {
        [PrimaryKey("id", false)]
        public int Id { get; set; }

        [Column("name")]
        public string Name { get; set; }

        [Reference(typeof(Person))]
        public List<Person> Persons { get; set; }


        [Column("created_at")]
        public DateTime CreatedAt { get; set; }
    }

    [Table("person")]
    public class Person : BaseModel
    {
        [PrimaryKey("id", false)]
        public int Id { get; set; }

        [Column("first_name")]
        public string FirstName { get; set; }

        [Column("last_name")]
        public string LastName { get; set; }

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

        [Column("created_at")]
        public DateTime CreatedAt { get; set; }
    }

    [Table("profile")]
    public class Profile : BaseModel
    {
        [Column("email")]
        public string Email { get; set; }
    }

You can do the following:

postgrest-csharp/PostgrestTests/ClientApi.cs

Lines 1096 to 1141 in 9cf805d

	[TestMethod("references")]
	public async Task TestReferences()
	{
	var client = Client.Initialize(baseUrl);
	var movies = await client.Table<Movie>().Get();
	Assert.IsTrue(movies.Models.Count > 0);
	var first = movies.Models.First();
	Assert.IsTrue(first.Persons.Count > 0);
	var people = first.Persons.First();
	Assert.IsNotNull(people.Profile);
	var person = await client.Table<Person>()
	.Filter("first_name", Operator.Equals, "Bob")
	.Single();
	Assert.IsNotNull(person.Profile);
	var byEmail = await client.Table<Person>()
	.Filter("profile.email", Operator.Equals, "[email protected]")
	.Single();
	Assert.IsNotNull(byEmail);
	}
	[TestMethod("columns")]
	public async Task TestColumns()
	{
	var client = Client.Initialize(baseUrl);
	var movies = await client.Table<Movie>().Get();
	var first = movies.Models.First();
	var originalTime = first.CreatedAt;
	var newTime = DateTime.UtcNow;
	first.Name = "I should be ignored on insert attempt.";
	first.CreatedAt = newTime;
	var result = await client.Table<Movie>().Columns(new[] { "created_at" }).Update(first);
	Assert.AreNotEqual(first.Name, result.Models.First().Name);
	Assert.AreNotEqual(originalTime, result.Models.First().CreatedAt);
	}
	}

The movie query would produce a response with the following data (prior to being hydrated into a model):

[
  {
    "id": 2,
    "created_at": "2022-09-21T04:37:19.079074",
    "name": "Mad Max: Fury Road",
    "person": [
      {
        "id": 2,
        "first_name": "Tom",
        "last_name": "Holland",
        "created_at": "2022-08-20T00:30:02.120528",
        "profile": {
          "email": "[email protected]"
        }
      }
    ]
  },
  {
    "id": 1,
    "created_at": "2022-09-21T04:39:18.111174",
    "name": "I ahve a new name",
    "person": [
      {
        "id": 1,
        "first_name": "Tom",
        "last_name": "Cruise",
        "created_at": "2022-08-20T00:30:02.120528",
        "profile": {
          "email": "[email protected]"
        }
      },
      {
        "id": 3,
        "first_name": "Bob",
        "last_name": "Saggett",
        "created_at": "2022-08-20T00:30:33.72443",
        "profile": {
          "email": "[email protected]"
        }
      }
    ]
  }
]

Would love some feedback if you have the time!

[bdcodestuff]

That's amazing! Thanks so much for working on this . I will try it out later and let you know how it goes.

[bdcodestuff]

Your changes work well when using the Postgrest library by itself but I'm having trouble using it with a Supabase setup, possibly due to the BaseModel v. SupabaseModel distinction in the model definitions. How hard would it be to integrate the two?

[acupofjose]

Good call! I've updated the latest commit to support derived models on that ReferenceAttribute. Give it a try for me now!