We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
Something is wrong with the data explorer....
<!-- A clear and concise description of what the bug is. -->
I'm trying to make the Overdue Books report and ended up with this:
<img width="1151" alt="Screen Shot 2022-12-20 at 7 23 10 PM" src="https://user-images.githubusercontent.com/287034/208792427-f8b7bc2a-7228-4c11-90d0-d95766bcd7e4.png">

Aaron Black has not checked out the same book over and over again, here's what his checkouts actually look like.
![Screenshot 2022-12-20 at 19-27-52 Checkouts Library Management Mathesar](https://user-images.githubusercontent.com/287034/208792546-da7f06f7-1adc-4f04-879a-23de904da309.png)

If I try to recreate the exploration, everything looks fine - I add Patron name, email, and book title (I selected do not summarize) and get this:
![Screenshot 2022-12-20 at 19-29-36 Data Explorer Library Management Mathesar](https://user-images.githubusercontent.com/287034/208792856-46012765-44e5-4c9d-8e61-aa91329bfbb4.png)

Then I add Due Date from Checkouts and everything goes nuts

![Screenshot 2022-12-20 at 19-30-01 Data Explorer Library Management Mathesar](https://user-images.githubusercontent.com/287034/208792901-6d923733-569d-464b-9da4-691b33955c75.png)


--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `db/queries/base.py`
Content:
```
1 from frozendict import frozendict
2 from sqlalchemy import select
3 
4 from db.records.operations import select as records_select
5 from db.columns.base import MathesarColumn
6 from db.columns.operations.select import get_column_name_from_attnum
7 from db.tables.operations.select import reflect_table_from_oid
8 from db.transforms.operations.apply import apply_transformations
9 from db.metadata import get_empty_metadata
10 
11 
12 class DBQuery:
13     def __init__(
14             self,
15             base_table_oid,
16             initial_columns,
17             engine,
18             transformations=None,
19             name=None,
20             # The same metadata will be used by all the methods within DBQuery
21             # So make sure to change the metadata in case the DBQuery methods are called
22             # after a mutation to the database object that could make the existing metadata invalid.
23             metadata=None
24     ):
25         self.base_table_oid = base_table_oid
26         for initial_col in initial_columns:
27             assert isinstance(initial_col, InitialColumn)
28         self.initial_columns = initial_columns
29         self.engine = engine
30         if transformations is None:
31             # Less states to consider if no transformations is just an empty sequence
32             transformations = tuple()
33         self.transformations = transformations
34         self.name = name
35         self.metadata = metadata if metadata else get_empty_metadata()
36 
37     def get_input_aliases(self, ix_of_transform):
38         """
39         Each transformation in a DBQuery has its own list of input aliases; this returns it.
40         """
41         initial_aliases = self.initial_aliases
42         if ix_of_transform == 0:
43             return initial_aliases
44         input_aliases = initial_aliases
45         previous_transforms = self.transformations[:ix_of_transform]
46         for transform in previous_transforms:
47             output_aliases = transform.get_output_aliases(input_aliases)
48             input_aliases = output_aliases
49         return input_aliases
50 
51     def get_initial_column_by_input_alias(self, ix_of_transform, input_alias):
52         """
53         Retraces the chain of input aliases until it gets to an initial column.
54 
55         Returns None if the alias does not originate from an initial column in a way that would
56         preserve a unique constraint. E.g. if it is generated by an aggregation.
57         """
58         initial_col_alias = \
59             self._get_initial_alias_by_input_alias(ix_of_transform, input_alias)
60         if initial_col_alias is None:
61             return None
62         initial_column = \
63             self._get_initial_column_by_initial_column_alias(initial_col_alias)
64         return initial_column
65 
66     def _get_initial_alias_by_input_alias(self, ix_of_transform, input_alias):
67         if ix_of_transform == 0:
68             return input_alias
69         transforms = self.transformations[:ix_of_transform]
70         initial_aliases = self.initial_aliases
71         input_aliases = initial_aliases
72         uc_mappings_for_each_transform = [
73             transform.get_unique_constraint_mappings(input_aliases)
74             for transform in transforms
75         ]
76         for uc_mappings in reversed(uc_mappings_for_each_transform):
77             for uc_mapping in uc_mappings:
78                 if uc_mapping.output_alias == input_alias:
79                     input_alias = uc_mapping.input_alias
80                     if input_alias is None:
81                         return None
82                     break
83         initial_alias = input_alias
84         return initial_alias
85 
86     def _get_initial_column_by_initial_column_alias(self, alias):
87         """
88         Looks up an initial column by initial column alias; no recursive logic.
89         """
90         for initial_column in self.initial_columns:
91             if initial_column.alias == alias:
92                 return initial_column
93 
94     @property
95     def initial_aliases(self):
96         return [
97             initial_column.alias
98             for initial_column
99             in self.initial_columns
100         ]
101 
102     # mirrors a method in db.records.operations.select
103     def get_records(self, **kwargs):
104         # NOTE how through this method you can perform a second batch of
105         # transformations.  this reflects fact that we can form a query, and
106         # then apply temporary transforms on it, like how you can apply
107         # temporary transforms to a table when in a table view.
108         return records_select.get_records_with_default_order(
109             table=self.transformed_relation, engine=self.engine, **kwargs,
110         )
111 
112     # mirrors a method in db.records.operations.select
113     def get_count(self, **kwargs):
114         return records_select.get_count(
115             table=self.transformed_relation, engine=self.engine, **kwargs,
116         )
117 
118     # NOTE if too expensive, can be rewritten to parse DBQuery spec, instead of leveraging sqlalchemy
119     @property
120     def all_sa_columns_map(self):
121         """
122         Expensive! use with care.
123         """
124         initial_columns_map = {
125             col.name: MathesarColumn.from_column(col, engine=self.engine)
126             for col in self.initial_relation.columns
127         }
128         output_columns_map = {
129             col.name: col for col in self.sa_output_columns
130         }
131         transforms_columns_map = {} if self.transformations is None else {
132             col.name: MathesarColumn.from_column(col, engine=self.engine)
133             for i in range(len(self.transformations))
134             for col in DBQuery(
135                 base_table_oid=self.base_table_oid,
136                 initial_columns=self.initial_columns,
137                 engine=self.engine,
138                 transformations=self.transformations[:i],
139                 name=f'{self.name}_{i}'
140             ).transformed_relation.columns
141         }
142         map_of_alias_to_sa_col = initial_columns_map | transforms_columns_map | output_columns_map
143         return map_of_alias_to_sa_col
144 
145     @property
146     def sa_output_columns(self):
147         """
148         Sequence of SQLAlchemy columns representing the output columns of the
149         relation described by this query.
150         """
151         return tuple(
152             MathesarColumn.from_column(sa_col, engine=self.engine)
153             for sa_col
154             in self.transformed_relation.columns
155         )
156 
157     @property
158     def transformed_relation(self):
159         """
160         A query describes a relation. This property is the result of parsing a
161         query into a relation.
162         """
163         transformations = self.transformations
164         if transformations:
165             transformed = apply_transformations(
166                 self.initial_relation,
167                 transformations,
168             )
169             return transformed
170         else:
171             return self.initial_relation
172 
173     @property
174     def initial_relation(self):
175         metadata = self.metadata
176         base_table = reflect_table_from_oid(
177             self.base_table_oid, self.engine, metadata=metadata
178         )
179         from_clause = base_table
180         # We cache this to avoid copies of the same join path to a given table
181         jp_path_alias_map = {(): base_table}
182 
183         def _get_table(oid):
184             """
185             We use the function-scoped metadata so all involved tables are aware
186             of each other.
187             """
188             return reflect_table_from_oid(oid, self.engine, metadata=metadata, keep_existing=True)
189 
190         def _get_column_name(oid, attnum):
191             return get_column_name_from_attnum(oid, attnum, self.engine, metadata=metadata)
192 
193         def _process_initial_column(col):
194             nonlocal from_clause
195             col_name = _get_column_name(col.reloid, col.attnum)
196             # Make the path hashable so it can be a dict key
197             jp_path = _guarantee_jp_path_tuples(col.jp_path)
198             right = base_table
199 
200             for i, jp in enumerate(jp_path):
201                 left = jp_path_alias_map[jp_path[:i]]
202                 right = _get_table(jp[1][0]).alias()
203                 jp_path_alias_map[jp_path[:i + 1]] = right
204                 left_col_name = _get_column_name(jp[0][0], jp[0][1])
205                 right_col_name = _get_column_name(jp[1][0], jp[1][1])
206                 left_col = left.columns[left_col_name]
207                 right_col = right.columns[right_col_name]
208                 from_clause = from_clause.join(
209                     right, onclause=left_col == right_col, isouter=True,
210                 )
211 
212             return right.columns[col_name].label(col.alias)
213 
214         stmt = select(
215             [_process_initial_column(col) for col in self.initial_columns]
216         ).select_from(from_clause)
217         return stmt.cte()
218 
219     def get_input_alias_for_output_alias(self, output_alias):
220         return self.map_of_output_alias_to_input_alias.get(output_alias)
221 
222     # TODO consider caching; not urgent, since redundant calls don't trigger IO, it seems
223     @property
224     def map_of_output_alias_to_input_alias(self):
225         m = dict()
226         transforms = self.transformations
227         if transforms:
228             for transform in transforms:
229                 m = m | transform.map_of_output_alias_to_input_alias
230         return m
231 
232 
233 class InitialColumn:
234     def __init__(
235             self,
236             # TODO consider renaming to oid; reloid is not a term we use,
237             # even if it's what postgres uses; or use reloid more
238             reloid,
239             attnum,
240             alias,
241             jp_path=None,
242     ):
243         # alias mustn't be an empty string
244         assert isinstance(alias, str) and alias.strip() != ""
245         self.reloid = reloid
246         self.attnum = attnum
247         self.alias = alias
248         self.jp_path = _guarantee_jp_path_tuples(jp_path)
249 
250     @property
251     def is_base_column(self):
252         """
253         A base column is an initial column on a query's base table.
254         """
255         return self.jp_path is None
256 
257     def __eq__(self, other):
258         """Instances are equal when attributes are equal."""
259         if type(other) is type(self):
260             return self.__dict__ == other.__dict__
261         return False
262 
263     def __hash__(self):
264         """Hashes are equal when attributes are equal."""
265         return hash(frozendict(self.__dict__))
266 
267 
268 def _guarantee_jp_path_tuples(jp_path):
269     """
270     Makes sure that jp_path is made up of tuples or is an empty tuple.
271     """
272     if jp_path is not None:
273         return tuple(
274             (
275                 tuple(edge[0]),
276                 tuple(edge[1]),
277             )
278             for edge
279             in jp_path
280         )
281     else:
282         return tuple()
283
```
--- 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/queries/base.py b/db/queries/base.py
--- a/db/queries/base.py
+++ b/db/queries/base.py
@@ -179,6 +179,7 @@
         from_clause = base_table
         # We cache this to avoid copies of the same join path to a given table
         jp_path_alias_map = {(): base_table}
+        jp_path_unique_set = set()
 
         def _get_table(oid):
             """
@@ -199,15 +200,22 @@
 
             for i, jp in enumerate(jp_path):
                 left = jp_path_alias_map[jp_path[:i]]
-                right = _get_table(jp[1][0]).alias()
-                jp_path_alias_map[jp_path[:i + 1]] = right
+                right_table = jp_path[:i + 1]
+                if right_table in jp_path_alias_map:
+                    right = jp_path_alias_map[right_table]
+                else:
+                    right = _get_table(jp[1][0]).alias()
+                    jp_path_alias_map[jp_path[:i + 1]] = right
                 left_col_name = _get_column_name(jp[0][0], jp[0][1])
                 right_col_name = _get_column_name(jp[1][0], jp[1][1])
                 left_col = left.columns[left_col_name]
                 right_col = right.columns[right_col_name]
-                from_clause = from_clause.join(
-                    right, onclause=left_col == right_col, isouter=True,
-                )
+                join_columns = f"{left_col}, {right_col}"
+                if join_columns not in jp_path_unique_set:
+                    jp_path_unique_set.add(join_columns)
+                    from_clause = from_clause.join(
+                        right, onclause=left_col == right_col, isouter=True,
+                    )
 
             return right.columns[col_name].label(col.alias)

{"golden_diff": "diff --git a/db/queries/base.py b/db/queries/base.py\n--- a/db/queries/base.py\n+++ b/db/queries/base.py\n@@ -179,6 +179,7 @@\n         from_clause = base_table\n         # We cache this to avoid copies of the same join path to a given table\n         jp_path_alias_map = {(): base_table}\n+        jp_path_unique_set = set()\n \n         def _get_table(oid):\n             \"\"\"\n@@ -199,15 +200,22 @@\n \n             for i, jp in enumerate(jp_path):\n                 left = jp_path_alias_map[jp_path[:i]]\n-                right = _get_table(jp[1][0]).alias()\n-                jp_path_alias_map[jp_path[:i + 1]] = right\n+                right_table = jp_path[:i + 1]\n+                if right_table in jp_path_alias_map:\n+                    right = jp_path_alias_map[right_table]\n+                else:\n+                    right = _get_table(jp[1][0]).alias()\n+                    jp_path_alias_map[jp_path[:i + 1]] = right\n                 left_col_name = _get_column_name(jp[0][0], jp[0][1])\n                 right_col_name = _get_column_name(jp[1][0], jp[1][1])\n                 left_col = left.columns[left_col_name]\n                 right_col = right.columns[right_col_name]\n-                from_clause = from_clause.join(\n-                    right, onclause=left_col == right_col, isouter=True,\n-                )\n+                join_columns = f\"{left_col}, {right_col}\"\n+                if join_columns not in jp_path_unique_set:\n+                    jp_path_unique_set.add(join_columns)\n+                    from_clause = from_clause.join(\n+                        right, onclause=left_col == right_col, isouter=True,\n+                    )\n \n             return right.columns[col_name].label(col.alias)\n", "issue": "Something is wrong with the data explorer....\n<!-- A clear and concise description of what the bug is. -->\r\nI'm trying to make the Overdue Books report and ended up with this:\r\n<img width=\"1151\" alt=\"Screen Shot 2022-12-20 at 7 23 10 PM\" src=\"https://user-images.githubusercontent.com/287034/208792427-f8b7bc2a-7228-4c11-90d0-d95766bcd7e4.png\">\r\n\r\nAaron Black has not checked out the same book over and over again, here's what his checkouts actually look like.\r\n![Screenshot 2022-12-20 at 19-27-52 Checkouts Library Management Mathesar](https://user-images.githubusercontent.com/287034/208792546-da7f06f7-1adc-4f04-879a-23de904da309.png)\r\n\r\nIf I try to recreate the exploration, everything looks fine - I add Patron name, email, and book title (I selected do not summarize) and get this:\r\n![Screenshot 2022-12-20 at 19-29-36 Data Explorer Library Management Mathesar](https://user-images.githubusercontent.com/287034/208792856-46012765-44e5-4c9d-8e61-aa91329bfbb4.png)\r\n\r\nThen I add Due Date from Checkouts and everything goes nuts\r\n\r\n![Screenshot 2022-12-20 at 19-30-01 Data Explorer Library Management Mathesar](https://user-images.githubusercontent.com/287034/208792901-6d923733-569d-464b-9da4-691b33955c75.png)\r\n\n", "before_files": [{"content": "from frozendict import frozendict\nfrom sqlalchemy import select\n\nfrom db.records.operations import select as records_select\nfrom db.columns.base import MathesarColumn\nfrom db.columns.operations.select import get_column_name_from_attnum\nfrom db.tables.operations.select import reflect_table_from_oid\nfrom db.transforms.operations.apply import apply_transformations\nfrom db.metadata import get_empty_metadata\n\n\nclass DBQuery:\n    def __init__(\n            self,\n            base_table_oid,\n            initial_columns,\n            engine,\n            transformations=None,\n            name=None,\n            # The same metadata will be used by all the methods within DBQuery\n            # So make sure to change the metadata in case the DBQuery methods are called\n            # after a mutation to the database object that could make the existing metadata invalid.\n            metadata=None\n    ):\n        self.base_table_oid = base_table_oid\n        for initial_col in initial_columns:\n            assert isinstance(initial_col, InitialColumn)\n        self.initial_columns = initial_columns\n        self.engine = engine\n        if transformations is None:\n            # Less states to consider if no transformations is just an empty sequence\n            transformations = tuple()\n        self.transformations = transformations\n        self.name = name\n        self.metadata = metadata if metadata else get_empty_metadata()\n\n    def get_input_aliases(self, ix_of_transform):\n        \"\"\"\n        Each transformation in a DBQuery has its own list of input aliases; this returns it.\n        \"\"\"\n        initial_aliases = self.initial_aliases\n        if ix_of_transform == 0:\n            return initial_aliases\n        input_aliases = initial_aliases\n        previous_transforms = self.transformations[:ix_of_transform]\n        for transform in previous_transforms:\n            output_aliases = transform.get_output_aliases(input_aliases)\n            input_aliases = output_aliases\n        return input_aliases\n\n    def get_initial_column_by_input_alias(self, ix_of_transform, input_alias):\n        \"\"\"\n        Retraces the chain of input aliases until it gets to an initial column.\n\n        Returns None if the alias does not originate from an initial column in a way that would\n        preserve a unique constraint. E.g. if it is generated by an aggregation.\n        \"\"\"\n        initial_col_alias = \\\n            self._get_initial_alias_by_input_alias(ix_of_transform, input_alias)\n        if initial_col_alias is None:\n            return None\n        initial_column = \\\n            self._get_initial_column_by_initial_column_alias(initial_col_alias)\n        return initial_column\n\n    def _get_initial_alias_by_input_alias(self, ix_of_transform, input_alias):\n        if ix_of_transform == 0:\n            return input_alias\n        transforms = self.transformations[:ix_of_transform]\n        initial_aliases = self.initial_aliases\n        input_aliases = initial_aliases\n        uc_mappings_for_each_transform = [\n            transform.get_unique_constraint_mappings(input_aliases)\n            for transform in transforms\n        ]\n        for uc_mappings in reversed(uc_mappings_for_each_transform):\n            for uc_mapping in uc_mappings:\n                if uc_mapping.output_alias == input_alias:\n                    input_alias = uc_mapping.input_alias\n                    if input_alias is None:\n                        return None\n                    break\n        initial_alias = input_alias\n        return initial_alias\n\n    def _get_initial_column_by_initial_column_alias(self, alias):\n        \"\"\"\n        Looks up an initial column by initial column alias; no recursive logic.\n        \"\"\"\n        for initial_column in self.initial_columns:\n            if initial_column.alias == alias:\n                return initial_column\n\n    @property\n    def initial_aliases(self):\n        return [\n            initial_column.alias\n            for initial_column\n            in self.initial_columns\n        ]\n\n    # mirrors a method in db.records.operations.select\n    def get_records(self, **kwargs):\n        # NOTE how through this method you can perform a second batch of\n        # transformations.  this reflects fact that we can form a query, and\n        # then apply temporary transforms on it, like how you can apply\n        # temporary transforms to a table when in a table view.\n        return records_select.get_records_with_default_order(\n            table=self.transformed_relation, engine=self.engine, **kwargs,\n        )\n\n    # mirrors a method in db.records.operations.select\n    def get_count(self, **kwargs):\n        return records_select.get_count(\n            table=self.transformed_relation, engine=self.engine, **kwargs,\n        )\n\n    # NOTE if too expensive, can be rewritten to parse DBQuery spec, instead of leveraging sqlalchemy\n    @property\n    def all_sa_columns_map(self):\n        \"\"\"\n        Expensive! use with care.\n        \"\"\"\n        initial_columns_map = {\n            col.name: MathesarColumn.from_column(col, engine=self.engine)\n            for col in self.initial_relation.columns\n        }\n        output_columns_map = {\n            col.name: col for col in self.sa_output_columns\n        }\n        transforms_columns_map = {} if self.transformations is None else {\n            col.name: MathesarColumn.from_column(col, engine=self.engine)\n            for i in range(len(self.transformations))\n            for col in DBQuery(\n                base_table_oid=self.base_table_oid,\n                initial_columns=self.initial_columns,\n                engine=self.engine,\n                transformations=self.transformations[:i],\n                name=f'{self.name}_{i}'\n            ).transformed_relation.columns\n        }\n        map_of_alias_to_sa_col = initial_columns_map | transforms_columns_map | output_columns_map\n        return map_of_alias_to_sa_col\n\n    @property\n    def sa_output_columns(self):\n        \"\"\"\n        Sequence of SQLAlchemy columns representing the output columns of the\n        relation described by this query.\n        \"\"\"\n        return tuple(\n            MathesarColumn.from_column(sa_col, engine=self.engine)\n            for sa_col\n            in self.transformed_relation.columns\n        )\n\n    @property\n    def transformed_relation(self):\n        \"\"\"\n        A query describes a relation. This property is the result of parsing a\n        query into a relation.\n        \"\"\"\n        transformations = self.transformations\n        if transformations:\n            transformed = apply_transformations(\n                self.initial_relation,\n                transformations,\n            )\n            return transformed\n        else:\n            return self.initial_relation\n\n    @property\n    def initial_relation(self):\n        metadata = self.metadata\n        base_table = reflect_table_from_oid(\n            self.base_table_oid, self.engine, metadata=metadata\n        )\n        from_clause = base_table\n        # We cache this to avoid copies of the same join path to a given table\n        jp_path_alias_map = {(): base_table}\n\n        def _get_table(oid):\n            \"\"\"\n            We use the function-scoped metadata so all involved tables are aware\n            of each other.\n            \"\"\"\n            return reflect_table_from_oid(oid, self.engine, metadata=metadata, keep_existing=True)\n\n        def _get_column_name(oid, attnum):\n            return get_column_name_from_attnum(oid, attnum, self.engine, metadata=metadata)\n\n        def _process_initial_column(col):\n            nonlocal from_clause\n            col_name = _get_column_name(col.reloid, col.attnum)\n            # Make the path hashable so it can be a dict key\n            jp_path = _guarantee_jp_path_tuples(col.jp_path)\n            right = base_table\n\n            for i, jp in enumerate(jp_path):\n                left = jp_path_alias_map[jp_path[:i]]\n                right = _get_table(jp[1][0]).alias()\n                jp_path_alias_map[jp_path[:i + 1]] = right\n                left_col_name = _get_column_name(jp[0][0], jp[0][1])\n                right_col_name = _get_column_name(jp[1][0], jp[1][1])\n                left_col = left.columns[left_col_name]\n                right_col = right.columns[right_col_name]\n                from_clause = from_clause.join(\n                    right, onclause=left_col == right_col, isouter=True,\n                )\n\n            return right.columns[col_name].label(col.alias)\n\n        stmt = select(\n            [_process_initial_column(col) for col in self.initial_columns]\n        ).select_from(from_clause)\n        return stmt.cte()\n\n    def get_input_alias_for_output_alias(self, output_alias):\n        return self.map_of_output_alias_to_input_alias.get(output_alias)\n\n    # TODO consider caching; not urgent, since redundant calls don't trigger IO, it seems\n    @property\n    def map_of_output_alias_to_input_alias(self):\n        m = dict()\n        transforms = self.transformations\n        if transforms:\n            for transform in transforms:\n                m = m | transform.map_of_output_alias_to_input_alias\n        return m\n\n\nclass InitialColumn:\n    def __init__(\n            self,\n            # TODO consider renaming to oid; reloid is not a term we use,\n            # even if it's what postgres uses; or use reloid more\n            reloid,\n            attnum,\n            alias,\n            jp_path=None,\n    ):\n        # alias mustn't be an empty string\n        assert isinstance(alias, str) and alias.strip() != \"\"\n        self.reloid = reloid\n        self.attnum = attnum\n        self.alias = alias\n        self.jp_path = _guarantee_jp_path_tuples(jp_path)\n\n    @property\n    def is_base_column(self):\n        \"\"\"\n        A base column is an initial column on a query's base table.\n        \"\"\"\n        return self.jp_path is None\n\n    def __eq__(self, other):\n        \"\"\"Instances are equal when attributes are equal.\"\"\"\n        if type(other) is type(self):\n            return self.__dict__ == other.__dict__\n        return False\n\n    def __hash__(self):\n        \"\"\"Hashes are equal when attributes are equal.\"\"\"\n        return hash(frozendict(self.__dict__))\n\n\ndef _guarantee_jp_path_tuples(jp_path):\n    \"\"\"\n    Makes sure that jp_path is made up of tuples or is an empty tuple.\n    \"\"\"\n    if jp_path is not None:\n        return tuple(\n            (\n                tuple(edge[0]),\n                tuple(edge[1]),\n            )\n            for edge\n            in jp_path\n        )\n    else:\n        return tuple()\n", "path": "db/queries/base.py"}], "after_files": [{"content": "from frozendict import frozendict\nfrom sqlalchemy import select\n\nfrom db.records.operations import select as records_select\nfrom db.columns.base import MathesarColumn\nfrom db.columns.operations.select import get_column_name_from_attnum\nfrom db.tables.operations.select import reflect_table_from_oid\nfrom db.transforms.operations.apply import apply_transformations\nfrom db.metadata import get_empty_metadata\n\n\nclass DBQuery:\n    def __init__(\n            self,\n            base_table_oid,\n            initial_columns,\n            engine,\n            transformations=None,\n            name=None,\n            # The same metadata will be used by all the methods within DBQuery\n            # So make sure to change the metadata in case the DBQuery methods are called\n            # after a mutation to the database object that could make the existing metadata invalid.\n            metadata=None\n    ):\n        self.base_table_oid = base_table_oid\n        for initial_col in initial_columns:\n            assert isinstance(initial_col, InitialColumn)\n        self.initial_columns = initial_columns\n        self.engine = engine\n        if transformations is None:\n            # Less states to consider if no transformations is just an empty sequence\n            transformations = tuple()\n        self.transformations = transformations\n        self.name = name\n        self.metadata = metadata if metadata else get_empty_metadata()\n\n    def get_input_aliases(self, ix_of_transform):\n        \"\"\"\n        Each transformation in a DBQuery has its own list of input aliases; this returns it.\n        \"\"\"\n        initial_aliases = self.initial_aliases\n        if ix_of_transform == 0:\n            return initial_aliases\n        input_aliases = initial_aliases\n        previous_transforms = self.transformations[:ix_of_transform]\n        for transform in previous_transforms:\n            output_aliases = transform.get_output_aliases(input_aliases)\n            input_aliases = output_aliases\n        return input_aliases\n\n    def get_initial_column_by_input_alias(self, ix_of_transform, input_alias):\n        \"\"\"\n        Retraces the chain of input aliases until it gets to an initial column.\n\n        Returns None if the alias does not originate from an initial column in a way that would\n        preserve a unique constraint. E.g. if it is generated by an aggregation.\n        \"\"\"\n        initial_col_alias = \\\n            self._get_initial_alias_by_input_alias(ix_of_transform, input_alias)\n        if initial_col_alias is None:\n            return None\n        initial_column = \\\n            self._get_initial_column_by_initial_column_alias(initial_col_alias)\n        return initial_column\n\n    def _get_initial_alias_by_input_alias(self, ix_of_transform, input_alias):\n        if ix_of_transform == 0:\n            return input_alias\n        transforms = self.transformations[:ix_of_transform]\n        initial_aliases = self.initial_aliases\n        input_aliases = initial_aliases\n        uc_mappings_for_each_transform = [\n            transform.get_unique_constraint_mappings(input_aliases)\n            for transform in transforms\n        ]\n        for uc_mappings in reversed(uc_mappings_for_each_transform):\n            for uc_mapping in uc_mappings:\n                if uc_mapping.output_alias == input_alias:\n                    input_alias = uc_mapping.input_alias\n                    if input_alias is None:\n                        return None\n                    break\n        initial_alias = input_alias\n        return initial_alias\n\n    def _get_initial_column_by_initial_column_alias(self, alias):\n        \"\"\"\n        Looks up an initial column by initial column alias; no recursive logic.\n        \"\"\"\n        for initial_column in self.initial_columns:\n            if initial_column.alias == alias:\n                return initial_column\n\n    @property\n    def initial_aliases(self):\n        return [\n            initial_column.alias\n            for initial_column\n            in self.initial_columns\n        ]\n\n    # mirrors a method in db.records.operations.select\n    def get_records(self, **kwargs):\n        # NOTE how through this method you can perform a second batch of\n        # transformations.  this reflects fact that we can form a query, and\n        # then apply temporary transforms on it, like how you can apply\n        # temporary transforms to a table when in a table view.\n        return records_select.get_records_with_default_order(\n            table=self.transformed_relation, engine=self.engine, **kwargs,\n        )\n\n    # mirrors a method in db.records.operations.select\n    def get_count(self, **kwargs):\n        return records_select.get_count(\n            table=self.transformed_relation, engine=self.engine, **kwargs,\n        )\n\n    # NOTE if too expensive, can be rewritten to parse DBQuery spec, instead of leveraging sqlalchemy\n    @property\n    def all_sa_columns_map(self):\n        \"\"\"\n        Expensive! use with care.\n        \"\"\"\n        initial_columns_map = {\n            col.name: MathesarColumn.from_column(col, engine=self.engine)\n            for col in self.initial_relation.columns\n        }\n        output_columns_map = {\n            col.name: col for col in self.sa_output_columns\n        }\n        transforms_columns_map = {} if self.transformations is None else {\n            col.name: MathesarColumn.from_column(col, engine=self.engine)\n            for i in range(len(self.transformations))\n            for col in DBQuery(\n                base_table_oid=self.base_table_oid,\n                initial_columns=self.initial_columns,\n                engine=self.engine,\n                transformations=self.transformations[:i],\n                name=f'{self.name}_{i}'\n            ).transformed_relation.columns\n        }\n        map_of_alias_to_sa_col = initial_columns_map | transforms_columns_map | output_columns_map\n        return map_of_alias_to_sa_col\n\n    @property\n    def sa_output_columns(self):\n        \"\"\"\n        Sequence of SQLAlchemy columns representing the output columns of the\n        relation described by this query.\n        \"\"\"\n        return tuple(\n            MathesarColumn.from_column(sa_col, engine=self.engine)\n            for sa_col\n            in self.transformed_relation.columns\n        )\n\n    @property\n    def transformed_relation(self):\n        \"\"\"\n        A query describes a relation. This property is the result of parsing a\n        query into a relation.\n        \"\"\"\n        transformations = self.transformations\n        if transformations:\n            transformed = apply_transformations(\n                self.initial_relation,\n                transformations,\n            )\n            return transformed\n        else:\n            return self.initial_relation\n\n    @property\n    def initial_relation(self):\n        metadata = self.metadata\n        base_table = reflect_table_from_oid(\n            self.base_table_oid, self.engine, metadata=metadata\n        )\n        from_clause = base_table\n        # We cache this to avoid copies of the same join path to a given table\n        jp_path_alias_map = {(): base_table}\n        jp_path_unique_set = set()\n\n        def _get_table(oid):\n            \"\"\"\n            We use the function-scoped metadata so all involved tables are aware\n            of each other.\n            \"\"\"\n            return reflect_table_from_oid(oid, self.engine, metadata=metadata, keep_existing=True)\n\n        def _get_column_name(oid, attnum):\n            return get_column_name_from_attnum(oid, attnum, self.engine, metadata=metadata)\n\n        def _process_initial_column(col):\n            nonlocal from_clause\n            col_name = _get_column_name(col.reloid, col.attnum)\n            # Make the path hashable so it can be a dict key\n            jp_path = _guarantee_jp_path_tuples(col.jp_path)\n            right = base_table\n\n            for i, jp in enumerate(jp_path):\n                left = jp_path_alias_map[jp_path[:i]]\n                right_table = jp_path[:i + 1]\n                if right_table in jp_path_alias_map:\n                    right = jp_path_alias_map[right_table]\n                else:\n                    right = _get_table(jp[1][0]).alias()\n                    jp_path_alias_map[jp_path[:i + 1]] = right\n                left_col_name = _get_column_name(jp[0][0], jp[0][1])\n                right_col_name = _get_column_name(jp[1][0], jp[1][1])\n                left_col = left.columns[left_col_name]\n                right_col = right.columns[right_col_name]\n                join_columns = f\"{left_col}, {right_col}\"\n                if join_columns not in jp_path_unique_set:\n                    jp_path_unique_set.add(join_columns)\n                    from_clause = from_clause.join(\n                        right, onclause=left_col == right_col, isouter=True,\n                    )\n\n            return right.columns[col_name].label(col.alias)\n\n        stmt = select(\n            [_process_initial_column(col) for col in self.initial_columns]\n        ).select_from(from_clause)\n        return stmt.cte()\n\n    def get_input_alias_for_output_alias(self, output_alias):\n        return self.map_of_output_alias_to_input_alias.get(output_alias)\n\n    # TODO consider caching; not urgent, since redundant calls don't trigger IO, it seems\n    @property\n    def map_of_output_alias_to_input_alias(self):\n        m = dict()\n        transforms = self.transformations\n        if transforms:\n            for transform in transforms:\n                m = m | transform.map_of_output_alias_to_input_alias\n        return m\n\n\nclass InitialColumn:\n    def __init__(\n            self,\n            # TODO consider renaming to oid; reloid is not a term we use,\n            # even if it's what postgres uses; or use reloid more\n            reloid,\n            attnum,\n            alias,\n            jp_path=None,\n    ):\n        # alias mustn't be an empty string\n        assert isinstance(alias, str) and alias.strip() != \"\"\n        self.reloid = reloid\n        self.attnum = attnum\n        self.alias = alias\n        self.jp_path = _guarantee_jp_path_tuples(jp_path)\n\n    @property\n    def is_base_column(self):\n        \"\"\"\n        A base column is an initial column on a query's base table.\n        \"\"\"\n        return self.jp_path is None\n\n    def __eq__(self, other):\n        \"\"\"Instances are equal when attributes are equal.\"\"\"\n        if type(other) is type(self):\n            return self.__dict__ == other.__dict__\n        return False\n\n    def __hash__(self):\n        \"\"\"Hashes are equal when attributes are equal.\"\"\"\n        return hash(frozendict(self.__dict__))\n\n\ndef _guarantee_jp_path_tuples(jp_path):\n    \"\"\"\n    Makes sure that jp_path is made up of tuples or is an empty tuple.\n    \"\"\"\n    if jp_path is not None:\n        return tuple(\n            (\n                tuple(edge[0]),\n                tuple(edge[1]),\n            )\n            for edge\n            in jp_path\n        )\n    else:\n        return tuple()\n", "path": "db/queries/base.py"}]}