def sql_query(query, schema=None):
"""
VERY BASIC QUERY EXPRESSION TO SQL
:param query: jx-expression
:return: SQL
"""
from jx_base import jx_expression
from jx_bigquery.expressions._utils import BQLang
query = wrap(query)
acc = [SQL_SELECT]
if not query.select:
acc.append(SQL_STAR)
else:
select = _normalize_select(query.select, query["from"], schema)
acc.append(
JoinSQL(
SQL_COMMA,
[
sql_alias(BQLang[jx_expression(s.value)].to_bq(schema),
escape_name(s.name)) for s in select
],
))
acc.append(SQL_FROM)
acc.append(quote_column(ApiName(*split_field(query["from"]))))
if query.where:
acc.append(SQL_WHERE)
acc.append(BQLang[jx_expression(query.where)].to_bq(schema))
if query.sort:
sort = _normalize_sort(query.sort)
acc.append(SQL_ORDERBY)
acc.append(
JoinSQL(
SQL_COMMA,
[
ConcatSQL(
BQLang[jx_expression(s.value)].to_bq(schema),
SQL_DESC if s.sort == -1 else SQL_ASC,
) for s in sort
],
))
if query.limit:
acc.append(SQL_LIMIT)
acc.append(BQLang[jx_expression(query.limit)].to_bq(schema))
return ConcatSQL(*acc)
def sort(data, fieldnames=None, already_normalized=False):
"""
PASS A FIELD NAME, OR LIST OF FIELD NAMES, OR LIST OF STRUCTS WITH {"field":field_name, "sort":direction}
"""
try:
if data == None:
return Null
if isinstance(fieldnames, int):
funcs = [(lambda t: t[fieldnames], 1)]
else:
if not fieldnames:
return wrap(sort_using_cmp(data, value_compare))
if already_normalized:
formal = fieldnames
else:
formal = query._normalize_sort(fieldnames)
funcs = [(get(f.value), f.sort) for f in formal]
def comparer(left, right):
for func, sort_ in funcs:
try:
result = value_compare(func(left), func(right), sort_)
if result != 0:
return result
except Exception as e:
Log.error("problem with compare", e)
return 0
if is_list(data):
output = FlatList(
[unwrap(d) for d in sort_using_cmp(data, cmp=comparer)])
elif is_text(data):
Log.error("Do not know how to handle")
elif hasattr(data, "__iter__"):
output = FlatList(
[unwrap(d) for d in sort_using_cmp(list(data), cmp=comparer)])
else:
Log.error("Do not know how to handle")
output = None
return output
except Exception as e:
Log.error("Problem sorting\n{{data}}", data=data, cause=e)
def sort(data, fieldnames=None, already_normalized=False):
"""
PASS A FIELD NAME, OR LIST OF FIELD NAMES, OR LIST OF STRUCTS WITH {"field":field_name, "sort":direction}
"""
try:
if data == None:
return Null
if not fieldnames:
return wrap(sort_using_cmp(data, value_compare))
if already_normalized:
formal = fieldnames
else:
formal = query._normalize_sort(fieldnames)
funcs = [(jx_expression_to_function(f.value), f.sort) for f in formal]
def comparer(left, right):
for func, sort_ in funcs:
try:
result = value_compare(func(left), func(right), sort_)
if result != 0:
return result
except Exception as e:
Log.error("problem with compare", e)
return 0
if is_list(data):
output = FlatList([unwrap(d) for d in sort_using_cmp(data, cmp=comparer)])
elif hasattr(data, "__iter__"):
output = FlatList(
[unwrap(d) for d in sort_using_cmp(list(data), cmp=comparer)]
)
else:
Log.error("Do not know how to handle")
output = None
return output
except Exception as e:
Log.error("Problem sorting\n{{data}}", data=data, cause=e)
