Modify records API to use column id instead of column name

[seancolsen]

I'd like to be able to use the records API when all I know and care about is the column id for my columns.

GET request params

The order_by, group_count_by, and filters params all expect references to columns to be given by name. For example:

• order_by=[{"field":"name","direction":"asc"}]
• group_count_by=["name"]
• filters={"and":[{"field":"name","op":"eq","value":"Sean"}]}

I'd like to the API to accept column references by id.

As a breaking change

A simple way to address this need would be to break the API, no longer accepting column references by name

• order_by=[{"field":"1","direction":"asc"}]
• group_count_by=["1"]
• filters={"and":[{"field":"1","op":"eq","value":"Sean"}]}

As a backwards compatible change

Another approach would be to accept both references by name and by id. If I want to use the column id, I would do:

• order_by=[{"field_id":"1","direction":"asc"}]
• group_count_by_ids=["1"]
• filters={"and":[{"field_id":"1","op":"eq","value":"Sean"}]}

GET Response

Currently the records API response look like:

{
  "count": 2,
  "results": [
    {
      "id": 1,
      "first_name": "Bart",
      "last_name": "Simpson",
    },
    {
      "id": 2,
      "first_name": "Lisa",
      "last_name": "Simpson",
    }
  ]
}

To avoid relying on the column name, I'd like the response to give the column id as keys instead.

{
  "count": 2,
  "results": [
    {
      "1": 1,
      "2": "Bart",
      "3": "Simpson",
    },
    {
      "1": 2,
      "2": "Lisa",
      "3": "Simpson",
    }
  ]
}

This would need to be a breaking change.

POST and PATCH request and response

Request and response bodies look like:

{
  "name": "Sean"
}

I'd like to be able to update a record or add a new record by sending something like:

{
  "1": "Sean"
}

Additional context

• Related:
  • Replace column index usage with attnums and Column model id #839

[mathemancer]

I think the breaking change option is superior (though a bit of a pain).

[kgodey]

Agreed re: breaking change.

[silentninja]

I have a few concerns with this change

1. I assume we would use the same format for views too and views might be having computed columns that would not go well with id.
2. This would be useful if filters are long-lived, but I think we would be creating views rather than storing the filter queries
3. I think we discussed having tables filtered/sorted by a user-specified default in which case we might have to store them. So in such cases, I think the backend should rather do the conversion(name->id) and store it.

[kgodey]

1. I assume we would use the same format for views too and views might be having computed columns that would not go well with id.

We can figure out how to reflect Views when it comes to it, I don't know if we'll end up following the same format or not.

2. This would be useful if filters are long-lived, but I think we would be creating views rather than storing the filter queries

The frontend saves the filter queries that a user was last looking at. We might also cache this in the backend at some point.

3. I think we discussed having tables filtered/sorted by a user-specified default in which case we might have to store them. So in such cases, I think the backend should rather do the conversion(name->id) and store it.

I'm not sure what you're getting at here, can you elaborate?

---

The general principle here is the column only has a single identifier throughout the API, which is its id. This will also be more safe to work with, if someone drops the original column and creates a new column with the same name outside of Mathesar, we won't operate on it thinking it's the same column because the id will have changed during reflection.

[silentninja]

I think we discussed having tables filtered/sorted by a user-specified default in which case we might have to store them. So in such cases, I think the backend should rather do the conversion(name->id) and store it.

We discussed that a table can have a default filter or sort. So in case we end up not using id I was suggesting an alternate solution where the frontend would send it as name and backend would store it internally as id

[mathemancer]

1. I assume we would use the same format for views too and views might be having computed columns that would not go well with id.

Views have their own oid and the columns in views have their own attnum, so it's perfectly fine (with me at least) to give them ids. I.e., we should be creating view column models which inherit from normal table columns, and have a couple extra attributes for their other metadata. @kgodey If we set things up in Django properly, is it possible for the standard generated Django ids to be "global" across both column types?

[mathemancer]

Alternatively, we could extend the column model to simply note whether it's a column of a table or of a view, and have some optional fields for view-specific metadata.

[kgodey]

@kgodey If we set things up in Django properly, is it possible for the standard generated Django ids to be "global" across both column types?

Yup, we can have it be the same Django Column model if we want.

Alternatively, we could extend the column model to simply note whether it's a column of a table or of a view, and have some optional fields for view-specific metadata.

I think that's the same thing, not an alternative. We could have separate models for TableColumn and ViewColumn if we want alternatively.

[silentninja]

My only concern is that this would limit us from using dynamically computed columns that could be generated by various means like group_by but general consensus is to use views, so I am okay with this change.

[kgodey]

My only concern is that this would limit us from using dynamically computed columns that could be generated by various means like group_by but general consensus is to use views, so I am okay with this change.

If we do expose dynamically computed columns, we won't be using the same models as table columns or view columns to do so since they represent different underlying DB concepts. We can figure out the appropriate architecture for those when we get to them.

[seancolsen]

I moved this to [06] Working with Tables because it it blocking some other work in that milestone.