def _orderby(q: QueryBuilder, column: Column, order: str) -> QueryBuilder:
if order == "asc":
return q.orderby(parse_parameter(column.parameter), order=Order.asc)
elif order == "desc":
return q.orderby(parse_parameter(column.parameter), order=Order.desc)
elif order == "none":
return q.orderby(parse_parameter(column.parameter))
def _orderby(q: QueryBuilder, column: Column, order: str) -> QueryBuilder:
column_param = parse_parameter(column.parameter)
value_ = column_param.get("value")
if order == "asc":
return q.orderby(value_, order=Order.asc)
elif order == "desc":
return q.orderby(value_, order=Order.desc)
elif order == "none":
return q.orderby(value_)
def _filter(q: QueryBuilder, filter: Filter) -> QueryBuilder:
if len(filter.filters) > 0:
where = _connective_filter(filter)
# print(where)
return q.where(where)
else:
return q
def apply_dates(query: QueryBuilder,
target: Table,
start: Optional[date] = None,
stop: Optional[date] = None) -> QueryBuilder:
if start and stop:
if start == stop:
return query.where(target.date == Entry.as_value(start))
else:
return query.where(
target.date[Entry.as_value(start):Entry.as_value(stop)])
elif start:
return query.where(target.date >= Entry.as_value(start))
elif stop:
return query.where(target.date <= Entry.as_value(stop))
return query
def test_q_basic_or(self):
q = Q(join_type="OR", id=8)
r = q._resolve(
TestQCall.DummyQuerySet(),
QueryContext(QueryBuilder()).push(IntFields, IntFields._meta.table())
)
self.assertEqual(r.where_criterion.get_sql(), '"id"=8')
def test_not_exists(self):
t1 = Table("def", alias="t1")
q1 = QueryBuilder().from_(t1).where(ExistsCriterion(
self.q2).negate()).select(t1.field1)
self.assertEqual(
'SELECT "t1"."field1" FROM "def" "t1" WHERE NOT EXISTS (SELECT "t2"."field2" FROM "abc" "t2")',
str(q1))
def test_q_with_blank_or3(self):
q = Q() | Q(id__gt=5)
r = q._resolve(
TestQCall.DummyQuerySet(),
QueryContext(QueryBuilder()).push(CharFields, CharFields._meta.table())
)
self.assertEqual(r.where_criterion.get_sql(), '"id">5')
def test_q_complex_int3(self):
q = Q(Q(id__lt=5, id__gt=50, join_type="OR"), join_type="AND", intnum=80)
r = q._resolve(
TestQCall.DummyQuerySet(),
QueryContext(QueryBuilder()).push(IntFields, IntFields._meta.table())
)
self.assertEqual(r.where_criterion.get_sql(), '"intnum"=80 AND ("id"<5 OR "id">50)')
def test_q_multiple_or2(self):
q = Q(join_type="OR", id=8, intnum=80)
r = q._resolve(
TestQCall.DummyQuerySet(),
QueryContext(QueryBuilder()).push(IntFields, IntFields._meta.table())
)
self.assertEqual(r.where_criterion.get_sql(), '"id"=8 OR "intnum"=80')
def test_q_multiple_and(self):
q = Q(join_type="AND", id__gt=8, id__lt=10)
r = q._resolve(
TestQCall.DummyQuerySet(),
QueryContext(QueryBuilder()).push(IntFields, IntFields._meta.table())
)
self.assertEqual(r.where_criterion.get_sql(), '"id">8 AND "id"<10')
def test_q_complex_char3(self):
q = Q(~Q(char__lt=5, char__gt=50, join_type="OR"), join_type="AND", char_null=80)
r = q._resolve(
TestQCall.DummyQuerySet(),
QueryContext(QueryBuilder()).push(CharFields, CharFields._meta.table())
)
self.assertEqual(
r.where_criterion.get_sql(),
"\"char_null\"='80' AND NOT (\"char\"<'5' OR \"char\">'50')",
)
def _join(q: QueryBuilder, join: Join) -> QueryBuilder:
# left
topic = get_topic_by_id(join.topicId)
topic_col_name = build_collection_name(topic.name)
factor = get_factor(join.factorId, topic)
left_table = Table(topic_col_name).as_(topic.name)
# right
sec_topic = get_topic_by_id(join.secondaryTopicId)
sec_topic_col_name = build_collection_name(sec_topic.name)
sec_factor = get_factor(join.secondaryFactorId, sec_topic)
right_table = Table(sec_topic_col_name).as_(sec_topic.name)
if join.type == JoinType.inner:
return q.join(right_table, JoinType.inner).on(
operator.eq(left_table[factor.name], right_table[sec_factor.name]))
if join.type == JoinType.left:
return q.join(right_table, JoinType.left).on(
operator.eq(left_table[factor.name], right_table[sec_factor.name]))
if join.type == JoinType.right:
return q.join(right_table, JoinType.right).on(
operator.eq(left_table[factor.name], right_table[sec_factor.name]))
def _indicator(q: QueryBuilder, indicator: ReportIndicator, column: Column) -> QueryBuilder:
if indicator.arithmetic == "sum":
return q.select(fn.Sum(parse_parameter(column.parameter)))
elif indicator.arithmetic == "avg":
return q.select(fn.Avg(parse_parameter(column.parameter)))
elif indicator.arithmetic == "max":
return q.select(fn.Max(parse_parameter(column.parameter)))
elif indicator.arithmetic == "min":
return q.select(fn.Min(parse_parameter(column.parameter)))
elif indicator.arithmetic == "count":
return q.select(fn.Count(parse_parameter(column.parameter)))
else:
return q.select(fn.Max(parse_parameter(column.parameter)))
def _indicator(q: QueryBuilder, indicator: ReportIndicator,
column: Column) -> QueryBuilder:
column_param = parse_parameter(column.parameter)
value_ = column_param.get("value")
if indicator.arithmetic == "sum":
return q.select(fn.Sum(value_))
elif indicator.arithmetic == "avg":
return q.select(fn.Avg(value_))
elif indicator.arithmetic == "max":
return q.select(fn.Max(value_))
elif indicator.arithmetic == "min":
return q.select(fn.Min(value_))
elif indicator.arithmetic == "count":
return q.select(fn.Count(value_))
else:
return q.select(fn.Max(value_))
def _groupby(q: QueryBuilder, column: Column) -> QueryBuilder:
column_param = parse_parameter(column.parameter)
return q.groupby(column_param.get("value"))
def _groupby(q: QueryBuilder, column: Column) -> QueryBuilder:
return q.groupby(parse_parameter(column.parameter))
def _select(q: QueryBuilder, column: Column) -> QueryBuilder:
param = parse_parameter(column.parameter)
return q.select(param.get("value")).as_(column.alias)
def _dimension(q: QueryBuilder, dimension: ReportDimension, column: Column):
return q.select(fn.Max(parse_parameter(column.parameter)))
def test__bitwise_and_in_where_clause(self):
q = QueryBuilder().from_('items').select('abc').where(
Field("foo").bitwiseand(1) == 1)
self.assertEqual('SELECT "abc" FROM "items" WHERE ("foo" & 1)=1',
str(q))
def _builder(cls, **kwargs: Any) -> QueryBuilder:
return QueryBuilder(dialect=Dialects.CLICKHOUSE,
wrap_set_operation_queries=False,
as_keyword=True,
**kwargs)
def _builder(cls, **kwargs):
return QueryBuilder(dialect=Dialects.CLICKHOUSE, wrap_union_queries=False, as_keyword=True, **kwargs)
def _builder(cls, **kwargs: Any) -> "QueryBuilder":
return QueryBuilder(dialect=Dialects.REDSHIFT, **kwargs)
def _dimension(q: QueryBuilder, dimension: ReportDimension, column: Column):
column_param = parse_parameter(column.parameter)
value_ = column_param.get("value")
return q.select(fn.Max(value_))
def _select(q: QueryBuilder, column: Column) -> QueryBuilder:
return q.select(parse_parameter(column.parameter)).as_(column.alias)
def _limit(q: QueryBuilder, count) -> QueryBuilder:
return q.limit(count)
def _builder(cls):
return QueryBuilder(dialect=Dialects.REDSHIFT)
def _builder(cls, **kwargs):
return QueryBuilder(dialect=Dialects.REDSHIFT, **kwargs)
def _builder(cls):
return QueryBuilder(dialect=Dialects.CLICKHOUSE, wrap_union_queries=False)
def _builder(cls, **kwargs):
return QueryBuilder(
dialect=Dialects.SQLLITE, wrapper_cls=SQLLiteValueWrapper, **kwargs
)
Union,
)
from pypika import JoinType, Order, Table
from pypika.functions import Count
from pypika.queries import QueryBuilder
from typing_extensions import Protocol
from tortoise import fields
from tortoise.backends.base.client import BaseDBAsyncClient, Capabilities
from tortoise.exceptions import DoesNotExist, FieldError, IntegrityError, MultipleObjectsReturned
from tortoise.functions import Function
from tortoise.query_utils import Prefetch, Q, QueryModifier, _get_joins_for_related_field
# Empty placeholder - Should never be edited.
QUERY: QueryBuilder = QueryBuilder()
if TYPE_CHECKING: # pragma: nocoverage
from tortoise.models import Model
MODEL = TypeVar("MODEL", bound="Model")
T_co = TypeVar("T_co", covariant=True)
class QuerySetIterable(Protocol[T_co]):
... # pylint: disable=W0104
class QuerySetSingle(Protocol[T_co]):
def __await__(self) -> Generator[Any, None, T_co]: # pragma: nocoverage
... # pylint: disable=W0104
