def _make_sql(self, query, ordering=True):
""" Calculates the SQL for the table widget based on the query """
## Calculate the SQL
query_str = "select "
try:
self.order = int(query.get('order',self.order))
except: self.order=0
try:
self.direction = int(query.get('direction',self.direction))
except: self.direction = 0
total_elements = self.elements + self.filter_elements
## Fixup the elements - if no table specified use the global
## table - this is just a shortcut which allows us to be lazy:
for e in total_elements:
if not e.table: e.table = self.table
if not e.case: e.case = self.case
## The columns and their aliases:
query_str += ",".join([ e.select() + " as `" + e.name + "`" for e in self.elements ])
query_str += _make_join_clause(total_elements)
if self.where:
w = ["(%s)" % self.where,]
else:
w = []
for e in total_elements:
tmp = e.where()
if tmp: w.append(tmp)
## Is there a filter condition?
if self.filter_str:
filter_str = self.filter_str.replace('\r\n', ' ').replace('\n', ' ')
filter_str = parser.parse_to_sql(filter_str, total_elements, ui=None)
if not filter_str: filter_str=1
else: filter_str = 1
query_str += "where (%s and (%s)) " % (" and ".join(w), filter_str)
if self.groupby:
query_str += "group by %s " % DB.escape_column_name(self.groupby)
elif self._groupby:
query_str += "group by %s " % self.groupby
## Now calculate the order by:
if ordering:
try:
query_str += "order by %s " % self.elements[self.order].order_by()
if self.direction == 1:
query_str += "asc"
else: query_str += "desc"
except IndexError:
pass
return query_str
