class Delete(Query):
__slots__ = ("force", "where_delegate")
def __init__(self, table: t.Type[Table], force: bool = False, **kwargs):
super().__init__(table, **kwargs)
self.force = force
self.where_delegate = WhereDelegate()
def where(self, *where: Combinable) -> Delete:
self.where_delegate.where(*where)
return self
def _validate(self):
"""
Don't let a deletion happen unless it has a where clause, or is
explicitly forced.
"""
if (not self.where_delegate._where) and (not self.force):
classname = self.table.__name__
raise DeletionError(
"Do you really want to delete all the data from "
f"{classname}? If so, use {classname}.delete(force=True). "
"Otherwise, add a where clause.")
@property
def default_querystrings(self) -> t.Sequence[QueryString]:
query = f"DELETE FROM {self.table._meta.tablename}"
if self.where_delegate._where:
query += " WHERE {}"
return [QueryString(query, self.where_delegate._where.querystring)]
else:
return [QueryString(query)]
def __init__(self, table: t.Type[Table]):
super().__init__(table)
self.limit_delegate = LimitDelegate()
self.offset_delegate = OffsetDelegate()
self.order_by_delegate = OrderByDelegate()
self.output_delegate = OutputDelegate()
self.output_delegate._output.as_objects = True
self.where_delegate = WhereDelegate()
class Update(Query):
__slots__ = ("force", "values_delegate", "where_delegate")
def __init__(self, table: t.Type[Table], force: bool = False, **kwargs):
super().__init__(table, **kwargs)
self.force = force
self.values_delegate = ValuesDelegate(table=table)
self.where_delegate = WhereDelegate()
def values(self,
values: t.Dict[t.Union[Column, str], t.Any] = {},
**kwargs) -> Update:
values = dict(values, **kwargs)
self.values_delegate.values(values)
return self
def where(self, *where: Combinable) -> Update:
self.where_delegate.where(*where)
return self
def _validate(self):
if len(self.values_delegate._values) == 0:
raise ValueError("No values were specified to update.")
for column, _ in self.values_delegate._values.items():
if len(column._meta.call_chain) > 0:
raise ValueError(
"Related values can't be updated via an update.")
if (not self.where_delegate._where) and (not self.force):
classname = self.table.__name__
raise UpdateError(
"Do you really want to update all rows in "
f"{classname}? If so, use pass `force=True` into "
f"`{classname}.update`. Otherwise, add a where clause.")
@property
def default_querystrings(self) -> t.Sequence[QueryString]:
columns_str = ", ".join(
f'"{col._meta.db_column_name}" = {{}}'
for col, _ in self.values_delegate._values.items())
query = f"UPDATE {self.table._meta.tablename} SET " + columns_str
querystring = QueryString(query,
*self.values_delegate.get_sql_values())
if not self.where_delegate._where:
return [querystring]
where_querystring = QueryString(
"{} WHERE {}",
querystring,
self.where_delegate._where.querystring,
)
return [where_querystring]
class Update(Query):
__slots__ = ("values_delegate", "where_delegate")
def __init__(self, table: t.Type[Table]):
super().__init__(table)
self.values_delegate = ValuesDelegate()
self.where_delegate = WhereDelegate()
def values(self, values: t.Dict[Column, t.Any]) -> Update:
self.values_delegate.values(values)
return self
def where(self, where: Combinable) -> Update:
self.where_delegate.where(where)
return self
def validate(self):
if len(self.values_delegate._values) == 0:
raise ValueError(
"No values were specified to update - please use .values"
)
for column, value in self.values_delegate._values.items():
if len(column._meta.call_chain) > 0:
raise ValueError(
"Related values can't be updated via an update"
)
@property
def default_querystrings(self) -> t.Sequence[QueryString]:
self.validate()
columns_str = ", ".join(
[
f"{col._meta.name} = {{}}"
for col, _ in self.values_delegate._values.items()
]
)
query = f"UPDATE {self.table._meta.tablename} SET " + columns_str
querystring = QueryString(
query, *self.values_delegate._values.values()
)
if self.where_delegate._where:
where_querystring = QueryString(
"{} WHERE {}",
querystring,
self.where_delegate._where.querystring,
)
return [where_querystring]
else:
return [querystring]
def __init__(
self,
table: t.Type[Table],
prefetch: t.Sequence[t.Union[ForeignKey, t.List[ForeignKey]]] = (),
**kwargs,
):
super().__init__(table, **kwargs)
self.limit_delegate = LimitDelegate()
self.offset_delegate = OffsetDelegate()
self.order_by_delegate = OrderByDelegate()
self.output_delegate = OutputDelegate()
self.output_delegate._output.as_objects = True
self.prefetch_delegate = PrefetchDelegate()
self.prefetch(*prefetch)
self.where_delegate = WhereDelegate()
def __init__(
self,
table: t.Type[Table],
columns_list: t.Sequence[t.Union[Selectable, str]] = [],
exclude_secrets: bool = False,
):
super().__init__(table)
self.exclude_secrets = exclude_secrets
self.columns_delegate = ColumnsDelegate()
self.distinct_delegate = DistinctDelegate()
self.group_by_delegate = GroupByDelegate()
self.limit_delegate = LimitDelegate()
self.offset_delegate = OffsetDelegate()
self.order_by_delegate = OrderByDelegate()
self.output_delegate = OutputDelegate()
self.where_delegate = WhereDelegate()
self.columns(*columns_list)
class Exists(Query):
__slots__ = ("where_delegate", )
def __init__(self, table: t.Type[Table]):
super().__init__(table)
self.where_delegate = WhereDelegate()
def where(self, where: Combinable) -> Exists:
self.where_delegate.where(where)
return self
async def response_handler(self, response) -> bool:
# Convert to a bool - postgres returns True, and sqlite return 1.
return bool(response[0]["exists"])
@property
def querystrings(self) -> t.Sequence[QueryString]:
select = Select(table=self.table)
select.where_delegate._where = self.where_delegate._where
return [
QueryString('SELECT EXISTS({}) AS "exists"',
select.querystrings[0])
]
class Count(Query):
__slots__ = ("where_delegate", )
def __init__(self, table: t.Type[Table]):
super().__init__(table)
self.where_delegate = WhereDelegate()
def where(self, where: Combinable) -> Count:
self.where_delegate.where(where)
return self
async def response_handler(self, response) -> bool:
return response[0]["count"]
@property
def querystrings(self) -> t.Sequence[QueryString]:
select = Select(self.table)
select.where_delegate._where = self.where_delegate._where
return [
QueryString(
'SELECT COUNT(*) AS "count" FROM ({}) AS "subquery"',
select.querystrings[0],
)
]
class Select(Query):
__slots__ = (
"columns_list",
"exclude_secrets",
"columns_delegate",
"distinct_delegate",
"group_by_delegate",
"limit_delegate",
"offset_delegate",
"order_by_delegate",
"output_delegate",
"where_delegate",
)
def __init__(
self,
table: t.Type[Table],
columns_list: t.Sequence[t.Union[Selectable, str]] = [],
exclude_secrets: bool = False,
):
super().__init__(table)
self.exclude_secrets = exclude_secrets
self.columns_delegate = ColumnsDelegate()
self.distinct_delegate = DistinctDelegate()
self.group_by_delegate = GroupByDelegate()
self.limit_delegate = LimitDelegate()
self.offset_delegate = OffsetDelegate()
self.order_by_delegate = OrderByDelegate()
self.output_delegate = OutputDelegate()
self.where_delegate = WhereDelegate()
self.columns(*columns_list)
def columns(self, *columns: t.Union[Selectable, str]) -> Select:
_columns = self.table._process_column_args(*columns)
self.columns_delegate.columns(*_columns)
return self
def distinct(self) -> Select:
self.distinct_delegate.distinct()
return self
def group_by(self, *columns: Column) -> Select:
_columns: t.List[Column] = [
i for i in self.table._process_column_args(*columns)
if isinstance(i, Column)
]
self.group_by_delegate.group_by(*_columns)
return self
def limit(self, number: int) -> Select:
self.limit_delegate.limit(number)
return self
def first(self) -> Select:
self.limit_delegate.first()
return self
def offset(self, number: int) -> Select:
self.offset_delegate.offset(number)
return self
async def response_handler(self, response):
if self.limit_delegate._first:
if len(response) == 0:
return None
else:
return response[0]
else:
return response
def order_by(self, *columns: Column, ascending=True) -> Select:
_columns: t.List[Column] = [
i for i in self.table._process_column_args(*columns)
if isinstance(i, Column)
]
self.order_by_delegate.order_by(*_columns, ascending=ascending)
return self
def output(self, as_list: bool = False, as_json: bool = False) -> Select:
self.output_delegate.output(as_list=as_list, as_json=as_json)
return self
def where(self, where: Combinable) -> Select:
self.where_delegate.where(where)
return self
async def batch(self,
batch_size: t.Optional[int] = None,
**kwargs) -> Batch:
if batch_size:
kwargs.update(batch_size=batch_size)
return await self.table._meta.db.batch(self, **kwargs)
###########################################################################
def _get_joins(self, columns: t.Sequence[Selectable]) -> t.List[str]:
"""
A call chain is a sequence of foreign keys representing joins which
need to be made to retrieve a column in another table.
"""
joins: t.List[str] = []
readables: t.List[Readable] = [
i for i in columns if isinstance(i, Readable)
]
columns = list(columns)
for readable in readables:
columns += readable.columns
for column in columns:
if not isinstance(column, Column):
continue
_joins: t.List[str] = []
for index, key in enumerate(column._meta.call_chain, 0):
table_alias = "$".join([
f"{_key._meta.table._meta.tablename}${_key._meta.name}"
for _key in column._meta.call_chain[:index + 1]
])
key._meta.table_alias = table_alias
if index > 0:
left_tablename = column._meta.call_chain[
index - 1]._meta.table_alias
else:
left_tablename = key._meta.table._meta.tablename
right_tablename = (
key._foreign_key_meta.resolved_references._meta.tablename)
_joins.append(
f"LEFT JOIN {right_tablename} {table_alias}"
" ON "
f"({left_tablename}.{key._meta.name} = {table_alias}.id)")
joins.extend(_joins)
# Remove duplicates
return list(OrderedDict.fromkeys(joins))
def _check_valid_call_chain(self, keys: t.Sequence[Selectable]) -> bool:
for column in keys:
if not isinstance(column, Column):
continue
if column._meta.call_chain:
# Make sure the call_chain isn't too large to discourage
# very inefficient queries.
if len(column._meta.call_chain) > 10:
raise Exception(
"Joining more than 10 tables isn't supported - "
"please restructure your query.")
return True
@property
def querystrings(self) -> t.Sequence[QueryString]:
# JOIN
self._check_valid_call_chain(self.columns_delegate.selected_columns)
select_joins = self._get_joins(self.columns_delegate.selected_columns)
where_joins = self._get_joins(self.where_delegate.get_where_columns())
order_by_joins = self._get_joins(
self.order_by_delegate.get_order_by_columns())
# Combine all joins, and remove duplicates
joins: t.List[str] = list(
OrderedDict.fromkeys(select_joins + where_joins + order_by_joins))
#######################################################################
# If no columns have been specified for selection, select all columns
# on the table:
if len(self.columns_delegate.selected_columns) == 0:
self.columns_delegate.selected_columns = self.table._meta.columns
# If secret fields need to be omitted, remove them from the list.
if self.exclude_secrets:
self.columns_delegate.remove_secret_columns()
engine_type = self.table._meta.db.engine_type
select_strings: t.List[str] = [
c.get_select_string(engine_type=engine_type)
for c in self.columns_delegate.selected_columns
]
columns_str = ", ".join(select_strings)
#######################################################################
select = ("SELECT DISTINCT"
if self.distinct_delegate._distinct else "SELECT")
query = f"{select} {columns_str} FROM {self.table._meta.tablename}"
for join in joins:
query += f" {join}"
#######################################################################
args: t.List[t.Any] = []
if self.where_delegate._where:
query += " WHERE {}"
args.append(self.where_delegate._where.querystring)
if self.group_by_delegate._group_by:
query += " {}"
args.append(self.group_by_delegate._group_by.querystring)
if self.order_by_delegate._order_by:
query += " {}"
args.append(self.order_by_delegate._order_by.querystring)
if (engine_type == "sqlite" and self.offset_delegate._offset
and not self.limit_delegate._limit):
raise ValueError(
"A limit clause must be provided when doing an offset with "
"SQLite.")
if self.limit_delegate._limit:
query += " {}"
args.append(self.limit_delegate._limit.querystring)
if self.offset_delegate._offset:
query += " {}"
args.append(self.offset_delegate._offset.querystring)
querystring = QueryString(query, *args)
return [querystring]
def __init__(self, table: t.Type[Table]):
super().__init__(table)
self.where_delegate = WhereDelegate()
class Objects(Query):
"""
Almost identical to select, except you have to select all fields, and
table instances are returned, rather than just data.
"""
__slots__ = (
"nested",
"limit_delegate",
"offset_delegate",
"order_by_delegate",
"output_delegate",
"prefetch_delegate",
"where_delegate",
)
def __init__(
self,
table: t.Type[Table],
prefetch: t.Sequence[t.Union[ForeignKey, t.List[ForeignKey]]] = (),
**kwargs,
):
super().__init__(table, **kwargs)
self.limit_delegate = LimitDelegate()
self.offset_delegate = OffsetDelegate()
self.order_by_delegate = OrderByDelegate()
self.output_delegate = OutputDelegate()
self.output_delegate._output.as_objects = True
self.prefetch_delegate = PrefetchDelegate()
self.prefetch(*prefetch)
self.where_delegate = WhereDelegate()
def output(self, load_json: bool = False) -> Objects:
self.output_delegate.output(
as_list=False, as_json=False, load_json=load_json
)
return self
def limit(self, number: int) -> Objects:
self.limit_delegate.limit(number)
return self
def first(self) -> Objects:
self.limit_delegate.first()
return self
def prefetch(
self, *fk_columns: t.Union[ForeignKey, t.List[ForeignKey]]
) -> Objects:
self.prefetch_delegate.prefetch(*fk_columns)
return self
def get(self, where: Combinable) -> Objects:
self.where_delegate.where(where)
self.limit_delegate.first()
return self
def offset(self, number: int) -> Objects:
self.offset_delegate.offset(number)
return self
def get_or_create(
self,
where: Combinable,
defaults: t.Dict[t.Union[Column, str], t.Any] = {},
):
return GetOrCreate(query=self, where=where, defaults=defaults)
def create(self, **columns: t.Any):
return Create(query=self, columns=columns)
def order_by(self, *columns: Column, ascending=True) -> Objects:
self.order_by_delegate.order_by(*columns, ascending=ascending)
return self
def where(self, *where: Combinable) -> Objects:
self.where_delegate.where(*where)
return self
async def batch(
self, batch_size: t.Optional[int] = None, **kwargs
) -> Batch:
if batch_size:
kwargs.update(batch_size=batch_size)
return await self.table._meta.db.batch(self, **kwargs)
async def response_handler(self, response):
if self.limit_delegate._first:
if len(response) == 0:
return None
if self.output_delegate._output.nested:
return make_nested(response[0])
else:
return response[0]
elif self.output_delegate._output.nested:
return [make_nested(i) for i in response]
else:
return response
@property
def default_querystrings(self) -> t.Sequence[QueryString]:
select = Select(table=self.table)
for attr in (
"limit_delegate",
"where_delegate",
"offset_delegate",
"output_delegate",
"order_by_delegate",
):
setattr(select, attr, getattr(self, attr))
if self.prefetch_delegate.fk_columns:
select.columns(*self.table.all_columns())
for fk in self.prefetch_delegate.fk_columns:
if isinstance(fk, ForeignKey):
select.columns(*fk.all_columns())
else:
raise ValueError(f"{fk} doesn't seem to be a ForeignKey.")
# Make sure that all intermediate objects are fully loaded.
for parent_fk in fk._meta.call_chain:
select.columns(*parent_fk.all_columns())
select.output_delegate.output(nested=True)
return select.querystrings
def __init__(self, table: t.Type[Table], force: bool = False, **kwargs):
super().__init__(table, **kwargs)
self.force = force
self.where_delegate = WhereDelegate()
class Objects(Query):
"""
Almost identical to select, except you have to select all fields, and
table instances are returned, rather than just data.
"""
__slots__ = (
"limit_delegate",
"offset_delegate",
"order_by_delegate",
"output_delegate",
"where_delegate",
)
def __init__(self, table: t.Type[Table]):
super().__init__(table)
self.limit_delegate = LimitDelegate()
self.offset_delegate = OffsetDelegate()
self.order_by_delegate = OrderByDelegate()
self.output_delegate = OutputDelegate()
self.output_delegate._output.as_objects = True
self.where_delegate = WhereDelegate()
def limit(self, number: int) -> Objects:
self.limit_delegate.limit(number)
return self
def first(self) -> Objects:
self.limit_delegate.first()
return self
def offset(self, number: int) -> Objects:
self.offset_delegate.offset(number)
return self
def order_by(self, *columns: Column, ascending=True) -> Objects:
self.order_by_delegate.order_by(*columns, ascending=ascending)
return self
def where(self, *where: Combinable, **kwargs):
for i in where:
self.where_delegate.where(i)
for key, value in kwargs.items():
column = self._meta.get_column_by_name(key)
self.where_delegate.where(Where(column=column, value=value, operator=Equal))
return self
async def batch(self, batch_size: t.Optional[int] = None, **kwargs) -> Batch:
if batch_size:
kwargs.update(batch_size=batch_size)
return await self.table._meta.db.batch(self, **kwargs)
async def response_handler(self, response):
if self.limit_delegate._first:
if len(response) == 0:
return None
else:
return response[0]
else:
return response
@property
def querystrings(self) -> t.Sequence[QueryString]:
select = Select(table=self.table)
for attr in (
"limit_delegate",
"where_delegate",
"offset_delegate",
"output_delegate",
"order_by_delegate",
):
setattr(select, attr, getattr(self, attr))
return select.querystrings
class Select(Query):
__slots__ = (
"columns_list",
"exclude_secrets",
"columns_delegate",
"distinct_delegate",
"group_by_delegate",
"limit_delegate",
"offset_delegate",
"order_by_delegate",
"output_delegate",
"where_delegate",
)
def __init__(
self,
table: t.Type[Table],
columns_list: t.Sequence[t.Union[Selectable, str]] = [],
exclude_secrets: bool = False,
**kwargs,
):
super().__init__(table, **kwargs)
self.exclude_secrets = exclude_secrets
self.columns_delegate = ColumnsDelegate()
self.distinct_delegate = DistinctDelegate()
self.group_by_delegate = GroupByDelegate()
self.limit_delegate = LimitDelegate()
self.offset_delegate = OffsetDelegate()
self.order_by_delegate = OrderByDelegate()
self.output_delegate = OutputDelegate()
self.where_delegate = WhereDelegate()
self.columns(*columns_list)
def columns(self, *columns: t.Union[Selectable, str]) -> Select:
_columns = self.table._process_column_args(*columns)
self.columns_delegate.columns(*_columns)
return self
def distinct(self) -> Select:
self.distinct_delegate.distinct()
return self
def group_by(self, *columns: Column) -> Select:
_columns: t.List[Column] = [
i for i in self.table._process_column_args(*columns)
if isinstance(i, Column)
]
self.group_by_delegate.group_by(*_columns)
return self
def limit(self, number: int) -> Select:
self.limit_delegate.limit(number)
return self
def first(self) -> Select:
self.limit_delegate.first()
return self
def offset(self, number: int) -> Select:
self.offset_delegate.offset(number)
return self
async def _splice_m2m_rows(
self,
response: t.List[t.Dict[str, t.Any]],
secondary_table: t.Type[Table],
secondary_table_pk: PrimaryKey,
m2m_name: str,
m2m_select: M2MSelect,
as_list: bool = False,
):
row_ids = list(
{i
for i in itertools.chain(*[row[m2m_name] for row in response])})
extra_rows = ((await secondary_table.select(
*m2m_select.columns,
secondary_table_pk.as_alias("mapping_key"),
).where(secondary_table_pk.is_in(row_ids)).output(
load_json=m2m_select.load_json).run()) if row_ids else [])
if as_list:
column_name = m2m_select.columns[0]._meta.name
extra_rows_map = {
row["mapping_key"]: row[column_name]
for row in extra_rows
}
else:
extra_rows_map = {
row["mapping_key"]: {
key: value
for key, value in row.items() if key != "mapping_key"
}
for row in extra_rows
}
for row in response:
row[m2m_name] = [extra_rows_map.get(i) for i in row[m2m_name]]
return response
async def response_handler(self, response):
m2m_selects = [
i for i in self.columns_delegate.selected_columns
if isinstance(i, M2MSelect)
]
for m2m_select in m2m_selects:
m2m_name = m2m_select.m2m._meta.name
secondary_table = m2m_select.m2m._meta.secondary_table
secondary_table_pk = secondary_table._meta.primary_key
if self.engine_type == "sqlite":
# With M2M queries in SQLite, we always get the value back as a
# list of strings, so we need to do some type conversion.
value_type = (m2m_select.columns[0].__class__.value_type
if m2m_select.as_list
and m2m_select.serialisation_safe else
secondary_table_pk.value_type)
try:
for row in response:
data = row[m2m_name]
row[m2m_name] = (
[value_type(i)
for i in row[m2m_name]] if data else [])
except ValueError:
colored_warning("Unable to do type conversion for the "
f"{m2m_name} relation")
# If the user requested a single column, we just return that
# from the database. Otherwise we request the primary key
# value, so we can fetch the rest of the data in a subsequent
# SQL query - see below.
if m2m_select.as_list:
if m2m_select.serialisation_safe:
pass
else:
response = await self._splice_m2m_rows(
response,
secondary_table,
secondary_table_pk,
m2m_name,
m2m_select,
as_list=True,
)
else:
if (len(m2m_select.columns) == 1
and m2m_select.serialisation_safe):
column_name = m2m_select.columns[0]._meta.name
for row in response:
row[m2m_name] = [{
column_name: i
} for i in row[m2m_name]]
else:
response = await self._splice_m2m_rows(
response,
secondary_table,
secondary_table_pk,
m2m_name,
m2m_select,
)
elif self.engine_type == "postgres":
if m2m_select.as_list:
# We get the data back as an array, and can just return it
# unless it's JSON.
if (type(m2m_select.columns[0]) in (JSON, JSONB)
and m2m_select.load_json):
for row in response:
data = row[m2m_name]
row[m2m_name] = [load_json(i) for i in data]
elif m2m_select.serialisation_safe:
# If the columns requested can be safely serialised, they
# are returned as a JSON string, so we need to deserialise
# it.
for row in response:
data = row[m2m_name]
row[m2m_name] = load_json(data) if data else []
else:
# If the data can't be safely serialised as JSON, we get
# back an array of primary key values, and need to
# splice in the correct values using Python.
response = await self._splice_m2m_rows(
response,
secondary_table,
secondary_table_pk,
m2m_name,
m2m_select,
)
#######################################################################
# If no columns were specified, it's a select *, so we know that
# no columns were selected from related tables.
was_select_star = len(self.columns_delegate.selected_columns) == 0
if self.limit_delegate._first:
if len(response) == 0:
return None
if self.output_delegate._output.nested and not was_select_star:
return make_nested(response[0])
else:
return response[0]
elif self.output_delegate._output.nested and not was_select_star:
return [make_nested(i) for i in response]
else:
return response
def order_by(self, *columns: Column, ascending=True) -> Select:
_columns: t.List[Column] = [
i for i in self.table._process_column_args(*columns)
if isinstance(i, Column)
]
self.order_by_delegate.order_by(*_columns, ascending=ascending)
return self
def output(
self,
as_list: bool = False,
as_json: bool = False,
load_json: bool = False,
nested: bool = False,
) -> Select:
self.output_delegate.output(
as_list=as_list,
as_json=as_json,
load_json=load_json,
nested=nested,
)
return self
def where(self, *where: Combinable) -> Select:
self.where_delegate.where(*where)
return self
async def batch(self,
batch_size: t.Optional[int] = None,
**kwargs) -> Batch:
if batch_size:
kwargs.update(batch_size=batch_size)
return await self.table._meta.db.batch(self, **kwargs)
###########################################################################
def _get_joins(self, columns: t.Sequence[Selectable]) -> t.List[str]:
"""
A call chain is a sequence of foreign keys representing joins which
need to be made to retrieve a column in another table.
"""
joins: t.List[str] = []
readables: t.List[Readable] = [
i for i in columns if isinstance(i, Readable)
]
columns = list(columns)
for readable in readables:
columns += readable.columns
for column in columns:
if not isinstance(column, Column):
continue
_joins: t.List[str] = []
for index, key in enumerate(column._meta.call_chain, 0):
table_alias = "$".join(
f"{_key._meta.table._meta.tablename}${_key._meta.name}"
for _key in column._meta.call_chain[:index + 1])
key._meta.table_alias = table_alias
if index > 0:
left_tablename = column._meta.call_chain[
index - 1]._meta.table_alias
else:
left_tablename = key._meta.table._meta.tablename
right_tablename = (
key._foreign_key_meta.resolved_references._meta.tablename)
pk_name = column._meta.call_chain[
index]._foreign_key_meta.resolved_target_column._meta.name
_joins.append(
f"LEFT JOIN {right_tablename} {table_alias}"
" ON "
f"({left_tablename}.{key._meta.name} = {table_alias}.{pk_name})" # noqa: E501
)
joins.extend(_joins)
# Remove duplicates
return list(OrderedDict.fromkeys(joins))
def _check_valid_call_chain(self, keys: t.Sequence[Selectable]) -> bool:
for column in keys:
if not isinstance(column, Column):
continue
if column._meta.call_chain and len(column._meta.call_chain) > 10:
# Make sure the call_chain isn't too large to discourage
# very inefficient queries.
raise Exception(
"Joining more than 10 tables isn't supported - "
"please restructure your query.")
return True
@property
def default_querystrings(self) -> t.Sequence[QueryString]:
# JOIN
self._check_valid_call_chain(self.columns_delegate.selected_columns)
select_joins = self._get_joins(self.columns_delegate.selected_columns)
where_joins = self._get_joins(self.where_delegate.get_where_columns())
order_by_joins = self._get_joins(
self.order_by_delegate.get_order_by_columns())
# Combine all joins, and remove duplicates
joins: t.List[str] = list(
OrderedDict.fromkeys(select_joins + where_joins + order_by_joins))
#######################################################################
# If no columns have been specified for selection, select all columns
# on the table:
if len(self.columns_delegate.selected_columns) == 0:
self.columns_delegate.selected_columns = self.table._meta.columns
# If secret fields need to be omitted, remove them from the list.
if self.exclude_secrets:
self.columns_delegate.remove_secret_columns()
engine_type = self.table._meta.db.engine_type
select_strings: t.List[str] = [
c.get_select_string(engine_type=engine_type)
for c in self.columns_delegate.selected_columns
]
columns_str = ", ".join(select_strings)
#######################################################################
select = ("SELECT DISTINCT"
if self.distinct_delegate._distinct else "SELECT")
query = f"{select} {columns_str} FROM {self.table._meta.tablename}"
for join in joins:
query += f" {join}"
#######################################################################
args: t.List[t.Any] = []
if self.where_delegate._where:
query += " WHERE {}"
args.append(self.where_delegate._where.querystring)
if self.group_by_delegate._group_by:
query += " {}"
args.append(self.group_by_delegate._group_by.querystring)
if self.order_by_delegate._order_by:
query += " {}"
args.append(self.order_by_delegate._order_by.querystring)
if (engine_type == "sqlite" and self.offset_delegate._offset
and not self.limit_delegate._limit):
raise ValueError(
"A limit clause must be provided when doing an offset with "
"SQLite.")
if self.limit_delegate._limit:
query += " {}"
args.append(self.limit_delegate._limit.querystring)
if self.offset_delegate._offset:
query += " {}"
args.append(self.offset_delegate._offset.querystring)
querystring = QueryString(query, *args)
return [querystring]
def __init__(self, table: t.Type[Table], **kwargs):
super().__init__(table, **kwargs)
self.where_delegate = WhereDelegate()
