def generate_mysql_group_wise_query(
*, table_name: str,
columns_names: List[str],
target_column_name: str,
filters: Optional[Tuple[str, Any]] = None,
groupings: List[str],
limit: Optional[int] = None,
offset: Optional[int] = None,
orderings: Optional[List[OrderingType]] = None,
is_maximum: bool) -> str:
# based on article
# http://mysql.rjweb.org/doc.php/groupwise_max
columns = join_str(columns_names)
groupings_str = join_str(groupings)
operator = 'MAX' if is_maximum else 'MIN'
query = (f'SELECT {groupings_str}, '
f'{operator}({target_column_name}) AS {target_column_name} '
f'FROM {table_name} ')
query = add_filters(query,
filters=filters)
query = add_pagination(query,
limit=limit,
offset=offset)
query = add_groupings(query,
groupings=groupings)
query = (f'SELECT {columns} '
f'FROM {table_name} '
f'JOIN ({query}) as subquery '
f'USING ({groupings_str}, {target_column_name}) ')
query = add_orderings(query,
orderings=orderings)
return query
def generate_postgres_group_wise_query(
*, table_name: str,
columns_names: List[str],
target_column_name: str,
filters: Optional[Tuple[str, Any]] = None,
groupings: List[str],
limit: Optional[int] = None,
offset: Optional[int] = None,
orderings: Optional[List[OrderingType]] = None,
is_maximum: bool) -> str:
# based on article
# https://explainextended.com/2009/11/26/postgresql-selecting-records-holding-group-wise-maximum/
columns = join_str(columns_names)
group_wise_orderings = generate_group_wise_orderings(
groupings=groupings,
target_column_name=target_column_name,
is_maximum=is_maximum)
groupings_str = join_str(groupings)
query = (f'SELECT DISTINCT ON ({groupings_str}) {ALL_COLUMNS_ALIAS} '
f'FROM {table_name} ')
query = add_filters(query,
filters=filters)
query = add_orderings(query,
orderings=group_wise_orderings)
query = (f'SELECT {columns} '
f'FROM ({query}) AS subquery ')
query = add_orderings(query,
orderings=orderings)
query = add_pagination(query,
limit=limit,
offset=offset)
return query
def generate_update_query(*,
table_name: str,
updates: UpdatesType,
filters: Optional[FiltersType] = None) -> str:
query = f'UPDATE {table_name} '
query = add_updates(query, updates=updates)
query = add_filters(query, filters=filters)
return query
def generate_select_query(
*, table_name: str,
columns_names: List[str],
filters: Optional[FiltersType] = None,
orderings: Optional[List[OrderingType]] = None,
groupings: List[str] = None,
limit: Optional[int] = None,
offset: Optional[int] = None) -> str:
check_query_parameters(columns_names=columns_names)
columns = join_str(columns_names)
query = (f'SELECT {columns} '
f'FROM {table_name} ')
query = add_filters(query,
filters=filters)
query = add_orderings(query,
orderings=orderings)
query = add_groupings(query,
groupings=groupings)
query = add_pagination(query,
limit=limit,
offset=offset)
return query
def generate_delete_query(*, table_name: str,
filters: Optional[FiltersType]) -> str:
query = f'DELETE FROM {table_name} '
query = add_filters(query, filters=filters)
return query