def test_compositeexpr_as_alias(self): pk = CompositeExpr(T.tb.obj_id, T.tb.land_id, T.tb.date).as_( ('al1', 'al2', 'al3')) today = datetime.datetime.now().replace(hour=0, minute=0, second=0, microsecond=0) self.assertEqual( Q(T.tb).fields(pk, T.tb.title).where(pk == (1, 'en', today)).select(), ('SELECT "tb"."obj_id" AS "al1", "tb"."land_id" AS "al2", "tb"."date" AS "al3", "tb"."title" FROM "tb" WHERE "al1" = %s AND "al2" = %s AND "al3" = %s', [1, 'en', today])) self.assertEqual( Q(T.tb).fields(pk, T.tb.title).where(pk != (1, 'en', today)).select(), ('SELECT "tb"."obj_id" AS "al1", "tb"."land_id" AS "al2", "tb"."date" AS "al3", "tb"."title" FROM "tb" WHERE "al1" <> %s AND "al2" <> %s AND "al3" <> %s', [1, 'en', today])) self.assertEqual( Q(T.tb).fields(pk, T.tb.title).where( pk.in_(((1, 'en', today), (2, 'en', today)))).select(), ('SELECT "tb"."obj_id" AS "al1", "tb"."land_id" AS "al2", "tb"."date" AS "al3", "tb"."title" FROM "tb" WHERE "al1" = %s AND "al2" = %s AND "al3" = %s OR "al1" = %s AND "al2" = %s AND "al3" = %s', [1, 'en', today, 2, 'en', today])) self.assertEqual( Q(T.tb).fields(pk, T.tb.title).where( pk.not_in(((1, 'en', today), (2, 'en', today)))).select(), ('SELECT "tb"."obj_id" AS "al1", "tb"."land_id" AS "al2", "tb"."date" AS "al3", "tb"."title" FROM "tb" WHERE NOT ("al1" = %s AND "al2" = %s AND "al3" = %s OR "al1" = %s AND "al2" = %s AND "al3" = %s)', [1, 'en', today, 2, 'en', today]))
def test_concat(self): self.assertEqual( Q(T.tb).where( T.tb.cl.concat(1, 2, 'str', T.tb.cl2) != 'str2').select('*'), ( 'SELECT * FROM "tb" WHERE "tb"."cl" || %s || %s || %s || "tb"."cl2" <> %s', [1, 2, 'str', 'str2'], )) self.assertEqual( Q(T.tb).where( T.tb.cl.concat_ws(' + ', 1, 2, 'str', T.tb.cl2) != 'str2'). select('*'), ( 'SELECT * FROM "tb" WHERE concat_ws(%s, "tb"."cl", %s, %s, %s, "tb"."cl2") <> %s', [' + ', 1, 2, 'str', 'str2'], )) self.assertEqual( Q(T.tb, result=Result(compile=mysql_compile)).where( T.tb.cl.concat(1, 2, 'str', T.tb.cl2) != 'str2').select('*'), ( 'SELECT * FROM `tb` WHERE CONCAT(`tb`.`cl`, %s, %s, %s, `tb`.`cl2`) <> %s', [1, 2, 'str', 'str2'], )) self.assertEqual( Q(T.tb, result=Result(compile=mysql_compile)).where( T.tb.cl.concat_ws(' + ', 1, 2, 'str', T.tb.cl2) != 'str2'). select('*'), ( 'SELECT * FROM `tb` WHERE CONCAT_WS(%s, `tb`.`cl`, %s, %s, %s, `tb`.`cl2`) <> %s', [' + ', 1, 2, 'str', 'str2'], ))
def test_t25_1(self): self.assertEqual( compile(Q().fields( Q().tables(T.a).fields(F('*').count()).as_('tot'), Q().tables(T.a).fields(F('*').count()).as_('another_tot'))), ('SELECT (SELECT COUNT(*) FROM "a") AS "tot", (SELECT COUNT(*) FROM "a") AS "another_tot"', []))
def sync(self, folder: str) -> None: with sqlite3.connect(self.db_name) as connection: row = connection.execute(*compile( Q(T.icalendar).fields( '*').where(T.icalendar.user == self.user and T.icalendar.url == self.url))).fetchone() url = row[1] sync_dir = path.join(folder, self.user, 'ics') if not path.exists(sync_dir): makedirs(sync_dir) IcsCalendar( url, SimpleCalEventsIntervals( tz.tzlocal(), FileTimeIntervals( path.join(sync_dir, urllib.parse.quote(self.url, safe=''))))).sync() connection.execute( *Q(T.icalendar, result=Result( compile=compile)).where(T.icalendar.user == self.user and T.icalendar.url == self.url). update({T.icalendar.sync_time: int(round(time.time() * 1000))}))
def test_where_subquery(self): sub_q = Q().fields(T.author.id).tables( T.author).where(T.author.status == 'active') q = Q().fields(T.book.id).tables(T.book).where( T.book.author_id.in_(sub_q)) self.assertEqual(compile(q), ( 'SELECT "book"."id" FROM "book" WHERE "book"."author_id" IN (SELECT "author"."id" FROM "author" WHERE "author"."status" = %s)', ['active']))
def test_insert(self): self.assertEqual( Q(T.stats).insert(OrderedDict(( (T.stats.object_type, 'author'), (T.stats.object_id, 15), (T.stats.counter, 1), )), on_duplicate_key_update=OrderedDict( ((T.stats.counter, T.stats.counter + func.VALUES(T.stats.counter)), )), duplicate_key=(T.stats.object_type, T.stats.object_id)), ('INSERT INTO "stats" ("object_type", "object_id", "counter") VALUES (%s, %s, %s) ON CONFLICT ("object_type", "object_id") DO UPDATE SET "counter" = "stats"."counter" + VALUES("stats"."counter")', ['author', 15, 1])) self.assertEqual( Q(T.stats).insert(OrderedDict(( ('object_type', 'author'), ('object_id', 15), ('counter', 1), )), on_duplicate_key_update=OrderedDict( (('counter', T.stats.counter + func.VALUES(T.stats.counter)), )), duplicate_key=('object_type', 'object_id')), ('INSERT INTO "stats" ("object_type", "object_id", "counter") VALUES (%s, %s, %s) ON CONFLICT ("object_type", "object_id") DO UPDATE SET "counter" = "stats"."counter" + VALUES("stats"."counter")', ['author', 15, 1])) self.assertEqual( Q().fields(T.stats.object_type, T.stats.object_id, T.stats.counter).tables(T.stats).insert( values=('author', 15, 1), on_duplicate_key_update=OrderedDict( ((T.stats.counter, T.stats.counter + func.VALUES(T.stats.counter)), )), duplicate_key=(T.stats.object_type, T.stats.object_id)), ('INSERT INTO "stats" ("object_type", "object_id", "counter") VALUES %s, %s, %s ON CONFLICT ("object_type", "object_id") DO UPDATE SET "counter" = "stats"."counter" + VALUES("stats"."counter")', ['author', 15, 1])) self.assertEqual( Q().fields(T.stats.object_type, T.stats.object_id, T.stats.counter).tables(T.stats).insert( values=( ('author', 15, 1), ('author', 16, 1), ), on_duplicate_key_update=OrderedDict( ((T.stats.counter, T.stats.counter + func.VALUES(T.stats.counter)), )), duplicate_key=(T.stats.object_type, T.stats.object_id)), ('INSERT INTO "stats" ("object_type", "object_id", "counter") VALUES (%s, %s, %s), (%s, %s, %s) ON CONFLICT ("object_type", "object_id") DO UPDATE SET "counter" = "stats"."counter" + VALUES("stats"."counter")', ['author', 15, 1, 'author', 16, 1])) self.assertEqual( Q().fields(T.stats.object_type, T.stats.object_id, T.stats.counter).tables(T.stats).insert( values=('author', 15, 1), ignore=True), ('INSERT INTO "stats" ("object_type", "object_id", "counter") VALUES %s, %s, %s ON CONFLICT DO NOTHING', ['author', 15, 1]))
def test_as_table(self): author_query_alias = Q(T.author).fields(T.author.id).where( T.author.status == 'active').as_table('author_query_alias') self.assertEqual( compile(Q().fields(T.book.id, T.book.title).tables( (T.book & author_query_alias).on( T.book.author_id == author_query_alias.id))), ('SELECT "book"."id", "book"."title" FROM "book" INNER JOIN (SELECT "author"."id" FROM "author" WHERE "author"."status" = %s) AS "author_query_alias" ON ("book"."author_id" = "author_query_alias"."id")', ['active']))
def test_in(self): self.assertEqual( Q(T.tb).where(T.tb.cl == [ 1, T.tb.cl3, 5, ]).where(T.tb.cl2 == [ 1, T.tb.cl4, ]).select('*'), ( 'SELECT * FROM "tb" WHERE "tb"."cl" IN (%s, "tb"."cl3", %s) AND "tb"."cl2" IN (%s, "tb"."cl4")', [ 1, 5, 1, ], )) self.assertEqual( Q(T.tb).where(T.tb.cl != [ 1, 3, 5, ]).select('*'), ( 'SELECT * FROM "tb" WHERE "tb"."cl" NOT IN (%s, %s, %s)', [ 1, 3, 5, ], )) self.assertEqual( Q(T.tb).where(T.tb.cl.in_([ 1, 3, 5, ])).select('*'), ( 'SELECT * FROM "tb" WHERE "tb"."cl" IN (%s, %s, %s)', [ 1, 3, 5, ], )) self.assertEqual( Q(T.tb).where(T.tb.cl.not_in([ 1, 3, 5, ])).select('*'), ( 'SELECT * FROM "tb" WHERE "tb"."cl" NOT IN (%s, %s, %s)', [ 1, 3, 5, ], ))
def test_alias_subquery(self): alias = Q().fields(T.book.id.count()).tables( T.book).where((T.book.pub_date > '2015-01-01') & (T.book.author_id == T.author.id)).group_by( T.book.author_id).as_("book_count") q = Q().fields(T.author.id, alias).tables( T.author).where(T.author.status == 'active').order_by(alias.desc()) self.assertEqual(compile(q), ( 'SELECT "author"."id", (SELECT COUNT("book"."id") FROM "book" WHERE "book"."pub_date" > %s AND "book"."author_id" = "author"."id" GROUP BY "book"."author_id") AS "book_count" FROM "author" WHERE "author"."status" = %s ORDER BY "book_count" DESC', ['2015-01-01', 'active']))
def test_fields_subquery(self): sub_q = Q().fields(T.book.id.count().as_("book_count")).tables( T.book).where(T.book.pub_date > '2015-01-01').group_by( T.book.author_id) q = Q().fields(T.author.id, sub_q.where(T.book.author_id == T.author.id)).tables( T.author).where(T.author.status == 'active') self.assertEqual(compile(q), ( 'SELECT "author"."id", (SELECT COUNT("book"."id") AS "book_count" FROM "book" WHERE "book"."pub_date" > %s AND "book"."author_id" = "author"."id" GROUP BY "book"."author_id") FROM "author" WHERE "author"."status" = %s', ['2015-01-01', 'active']))
def test_union(self): a = Q(T.item).where(T.item.status != -1).fields( T.item.type, T.item.name, T.item.img) b = Q(T.gift).where(T.gift.storage > 0).columns( T.gift.type, T.gift.name, T.gift.img) self.assertEqual(( a.as_set(True) | b ).order_by("type", "name", desc=True).limit(100, 10).select(), ( '(SELECT "item"."type", "item"."name", "item"."img" FROM "item" WHERE "item"."status" <> %s) UNION ALL (SELECT "gift"."type", "gift"."name", "gift"."img" FROM "gift" WHERE "gift"."storage" > %s) ORDER BY %s DESC, %s DESC LIMIT %s OFFSET %s', [-1, 0, 'type', 'name', 10, 100], ))
def test_expression(self): self.assertEqual( Q(T.tb1).select(E('5 * 3 - 2*8').as_('sub_value')), ( 'SELECT (5 * 3 - 2*8) AS "sub_value" FROM "tb1"', [], )) self.assertEqual( Q(T.tb1).select(E('(5 - 2) * 8 + (6 - 3) * 8').as_('sub_value')), ( 'SELECT ((5 - 2) * 8 + (6 - 3) * 8) AS "sub_value" FROM "tb1"', [], ))
def __ensure_table(self, connection: Connection) -> None: info = connection.execute(*compile( Q(T.sqlite_master).fields( '*').where((T.sqlite_master.name == 'version') & (T.sqlite_master.type == 'table')))).fetchone() if not info: connection.execute( 'CREATE TABLE version (number INTEGER NOT NULL)') connection.execute( *Q(T(self.table), result=Result( compile=compile)).insert({T(self.table).number: 0}))
def test_function(self): self.assertEqual( Q(T.tb).where(func.FUNC_NAME(T.tb.cl) == 5).select('*'), ( 'SELECT * FROM "tb" WHERE FUNC_NAME("tb"."cl") = %s', [ 5, ], )) self.assertEqual( Q(T.tb).where(T.tb.cl == func.RAND()).select('*'), ( 'SELECT * FROM "tb" WHERE "tb"."cl" = RAND()', [], ))
def test_insert_select(self): self.assertEqual( Q().fields( T.stats.object_type, T.stats.object_id, T.stats.counter).tables(T.stats).insert( values=Q().fields(T.old_stats.object_type, T.old_stats.object_id, T.old_stats.counter).tables(T.old_stats), on_duplicate_key_update=OrderedDict( ((T.stats.counter, T.stats.counter + T.old_stats.counter), )), duplicate_key=(T.stats.object_type, T.stats.object_id)), ('INSERT INTO "stats" ("object_type", "object_id", "counter") SELECT "old_stats"."object_type", "old_stats"."object_id", "old_stats"."counter" FROM "old_stats" ON CONFLICT ("object_type", "object_id") DO UPDATE SET "counter" = "stats"."counter" + "old_stats"."counter"', []))
def test_prefix(self): self.assertEqual( Q(T.tb).where(~(T.tb.cl == 3)).select('*'), ( 'SELECT * FROM "tb" WHERE NOT "tb"."cl" = %s', [ 3, ], )) self.assertEqual( Q(T.tb).where(Not(T.tb.cl == 3)).select('*'), ( 'SELECT * FROM "tb" WHERE NOT "tb"."cl" = %s', [ 3, ], ))
def test_field(self): # Get field as table attribute self.assertEqual(type(T.book.title), Field) self.assertEqual(compile(T.book.title), ('"book"."title"', [])) self.assertEqual(type(T.book.title.as_('a')), A) self.assertEqual(compile(T.book.title.as_('a')), ('"a"', [])) self.assertEqual(type(T.book.title__a), A) self.assertEqual(compile(T.book.title__a), ('"a"', [])) # Get field as class F attribute (Legacy) self.assertEqual(type(F.book__title), Field) self.assertEqual(compile(F.book__title), ('"book"."title"', [])) self.assertEqual(type(F.book__title.as_('a')), A) self.assertEqual(compile(F.book__title.as_('a')), ('"a"', [])) self.assertEqual(type(F.book__title__a), A) self.assertEqual(compile(F.book__title__a), ('"a"', [])) # Test with context al = T.book.status.as_('a') self.assertEqual( compile(Q().tables(T.book).fields(T.book.id, al).where( al.in_(('new', 'approved')))), ('SELECT "book"."id", "book"."status" AS "a" FROM "book" WHERE "a" IN (%s, %s)', ['new', 'approved']))
def as_html(self, sync_url: typing.Callable[[str], str]) -> str: result = '' with sqlite3.connect(self.db_name) as connection: row = connection.execute(*compile( Q(T.icalendar).fields( '*').where(T.icalendar.user == self.user and T.icalendar.url == self.url))).fetchone() try: sync = datetime.datetime.fromtimestamp(row[3] / 1000) except OSError: sync = datetime.datetime.min name = row[2] url = row[1] delta: datetime.timedelta = datetime.datetime.now() - sync if delta.seconds > 60: sync_html = f'<mark>Sync was at {str(sync)}</mark>' else: sync_html = f'<code>Sync was at {str(sync)}</code>' result = f''' <h2>{name}</h2> <span><strong>Url: </strong>{url}</span></br> {sync_html}</br> <span><a href="{sync_url(url)}">Sync it now!</a></span></br> ''' return result
def get_list(self, timestamp=False): q = Q(self.Table, result=Result(compile=mysql_compile)).\ fields(self.Column, self.Date) if self.start: q = q.where(self.Date >= self.start) if self.end: q = q.where(self.Date <= self.end) if self.limit: q = q.limit(self.offset, self.limit) if self.desc: q = q.order_by(self.Date.desc()) else: q = q.order_by(self.Date) sql, params = q.select() logging.info("获取数据SQL:%s, 参数:%s", sql, params) with get_mysql_client() as cursor: cursor.execute(sql, params) data = cursor.fetchall() if timestamp: result = [ (int(time.mktime(item[self.date_alias].timetuple())) * 1000, item[self.column]) for item in data if item[self.column] is not None ] else: result = [ item[self.column] for item in data if item[self.column] is not None ] # logging.info("数据为:%s", result) return result
def test_order_by(self): q = Q().tables(T.author).fields('*') self.assertEqual(compile(q), ('SELECT * FROM "author"', [])) q = q.order_by(T.author.first_name, T.author.last_name) self.assertEqual(compile(q), ( 'SELECT * FROM "author" ORDER BY "author"."first_name" ASC, "author"."last_name" ASC', [])) q = q.order_by(T.author.age.desc()) self.assertEqual(compile(q), ( 'SELECT * FROM "author" ORDER BY "author"."first_name" ASC, "author"."last_name" ASC, "author"."age" DESC', [])) self.assertEqual(type(q.order_by()), ExprList) self.assertEqual(compile(q.order_by()), ( '"author"."first_name" ASC, "author"."last_name" ASC, "author"."age" DESC', [])) self.assertEqual( compile(q.order_by([T.author.id.desc(), T.author.status])), ('SELECT * FROM "author" ORDER BY "author"."id" DESC, "author"."status" ASC', [])) self.assertEqual(compile(q.order_by([])), ('SELECT * FROM "author"', [])) self.assertEqual(compile(q.order_by(reset=True)), ('SELECT * FROM "author"', [])) self.assertEqual(compile(q), ( 'SELECT * FROM "author" ORDER BY "author"."first_name" ASC, "author"."last_name" ASC, "author"."age" DESC', []))
def test_distinct(self): q = Q().tables(T.author).fields(T.author.first_name, T.author.last_name, T.author.age) self.assertEqual(compile(q), ( 'SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author"', [])) q = q.distinct(T.author.first_name, T.author.last_name) self.assertEqual(compile(q), ( 'SELECT DISTINCT ON ("author"."first_name", "author"."last_name") "author"."first_name", "author"."last_name", "author"."age" FROM "author"', [])) q = q.distinct(T.author.age) self.assertEqual(compile(q), ( 'SELECT DISTINCT ON ("author"."first_name", "author"."last_name", "author"."age") "author"."first_name", "author"."last_name", "author"."age" FROM "author"', [])) self.assertEqual(type(q.distinct()), ExprList) self.assertEqual( compile(q.distinct()), ('"author"."first_name", "author"."last_name", "author"."age"', [])) self.assertEqual(compile(q.distinct([T.author.id, T.author.status])), ( 'SELECT DISTINCT ON ("author"."id", "author"."status") "author"."first_name", "author"."last_name", "author"."age" FROM "author"', [])) self.assertEqual(compile(q.distinct([])), ( 'SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author"', [])) self.assertEqual(compile(q.distinct(reset=True)), ( 'SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author"', [])) self.assertEqual(compile(q), ( 'SELECT DISTINCT ON ("author"."first_name", "author"."last_name", "author"."age") "author"."first_name", "author"."last_name", "author"."age" FROM "author"', []))
def test_group_by(self): q = Q().tables(T.author).fields('*') self.assertEqual(compile(q), ('SELECT * FROM "author"', [])) q = q.group_by(T.author.first_name, T.author.last_name) self.assertEqual(compile(q), ( 'SELECT * FROM "author" GROUP BY "author"."first_name", "author"."last_name"', [])) q = q.group_by(T.author.age) self.assertEqual(compile(q), ( 'SELECT * FROM "author" GROUP BY "author"."first_name", "author"."last_name", "author"."age"', [])) self.assertEqual(type(q.group_by()), ExprList) self.assertEqual( compile(q.group_by()), ('"author"."first_name", "author"."last_name", "author"."age"', [])) self.assertEqual(compile(q.group_by([T.author.id, T.author.status])), ( 'SELECT * FROM "author" GROUP BY "author"."id", "author"."status"', [])) self.assertEqual(compile(q.group_by([])), ('SELECT * FROM "author"', [])) self.assertEqual(compile(q.group_by(reset=True)), ('SELECT * FROM "author"', [])) self.assertEqual(compile(q), ( 'SELECT * FROM "author" GROUP BY "author"."first_name", "author"."last_name", "author"."age"', []))
def EXCHANGE(self, env=None, limit=1, contract=None, **kwargs): _table_name = "future_exchange" _column = "price_sell" _currency = 'USD' _future = "1w" _description = "汇率" _desc = ["date"] _date_column = "date" _field_name = inspect.stack()[0].function tb = getattr(T, _table_name) cl = getattr(tb, _column).as_(_field_name) f_l = [cl] if kwargs.get("need_date", False): dl = getattr(tb, _date_column).as_("date") f_l.append(dl) q = Q(tb, result=Result(compile=mysql_compile)).fields(tuple(f_l)). \ where(tb.currency == _currency).where(tb.future == _future).limit( limit).order_by( tb.date.desc()) q = self._add_date_limit(q, tb, _date_column, **kwargs) return q.select()
def number(self): with sqlite3.connect(self.db_name) as connection: self.__ensure_table(connection) version = connection.execute( *compile(Q(T(self.table)).fields('number'))).fetchone()[0] return version
def get_list(self, timestamp=False): q = None std_obj = None for data_obj in self.data_objects: if q is None: std_obj = data_obj q = Q(std_obj.Table, result=Result(compile=mysql_compile)).fields( std_obj.Column) else: q = q.tables(q.tables() + data_obj.Table).\ on(std_obj.Date == data_obj.Date).\ fields(data_obj.Column) if timestamp: q = q.fields(std_obj.Date) if self.start: q = q.where(std_obj.Date >= self.start) if self.end: q = q.where(std_obj.Date <= self.end) if self.limit: q = q.limit(self.offset, self.limit) if self.desc: q = q.order_by(std_obj.Date.desc()) else: q = q.order_by(std_obj.Date) sql, params = q.select() with get_mysql_client() as cursor: cursor.execute(sql, params) data = cursor.fetchall() result = [item.values() for item in data] logging.info("获取数据SQL:%s, 参数:%s, 数据为:%s", sql, params, result) return result
def test_result(self): class CustomResult(Result): custom_attr = 5 def custom_method(self, arg1, arg2): return (self._query, arg1, arg2) def find_by_name(self, name): return self._query.where(T.author.name == name) q = Q(result=CustomResult(compile=mysql_compile)).fields( T.author.id, T.author.name).tables(T.author) self.assertEqual(q.custom_attr, 5) q2, arg1, arg2 = q.custom_method(5, 10) self.assertIsNot(q2, q) self.assertEqual(q2.select(), q.select()) self.assertEqual( q2.select(), ('SELECT `author`.`id`, `author`.`name` FROM `author`', [])) self.assertEqual(arg1, 5) self.assertEqual(arg2, 10) q3 = q.find_by_name('John') self.assertIsNot(q3, q) self.assertEqual(q3.select(), ( 'SELECT `author`.`id`, `author`.`name` FROM `author` WHERE `author`.`name` = %s', ['John']))
async def select_by_login(cls, login): async with database.query() as Q: return await (Q().tables( (T.company & T.user & T.m2m_user_company ).on((T.company.id == T.m2m_user_company.company_id) & (T.user.id == T.m2m_user_company.user_id))).fields( T.company.id, T.company.name).where( T.user.login == login).selectall())
async def select_by_login(cls, login): async with database.query() as Q: return await (Q().tables((T.user & T.user_permission_schema).on( T.user.id == T.user_permission_schema.user_id)).fields( T.user.login, T.user_permission_schema.target, T.user_permission_schema.permission, T.user_permission_schema.domen).where( T.user.login == login).selectall())
async def select_roles_by_login(cls, login): async with database.query() as Q: return await (Q().tables( (T.role & T.m2m_user_role & T.user).on((T.role.id == T.m2m_user_role.role_id) & (T.user.id == T.m2m_user_role.user_id))).fields( T.role.name).where( (T.user.login == login)).selectall())
def Table(self): ft = self._ft ct = T('contracts') dt = T('day_kline') dq = Q(dt, result=Result(compile=mysql_compile)) dq = dq.fields( Field(Expr('max(`day_kline`.`date_time`)')).as_('latest'), dt['contract']) dq = dq.group_by(dt['contract']) lt = dq.as_table('future_latest') q = Q(ft, result=Result(compile=mysql_compile)) q = q.tables((q.tables() + ct)).on(ft['contract'] == ct['contract']) q = q.tables((q.tables() + lt)).on(ft['contract'] == lt['contract']) q = q.tables((q.tables() + dt)).on(ft['contract'] == dt['contract']) q = q.where(lt['latest'] == dt['date_time']) q = q.fields(dt[self.column], ct['expire_date'].as_(self.date_alias)) return q.as_table(str(id(self)))