We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
Undesirable record grouping behaviours
## Description
Record grouping has a set of behaviours, that are not desirable.
* It considers order_by, which leads to formation of incorrect query on the backend, if we don't group by the sorted column.
![Screenshot 2021-07-13 at 12 05 01 AM](https://user-images.githubusercontent.com/11850603/125338760-3630af00-e36e-11eb-9240-68cd524c4eec.png)

* It considers limit and offset. These apply on the grouped result itself, and is unrelated to the record limit & offset.
![Screenshot 2021-07-13 at 12 02 20 AM](https://user-images.githubusercontent.com/11850603/125338797-45aff800-e36e-11eb-8845-91c128a21606.png)
![Screenshot 2021-07-13 at 12 02 42 AM](https://user-images.githubusercontent.com/11850603/125338811-49dc1580-e36e-11eb-8f5e-ca0560b41239.png)


## Expected behavior
* It should not consider order_by.
* It should not consider limit and offset.

We could also probably have a dedicated API for this. It could also obtain the values for columns, to filter the grouped results. Having it as part of records API makes less sense, since the group count is not a reflection of the record results.

--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `mathesar/pagination.py`
Content:
```
1 from collections import OrderedDict
2 
3 from rest_framework.pagination import LimitOffsetPagination
4 from rest_framework.response import Response
5 
6 
7 class DefaultLimitOffsetPagination(LimitOffsetPagination):
8     default_limit = 50
9     max_limit = 500
10 
11     def get_paginated_response(self, data):
12         return Response(OrderedDict([
13             ('count', self.count),
14             ('results', data)
15         ]))
16 
17 
18 class ColumnLimitOffsetPagination(DefaultLimitOffsetPagination):
19 
20     def paginate_queryset(self, queryset, request, table_id):
21         self.limit = self.get_limit(request)
22         if self.limit is None:
23             self.limit = self.default_limit
24         self.offset = self.get_offset(request)
25         table = queryset.get(id=table_id)
26         self.count = len(table.sa_columns)
27         self.request = request
28         return list(table.sa_columns)[self.offset:self.offset + self.limit]
29 
30 
31 class TableLimitOffsetPagination(DefaultLimitOffsetPagination):
32 
33     def paginate_queryset(self, queryset, request, table_id,
34                           filters=[], order_by=[]):
35         self.limit = self.get_limit(request)
36         if self.limit is None:
37             self.limit = self.default_limit
38         self.offset = self.get_offset(request)
39         # TODO: Cache count value somewhere, since calculating it is expensive.
40         table = queryset.get(id=table_id)
41         self.count = table.sa_num_records(filters=filters)
42         self.request = request
43 
44         return table.get_records(
45             self.limit, self.offset, filters=filters, order_by=order_by,
46         )
47 
48 
49 class TableLimitOffsetGroupPagination(TableLimitOffsetPagination):
50     def get_paginated_response(self, data):
51         return Response(OrderedDict([
52             ('count', self.count),
53             ('group_count', self.group_count),
54             ('results', data)
55         ]))
56 
57     def paginate_queryset(self, queryset, request, table_id,
58                           filters=[], order_by=[], group_count_by=[]):
59         records = super().paginate_queryset(
60             queryset, request, table_id, filters=filters, order_by=order_by
61         )
62 
63         table = queryset.get(id=table_id)
64         if group_count_by:
65             group_count = table.get_group_counts(
66                 group_count_by, self.limit, self.offset,
67                 filters=filters, order_by=order_by
68             )
69             # Convert the tuple keys into strings so it can be converted to JSON
70             group_count = {','.join(k): v for k, v in group_count.items()}
71             self.group_count = {
72                 'group_count_by': group_count_by,
73                 'results': group_count,
74             }
75         else:
76             self.group_count = {
77                 'group_count_by': None,
78                 'results': None,
79             }
80 
81         return records
82
```

Path: `db/records.py`
Content:
```
1 import logging
2 from sqlalchemy import delete, select, Column, func
3 from sqlalchemy.inspection import inspect
4 from sqlalchemy_filters import apply_filters, apply_sort
5 from sqlalchemy_filters.exceptions import FieldNotFound
6 
7 from db.constants import ID
8 
9 logger = logging.getLogger(__name__)
10 
11 
12 # Grouping exceptions follow the sqlalchemy_filters exceptions patterns
13 class BadGroupFormat(Exception):
14     pass
15 
16 
17 class GroupFieldNotFound(FieldNotFound):
18     pass
19 
20 
21 def _get_primary_key_column(table):
22     primary_key_list = list(inspect(table).primary_key)
23     # We do not support getting by composite primary keys
24     assert len(primary_key_list) == 1
25     return primary_key_list[0]
26 
27 
28 def _create_col_objects(table, column_list):
29     return [
30         table.columns[col] if type(col) == str else col
31         for col in column_list
32     ]
33 
34 
35 def get_record(table, engine, id_value):
36     primary_key_column = _get_primary_key_column(table)
37     query = select(table).where(primary_key_column == id_value)
38     with engine.begin() as conn:
39         result = conn.execute(query).fetchall()
40         assert len(result) <= 1
41         return result[0] if result else None
42 
43 
44 def get_records(
45         table, engine, limit=None, offset=None, order_by=[], filters=[],
46 ):
47     """
48     Returns records from a table.
49 
50     Args:
51         table:    SQLAlchemy table object
52         engine:   SQLAlchemy engine object
53         limit:    int, gives number of rows to return
54         offset:   int, gives number of rows to skip
55         order_by: list of dictionaries, where each dictionary has a 'field' and
56                   'direction' field.
57                   See: https://github.com/centerofci/sqlalchemy-filters#sort-format
58         filters:  list of dictionaries, where each dictionary has a 'field' and 'op'
59                   field, in addition to an 'value' field if appropriate.
60                   See: https://github.com/centerofci/sqlalchemy-filters#filters-format
61     """
62     query = select(table).limit(limit).offset(offset)
63     if order_by is not None:
64         query = apply_sort(query, order_by)
65     if filters is not None:
66         query = apply_filters(query, filters)
67     with engine.begin() as conn:
68         return conn.execute(query).fetchall()
69 
70 
71 def get_group_counts(
72         table, engine, group_by, limit=None, offset=None, order_by=[], filters=[],
73 ):
74     """
75     Returns counts by specified groupings
76 
77     Args:
78         table:    SQLAlchemy table object
79         engine:   SQLAlchemy engine object
80         limit:    int, gives number of rows to return
81         offset:   int, gives number of rows to skip
82         group_by: list or tuple of column names or column objects to group by
83         order_by: list of dictionaries, where each dictionary has a 'field' and
84                   'direction' field.
85                   See: https://github.com/centerofci/sqlalchemy-filters#sort-format
86         filters:  list of dictionaries, where each dictionary has a 'field' and 'op'
87                   field, in addition to an 'value' field if appropriate.
88                   See: https://github.com/centerofci/sqlalchemy-filters#filters-format
89     """
90     if type(group_by) not in (tuple, list):
91         raise BadGroupFormat(f"Group spec {group_by} must be list or tuple.")
92     for field in group_by:
93         if type(field) not in (str, Column):
94             raise BadGroupFormat(f"Group field {field} must be a string or Column.")
95         field_name = field if type(field) == str else field.name
96         if field_name not in table.c:
97             raise GroupFieldNotFound(f"Group field {field} not found in {table}.")
98 
99     group_by = _create_col_objects(table, group_by)
100     query = (
101         select(*group_by, func.count(table.c[ID]))
102         .group_by(*group_by)
103         .limit(limit)
104         .offset(offset)
105     )
106     if order_by is not None:
107         query = apply_sort(query, order_by)
108     if filters is not None:
109         query = apply_filters(query, filters)
110     with engine.begin() as conn:
111         records = conn.execute(query).fetchall()
112 
113     # Last field is the count, preceding fields are the group by fields
114     counts = {
115         (*record[:-1],): record[-1]
116         for record in records
117     }
118     return counts
119 
120 
121 def get_distinct_tuple_values(
122         column_list, engine, table=None, limit=None, offset=None,
123 ):
124     """
125     Returns distinct tuples from a given list of columns.
126 
127     Args:
128         column_list: list of column names or SQLAlchemy column objects
129         engine:   SQLAlchemy engine object
130         table:    SQLAlchemy table object
131         limit:    int, gives number of rows to return
132         offset:   int, gives number of rows to skip
133 
134     If no table is given, the column_list must consist entirely of
135     SQLAlchemy column objects associated with a table.
136     """
137     if table is not None:
138         column_objects = _create_col_objects(table, column_list)
139     else:
140         column_objects = column_list
141     try:
142         assert all([type(col) == Column for col in column_objects])
143     except AssertionError as e:
144         logger.error("All columns must be str or sqlalchemy.Column type")
145         raise e
146 
147     query = (
148         select(*column_objects)
149         .distinct()
150         .limit(limit)
151         .offset(offset)
152     )
153     with engine.begin() as conn:
154         res = conn.execute(query).fetchall()
155     return [tuple(zip(column_objects, row)) for row in res]
156 
157 
158 def distinct_tuples_to_filter(distinct_tuples):
159     filters = []
160     for col, value in distinct_tuples:
161         filters.append({
162             "field": col,
163             "op": "==",
164             "value": value,
165         })
166     return filters
167 
168 
169 def create_record_or_records(table, engine, record_data):
170     """
171     record_data can be a dictionary, tuple, or list of dictionaries or tuples.
172     if record_data is a list, it creates multiple records.
173     """
174     id_value = None
175     with engine.begin() as connection:
176         result = connection.execute(table.insert(), record_data)
177         # If there was only a single record created, return the record.
178         if result.rowcount == 1:
179             # We need to manually commit insertion so that we can retrieve the record.
180             connection.commit()
181             id_value = result.inserted_primary_key[0]
182             if id_value is not None:
183                 return get_record(table, engine, id_value)
184     # Do not return any records if multiple rows were added.
185     return None
186 
187 
188 def create_records_from_csv(table, engine, csv_filename, column_names, delimiter=None,
189                             escape=None, quote=None):
190     with open(csv_filename, 'rb') as csv_file:
191         with engine.begin() as conn:
192             cursor = conn.connection.cursor()
193             relation = '.'.join('"{}"'.format(part) for part in (table.schema, table.name))
194             formatted_columns = '({})'.format(','.join([f'"{column_name}"' for column_name in column_names]))
195 
196             copy_sql = f'COPY {relation} {formatted_columns} FROM STDIN CSV HEADER'
197             if delimiter:
198                 copy_sql += f" DELIMITER E'{delimiter}'"
199             if escape:
200                 copy_sql += f" ESCAPE '{escape}'"
201             if quote:
202                 if quote == "'":
203                     quote = "''"
204                 copy_sql += f" QUOTE '{quote}'"
205 
206             cursor.copy_expert(copy_sql, csv_file)
207 
208 
209 def update_record(table, engine, id_value, record_data):
210     primary_key_column = _get_primary_key_column(table)
211     with engine.begin() as connection:
212         connection.execute(
213             table.update().where(primary_key_column == id_value).values(record_data)
214         )
215     return get_record(table, engine, id_value)
216 
217 
218 def delete_record(table, engine, id_value):
219     primary_key_column = _get_primary_key_column(table)
220     query = delete(table).where(primary_key_column == id_value)
221     with engine.begin() as conn:
222         return conn.execute(query)
223
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/db/records.py b/db/records.py
--- a/db/records.py
+++ b/db/records.py
@@ -96,10 +96,8 @@
         if field_name not in table.c:
             raise GroupFieldNotFound(f"Group field {field} not found in {table}.")
 
-    group_by = _create_col_objects(table, group_by)
     query = (
-        select(*group_by, func.count(table.c[ID]))
-        .group_by(*group_by)
+        select(table)
         .limit(limit)
         .offset(offset)
     )
@@ -107,6 +105,13 @@
         query = apply_sort(query, order_by)
     if filters is not None:
         query = apply_filters(query, filters)
+    subquery = query.subquery()
+
+    group_by = [
+        subquery.columns[col] if type(col) == str else subquery.columns[col.name]
+        for col in group_by
+    ]
+    query = select(*group_by, func.count(subquery.c[ID])).group_by(*group_by)
     with engine.begin() as conn:
         records = conn.execute(query).fetchall()
 
diff --git a/mathesar/pagination.py b/mathesar/pagination.py
--- a/mathesar/pagination.py
+++ b/mathesar/pagination.py
@@ -67,7 +67,8 @@
                 filters=filters, order_by=order_by
             )
             # Convert the tuple keys into strings so it can be converted to JSON
-            group_count = {','.join(k): v for k, v in group_count.items()}
+            group_count = [{"values": list(cols), "count": count}
+                           for cols, count in group_count.items()]
             self.group_count = {
                 'group_count_by': group_count_by,
                 'results': group_count,

{"golden_diff": "diff --git a/db/records.py b/db/records.py\n--- a/db/records.py\n+++ b/db/records.py\n@@ -96,10 +96,8 @@\n         if field_name not in table.c:\n             raise GroupFieldNotFound(f\"Group field {field} not found in {table}.\")\n \n-    group_by = _create_col_objects(table, group_by)\n     query = (\n-        select(*group_by, func.count(table.c[ID]))\n-        .group_by(*group_by)\n+        select(table)\n         .limit(limit)\n         .offset(offset)\n     )\n@@ -107,6 +105,13 @@\n         query = apply_sort(query, order_by)\n     if filters is not None:\n         query = apply_filters(query, filters)\n+    subquery = query.subquery()\n+\n+    group_by = [\n+        subquery.columns[col] if type(col) == str else subquery.columns[col.name]\n+        for col in group_by\n+    ]\n+    query = select(*group_by, func.count(subquery.c[ID])).group_by(*group_by)\n     with engine.begin() as conn:\n         records = conn.execute(query).fetchall()\n \ndiff --git a/mathesar/pagination.py b/mathesar/pagination.py\n--- a/mathesar/pagination.py\n+++ b/mathesar/pagination.py\n@@ -67,7 +67,8 @@\n                 filters=filters, order_by=order_by\n             )\n             # Convert the tuple keys into strings so it can be converted to JSON\n-            group_count = {','.join(k): v for k, v in group_count.items()}\n+            group_count = [{\"values\": list(cols), \"count\": count}\n+                           for cols, count in group_count.items()]\n             self.group_count = {\n                 'group_count_by': group_count_by,\n                 'results': group_count,\n", "issue": "Undesirable record grouping behaviours\n## Description\r\nRecord grouping has a set of behaviours, that are not desirable.\r\n* It considers order_by, which leads to formation of incorrect query on the backend, if we don't group by the sorted column.\r\n![Screenshot 2021-07-13 at 12 05 01 AM](https://user-images.githubusercontent.com/11850603/125338760-3630af00-e36e-11eb-9240-68cd524c4eec.png)\r\n\r\n* It considers limit and offset. These apply on the grouped result itself, and is unrelated to the record limit & offset.\r\n![Screenshot 2021-07-13 at 12 02 20 AM](https://user-images.githubusercontent.com/11850603/125338797-45aff800-e36e-11eb-8845-91c128a21606.png)\r\n![Screenshot 2021-07-13 at 12 02 42 AM](https://user-images.githubusercontent.com/11850603/125338811-49dc1580-e36e-11eb-8f5e-ca0560b41239.png)\r\n\r\n\r\n## Expected behavior\r\n* It should not consider order_by.\r\n* It should not consider limit and offset.\r\n\r\nWe could also probably have a dedicated API for this. It could also obtain the values for columns, to filter the grouped results. Having it as part of records API makes less sense, since the group count is not a reflection of the record results.\n", "before_files": [{"content": "from collections import OrderedDict\n\nfrom rest_framework.pagination import LimitOffsetPagination\nfrom rest_framework.response import Response\n\n\nclass DefaultLimitOffsetPagination(LimitOffsetPagination):\n    default_limit = 50\n    max_limit = 500\n\n    def get_paginated_response(self, data):\n        return Response(OrderedDict([\n            ('count', self.count),\n            ('results', data)\n        ]))\n\n\nclass ColumnLimitOffsetPagination(DefaultLimitOffsetPagination):\n\n    def paginate_queryset(self, queryset, request, table_id):\n        self.limit = self.get_limit(request)\n        if self.limit is None:\n            self.limit = self.default_limit\n        self.offset = self.get_offset(request)\n        table = queryset.get(id=table_id)\n        self.count = len(table.sa_columns)\n        self.request = request\n        return list(table.sa_columns)[self.offset:self.offset + self.limit]\n\n\nclass TableLimitOffsetPagination(DefaultLimitOffsetPagination):\n\n    def paginate_queryset(self, queryset, request, table_id,\n                          filters=[], order_by=[]):\n        self.limit = self.get_limit(request)\n        if self.limit is None:\n            self.limit = self.default_limit\n        self.offset = self.get_offset(request)\n        # TODO: Cache count value somewhere, since calculating it is expensive.\n        table = queryset.get(id=table_id)\n        self.count = table.sa_num_records(filters=filters)\n        self.request = request\n\n        return table.get_records(\n            self.limit, self.offset, filters=filters, order_by=order_by,\n        )\n\n\nclass TableLimitOffsetGroupPagination(TableLimitOffsetPagination):\n    def get_paginated_response(self, data):\n        return Response(OrderedDict([\n            ('count', self.count),\n            ('group_count', self.group_count),\n            ('results', data)\n        ]))\n\n    def paginate_queryset(self, queryset, request, table_id,\n                          filters=[], order_by=[], group_count_by=[]):\n        records = super().paginate_queryset(\n            queryset, request, table_id, filters=filters, order_by=order_by\n        )\n\n        table = queryset.get(id=table_id)\n        if group_count_by:\n            group_count = table.get_group_counts(\n                group_count_by, self.limit, self.offset,\n                filters=filters, order_by=order_by\n            )\n            # Convert the tuple keys into strings so it can be converted to JSON\n            group_count = {','.join(k): v for k, v in group_count.items()}\n            self.group_count = {\n                'group_count_by': group_count_by,\n                'results': group_count,\n            }\n        else:\n            self.group_count = {\n                'group_count_by': None,\n                'results': None,\n            }\n\n        return records\n", "path": "mathesar/pagination.py"}, {"content": "import logging\nfrom sqlalchemy import delete, select, Column, func\nfrom sqlalchemy.inspection import inspect\nfrom sqlalchemy_filters import apply_filters, apply_sort\nfrom sqlalchemy_filters.exceptions import FieldNotFound\n\nfrom db.constants import ID\n\nlogger = logging.getLogger(__name__)\n\n\n# Grouping exceptions follow the sqlalchemy_filters exceptions patterns\nclass BadGroupFormat(Exception):\n    pass\n\n\nclass GroupFieldNotFound(FieldNotFound):\n    pass\n\n\ndef _get_primary_key_column(table):\n    primary_key_list = list(inspect(table).primary_key)\n    # We do not support getting by composite primary keys\n    assert len(primary_key_list) == 1\n    return primary_key_list[0]\n\n\ndef _create_col_objects(table, column_list):\n    return [\n        table.columns[col] if type(col) == str else col\n        for col in column_list\n    ]\n\n\ndef get_record(table, engine, id_value):\n    primary_key_column = _get_primary_key_column(table)\n    query = select(table).where(primary_key_column == id_value)\n    with engine.begin() as conn:\n        result = conn.execute(query).fetchall()\n        assert len(result) <= 1\n        return result[0] if result else None\n\n\ndef get_records(\n        table, engine, limit=None, offset=None, order_by=[], filters=[],\n):\n    \"\"\"\n    Returns records from a table.\n\n    Args:\n        table:    SQLAlchemy table object\n        engine:   SQLAlchemy engine object\n        limit:    int, gives number of rows to return\n        offset:   int, gives number of rows to skip\n        order_by: list of dictionaries, where each dictionary has a 'field' and\n                  'direction' field.\n                  See: https://github.com/centerofci/sqlalchemy-filters#sort-format\n        filters:  list of dictionaries, where each dictionary has a 'field' and 'op'\n                  field, in addition to an 'value' field if appropriate.\n                  See: https://github.com/centerofci/sqlalchemy-filters#filters-format\n    \"\"\"\n    query = select(table).limit(limit).offset(offset)\n    if order_by is not None:\n        query = apply_sort(query, order_by)\n    if filters is not None:\n        query = apply_filters(query, filters)\n    with engine.begin() as conn:\n        return conn.execute(query).fetchall()\n\n\ndef get_group_counts(\n        table, engine, group_by, limit=None, offset=None, order_by=[], filters=[],\n):\n    \"\"\"\n    Returns counts by specified groupings\n\n    Args:\n        table:    SQLAlchemy table object\n        engine:   SQLAlchemy engine object\n        limit:    int, gives number of rows to return\n        offset:   int, gives number of rows to skip\n        group_by: list or tuple of column names or column objects to group by\n        order_by: list of dictionaries, where each dictionary has a 'field' and\n                  'direction' field.\n                  See: https://github.com/centerofci/sqlalchemy-filters#sort-format\n        filters:  list of dictionaries, where each dictionary has a 'field' and 'op'\n                  field, in addition to an 'value' field if appropriate.\n                  See: https://github.com/centerofci/sqlalchemy-filters#filters-format\n    \"\"\"\n    if type(group_by) not in (tuple, list):\n        raise BadGroupFormat(f\"Group spec {group_by} must be list or tuple.\")\n    for field in group_by:\n        if type(field) not in (str, Column):\n            raise BadGroupFormat(f\"Group field {field} must be a string or Column.\")\n        field_name = field if type(field) == str else field.name\n        if field_name not in table.c:\n            raise GroupFieldNotFound(f\"Group field {field} not found in {table}.\")\n\n    group_by = _create_col_objects(table, group_by)\n    query = (\n        select(*group_by, func.count(table.c[ID]))\n        .group_by(*group_by)\n        .limit(limit)\n        .offset(offset)\n    )\n    if order_by is not None:\n        query = apply_sort(query, order_by)\n    if filters is not None:\n        query = apply_filters(query, filters)\n    with engine.begin() as conn:\n        records = conn.execute(query).fetchall()\n\n    # Last field is the count, preceding fields are the group by fields\n    counts = {\n        (*record[:-1],): record[-1]\n        for record in records\n    }\n    return counts\n\n\ndef get_distinct_tuple_values(\n        column_list, engine, table=None, limit=None, offset=None,\n):\n    \"\"\"\n    Returns distinct tuples from a given list of columns.\n\n    Args:\n        column_list: list of column names or SQLAlchemy column objects\n        engine:   SQLAlchemy engine object\n        table:    SQLAlchemy table object\n        limit:    int, gives number of rows to return\n        offset:   int, gives number of rows to skip\n\n    If no table is given, the column_list must consist entirely of\n    SQLAlchemy column objects associated with a table.\n    \"\"\"\n    if table is not None:\n        column_objects = _create_col_objects(table, column_list)\n    else:\n        column_objects = column_list\n    try:\n        assert all([type(col) == Column for col in column_objects])\n    except AssertionError as e:\n        logger.error(\"All columns must be str or sqlalchemy.Column type\")\n        raise e\n\n    query = (\n        select(*column_objects)\n        .distinct()\n        .limit(limit)\n        .offset(offset)\n    )\n    with engine.begin() as conn:\n        res = conn.execute(query).fetchall()\n    return [tuple(zip(column_objects, row)) for row in res]\n\n\ndef distinct_tuples_to_filter(distinct_tuples):\n    filters = []\n    for col, value in distinct_tuples:\n        filters.append({\n            \"field\": col,\n            \"op\": \"==\",\n            \"value\": value,\n        })\n    return filters\n\n\ndef create_record_or_records(table, engine, record_data):\n    \"\"\"\n    record_data can be a dictionary, tuple, or list of dictionaries or tuples.\n    if record_data is a list, it creates multiple records.\n    \"\"\"\n    id_value = None\n    with engine.begin() as connection:\n        result = connection.execute(table.insert(), record_data)\n        # If there was only a single record created, return the record.\n        if result.rowcount == 1:\n            # We need to manually commit insertion so that we can retrieve the record.\n            connection.commit()\n            id_value = result.inserted_primary_key[0]\n            if id_value is not None:\n                return get_record(table, engine, id_value)\n    # Do not return any records if multiple rows were added.\n    return None\n\n\ndef create_records_from_csv(table, engine, csv_filename, column_names, delimiter=None,\n                            escape=None, quote=None):\n    with open(csv_filename, 'rb') as csv_file:\n        with engine.begin() as conn:\n            cursor = conn.connection.cursor()\n            relation = '.'.join('\"{}\"'.format(part) for part in (table.schema, table.name))\n            formatted_columns = '({})'.format(','.join([f'\"{column_name}\"' for column_name in column_names]))\n\n            copy_sql = f'COPY {relation} {formatted_columns} FROM STDIN CSV HEADER'\n            if delimiter:\n                copy_sql += f\" DELIMITER E'{delimiter}'\"\n            if escape:\n                copy_sql += f\" ESCAPE '{escape}'\"\n            if quote:\n                if quote == \"'\":\n                    quote = \"''\"\n                copy_sql += f\" QUOTE '{quote}'\"\n\n            cursor.copy_expert(copy_sql, csv_file)\n\n\ndef update_record(table, engine, id_value, record_data):\n    primary_key_column = _get_primary_key_column(table)\n    with engine.begin() as connection:\n        connection.execute(\n            table.update().where(primary_key_column == id_value).values(record_data)\n        )\n    return get_record(table, engine, id_value)\n\n\ndef delete_record(table, engine, id_value):\n    primary_key_column = _get_primary_key_column(table)\n    query = delete(table).where(primary_key_column == id_value)\n    with engine.begin() as conn:\n        return conn.execute(query)\n", "path": "db/records.py"}], "after_files": [{"content": "from collections import OrderedDict\n\nfrom rest_framework.pagination import LimitOffsetPagination\nfrom rest_framework.response import Response\n\n\nclass DefaultLimitOffsetPagination(LimitOffsetPagination):\n    default_limit = 50\n    max_limit = 500\n\n    def get_paginated_response(self, data):\n        return Response(OrderedDict([\n            ('count', self.count),\n            ('results', data)\n        ]))\n\n\nclass ColumnLimitOffsetPagination(DefaultLimitOffsetPagination):\n\n    def paginate_queryset(self, queryset, request, table_id):\n        self.limit = self.get_limit(request)\n        if self.limit is None:\n            self.limit = self.default_limit\n        self.offset = self.get_offset(request)\n        table = queryset.get(id=table_id)\n        self.count = len(table.sa_columns)\n        self.request = request\n        return list(table.sa_columns)[self.offset:self.offset + self.limit]\n\n\nclass TableLimitOffsetPagination(DefaultLimitOffsetPagination):\n\n    def paginate_queryset(self, queryset, request, table_id,\n                          filters=[], order_by=[]):\n        self.limit = self.get_limit(request)\n        if self.limit is None:\n            self.limit = self.default_limit\n        self.offset = self.get_offset(request)\n        # TODO: Cache count value somewhere, since calculating it is expensive.\n        table = queryset.get(id=table_id)\n        self.count = table.sa_num_records(filters=filters)\n        self.request = request\n\n        return table.get_records(\n            self.limit, self.offset, filters=filters, order_by=order_by,\n        )\n\n\nclass TableLimitOffsetGroupPagination(TableLimitOffsetPagination):\n    def get_paginated_response(self, data):\n        return Response(OrderedDict([\n            ('count', self.count),\n            ('group_count', self.group_count),\n            ('results', data)\n        ]))\n\n    def paginate_queryset(self, queryset, request, table_id,\n                          filters=[], order_by=[], group_count_by=[]):\n        records = super().paginate_queryset(\n            queryset, request, table_id, filters=filters, order_by=order_by\n        )\n\n        table = queryset.get(id=table_id)\n        if group_count_by:\n            group_count = table.get_group_counts(\n                group_count_by, self.limit, self.offset,\n                filters=filters, order_by=order_by\n            )\n            # Convert the tuple keys into strings so it can be converted to JSON\n            group_count = [{\"values\": list(cols), \"count\": count}\n                           for cols, count in group_count.items()]\n            self.group_count = {\n                'group_count_by': group_count_by,\n                'results': group_count,\n            }\n        else:\n            self.group_count = {\n                'group_count_by': None,\n                'results': None,\n            }\n\n        return records\n", "path": "mathesar/pagination.py"}, {"content": "import logging\nfrom sqlalchemy import delete, select, Column, func\nfrom sqlalchemy.inspection import inspect\nfrom sqlalchemy_filters import apply_filters, apply_sort\nfrom sqlalchemy_filters.exceptions import FieldNotFound\n\nfrom db.constants import ID\n\nlogger = logging.getLogger(__name__)\n\n\n# Grouping exceptions follow the sqlalchemy_filters exceptions patterns\nclass BadGroupFormat(Exception):\n    pass\n\n\nclass GroupFieldNotFound(FieldNotFound):\n    pass\n\n\ndef _get_primary_key_column(table):\n    primary_key_list = list(inspect(table).primary_key)\n    # We do not support getting by composite primary keys\n    assert len(primary_key_list) == 1\n    return primary_key_list[0]\n\n\ndef _create_col_objects(table, column_list):\n    return [\n        table.columns[col] if type(col) == str else col\n        for col in column_list\n    ]\n\n\ndef get_record(table, engine, id_value):\n    primary_key_column = _get_primary_key_column(table)\n    query = select(table).where(primary_key_column == id_value)\n    with engine.begin() as conn:\n        result = conn.execute(query).fetchall()\n        assert len(result) <= 1\n        return result[0] if result else None\n\n\ndef get_records(\n        table, engine, limit=None, offset=None, order_by=[], filters=[],\n):\n    \"\"\"\n    Returns records from a table.\n\n    Args:\n        table:    SQLAlchemy table object\n        engine:   SQLAlchemy engine object\n        limit:    int, gives number of rows to return\n        offset:   int, gives number of rows to skip\n        order_by: list of dictionaries, where each dictionary has a 'field' and\n                  'direction' field.\n                  See: https://github.com/centerofci/sqlalchemy-filters#sort-format\n        filters:  list of dictionaries, where each dictionary has a 'field' and 'op'\n                  field, in addition to an 'value' field if appropriate.\n                  See: https://github.com/centerofci/sqlalchemy-filters#filters-format\n    \"\"\"\n    query = select(table).limit(limit).offset(offset)\n    if order_by is not None:\n        query = apply_sort(query, order_by)\n    if filters is not None:\n        query = apply_filters(query, filters)\n    with engine.begin() as conn:\n        return conn.execute(query).fetchall()\n\n\ndef get_group_counts(\n        table, engine, group_by, limit=None, offset=None, order_by=[], filters=[],\n):\n    \"\"\"\n    Returns counts by specified groupings\n\n    Args:\n        table:    SQLAlchemy table object\n        engine:   SQLAlchemy engine object\n        limit:    int, gives number of rows to return\n        offset:   int, gives number of rows to skip\n        group_by: list or tuple of column names or column objects to group by\n        order_by: list of dictionaries, where each dictionary has a 'field' and\n                  'direction' field.\n                  See: https://github.com/centerofci/sqlalchemy-filters#sort-format\n        filters:  list of dictionaries, where each dictionary has a 'field' and 'op'\n                  field, in addition to an 'value' field if appropriate.\n                  See: https://github.com/centerofci/sqlalchemy-filters#filters-format\n    \"\"\"\n    if type(group_by) not in (tuple, list):\n        raise BadGroupFormat(f\"Group spec {group_by} must be list or tuple.\")\n    for field in group_by:\n        if type(field) not in (str, Column):\n            raise BadGroupFormat(f\"Group field {field} must be a string or Column.\")\n        field_name = field if type(field) == str else field.name\n        if field_name not in table.c:\n            raise GroupFieldNotFound(f\"Group field {field} not found in {table}.\")\n\n    query = (\n        select(table)\n        .limit(limit)\n        .offset(offset)\n    )\n    if order_by is not None:\n        query = apply_sort(query, order_by)\n    if filters is not None:\n        query = apply_filters(query, filters)\n    subquery = query.subquery()\n\n    group_by = [\n        subquery.columns[col] if type(col) == str else subquery.columns[col.name]\n        for col in group_by\n    ]\n    query = select(*group_by, func.count(subquery.c[ID])).group_by(*group_by)\n    with engine.begin() as conn:\n        records = conn.execute(query).fetchall()\n\n    # Last field is the count, preceding fields are the group by fields\n    counts = {\n        (*record[:-1],): record[-1]\n        for record in records\n    }\n    return counts\n\n\ndef get_distinct_tuple_values(\n        column_list, engine, table=None, limit=None, offset=None,\n):\n    \"\"\"\n    Returns distinct tuples from a given list of columns.\n\n    Args:\n        column_list: list of column names or SQLAlchemy column objects\n        engine:   SQLAlchemy engine object\n        table:    SQLAlchemy table object\n        limit:    int, gives number of rows to return\n        offset:   int, gives number of rows to skip\n\n    If no table is given, the column_list must consist entirely of\n    SQLAlchemy column objects associated with a table.\n    \"\"\"\n    if table is not None:\n        column_objects = _create_col_objects(table, column_list)\n    else:\n        column_objects = column_list\n    try:\n        assert all([type(col) == Column for col in column_objects])\n    except AssertionError as e:\n        logger.error(\"All columns must be str or sqlalchemy.Column type\")\n        raise e\n\n    query = (\n        select(*column_objects)\n        .distinct()\n        .limit(limit)\n        .offset(offset)\n    )\n    with engine.begin() as conn:\n        res = conn.execute(query).fetchall()\n    return [tuple(zip(column_objects, row)) for row in res]\n\n\ndef distinct_tuples_to_filter(distinct_tuples):\n    filters = []\n    for col, value in distinct_tuples:\n        filters.append({\n            \"field\": col,\n            \"op\": \"==\",\n            \"value\": value,\n        })\n    return filters\n\n\ndef create_record_or_records(table, engine, record_data):\n    \"\"\"\n    record_data can be a dictionary, tuple, or list of dictionaries or tuples.\n    if record_data is a list, it creates multiple records.\n    \"\"\"\n    id_value = None\n    with engine.begin() as connection:\n        result = connection.execute(table.insert(), record_data)\n        # If there was only a single record created, return the record.\n        if result.rowcount == 1:\n            # We need to manually commit insertion so that we can retrieve the record.\n            connection.commit()\n            id_value = result.inserted_primary_key[0]\n            if id_value is not None:\n                return get_record(table, engine, id_value)\n    # Do not return any records if multiple rows were added.\n    return None\n\n\ndef create_records_from_csv(table, engine, csv_filename, column_names, delimiter=None,\n                            escape=None, quote=None):\n    with open(csv_filename, 'rb') as csv_file:\n        with engine.begin() as conn:\n            cursor = conn.connection.cursor()\n            relation = '.'.join('\"{}\"'.format(part) for part in (table.schema, table.name))\n            formatted_columns = '({})'.format(','.join([f'\"{column_name}\"' for column_name in column_names]))\n\n            copy_sql = f'COPY {relation} {formatted_columns} FROM STDIN CSV HEADER'\n            if delimiter:\n                copy_sql += f\" DELIMITER E'{delimiter}'\"\n            if escape:\n                copy_sql += f\" ESCAPE '{escape}'\"\n            if quote:\n                if quote == \"'\":\n                    quote = \"''\"\n                copy_sql += f\" QUOTE '{quote}'\"\n\n            cursor.copy_expert(copy_sql, csv_file)\n\n\ndef update_record(table, engine, id_value, record_data):\n    primary_key_column = _get_primary_key_column(table)\n    with engine.begin() as connection:\n        connection.execute(\n            table.update().where(primary_key_column == id_value).values(record_data)\n        )\n    return get_record(table, engine, id_value)\n\n\ndef delete_record(table, engine, id_value):\n    primary_key_column = _get_primary_key_column(table)\n    query = delete(table).where(primary_key_column == id_value)\n    with engine.begin() as conn:\n        return conn.execute(query)\n", "path": "db/records.py"}]}