def get_sql(self, with_alias=False, subquery=False, **kwargs):
union_template = ' UNION{type} {union}'
kwargs = {'quote_char': self.base_query.quote_char, 'dialect': self.base_query.dialect}
base_querystring = self.base_query.get_sql(subquery=self.base_query.wrap_union_queries, **kwargs)
querystring = base_querystring
for union_type, union_query in self._unions:
union_querystring = union_query.get_sql(subquery=self.base_query.wrap_union_queries, **kwargs)
if len(self.base_query._selects) != len(union_query._selects):
raise UnionException("Queries must have an equal number of select statements in a union."
"\n\nMain Query:\n{query1}\n\nUnion Query:\n{query2}"
.format(query1=base_querystring, query2=union_querystring))
querystring += union_template.format(type=union_type.value,
union=union_querystring)
if self._orderbys:
querystring += self._orderby_sql(**kwargs)
if self._limit:
querystring += self._limit_sql()
if self._offset:
querystring += self._offset_sql()
if subquery:
querystring = '({query})'.format(query=querystring)
if with_alias:
return alias_sql(querystring, self.alias or self._table_name, kwargs.get('quote_char'))
return querystring
def _union_sql(self, querystring):
unionstring = ''
if self._unions:
for (union_type, other) in self._unions:
if len(self._selects) != len(other._selects):
raise UnionException("Queries must have an equal number of select statements in a union."
"\n\nMain Query:\n{query1}"
"\n\nUnion Query:\n{query2}".format(query1=querystring,
query2=other.get_sql()))
unionstring += ' UNION{type} {query}'.format(
type=union_type.value,
query=other.get_sql()
)
return unionstring
def get_sql(self, with_alias=False, subquery=False, **kwargs):
union_template = " UNION{type} {union}"
kwargs.setdefault("dialect", self.base_query.dialect)
# This initializes the quote char based on the base query, which could be a dialect specific query class
# This might be overridden if quote_char is set explicitly in kwargs
kwargs.setdefault("quote_char", self.base_query.QUOTE_CHAR)
base_querystring = self.base_query.get_sql(
subquery=self.base_query.wrap_union_queries, **kwargs
)
querystring = base_querystring
for union_type, union_query in self._unions:
union_querystring = union_query.get_sql(
subquery=self.base_query.wrap_union_queries, **kwargs
)
if len(self.base_query._selects) != len(union_query._selects):
raise UnionException(
"Queries must have an equal number of select statements in a union."
"\n\nMain Query:\n{query1}\n\nUnion Query:\n{query2}".format(
query1=base_querystring, query2=union_querystring
)
)
querystring += union_template.format(
type=union_type.value, union=union_querystring
)
if self._orderbys:
querystring += self._orderby_sql(**kwargs)
if self._limit:
querystring += self._limit_sql()
if self._offset:
querystring += self._offset_sql()
if subquery:
querystring = "({query})".format(query=querystring, **kwargs)
if with_alias:
return format_alias_sql(
querystring, self.alias or self._table_name, **kwargs
)
return querystring
def _union_sql(self, querystring, quote_char=None, **kwargs):
if self._unions:
# Some queries require brackets for unions so easier to just always use them
querystring = "({})".format(querystring)
for (union_type, other) in self._unions:
other_querystring = other.get_sql(quote_char=quote_char)
if len(self._selects) != len(other._selects):
raise UnionException("Queries must have an equal number of select statements in a union."
"\n\nMain Query:\n{query1}\n\nUnion Query:\n{query2}" \
.format(query1=querystring, query2=other_querystring))
querystring += ' UNION{type} ({query})' \
.format(type=union_type.value, query=other_querystring)
return querystring
def _union_sql(self, querystring, quote_char=None, **kwargs):
if not self._unions:
return querystring
template = '({query}) UNION{type} ({union})' \
if self.wrap_union_queries \
else '{query} UNION{type} {union}'
for (union_type, other) in self._unions:
other_querystring = other.get_sql(quote_char=quote_char)
if len(self._selects) != len(other._selects):
raise UnionException(
"Queries must have an equal number of select statements in a union."
"\n\nMain Query:\n{query1}\n\nUnion Query:\n{query2}".
format(query1=querystring, query2=other_querystring))
querystring = template.format(query=querystring,
type=union_type.value,
union=other_querystring)
return querystring