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