def select(self) -> Select:
f = None
if self.method == Method.browse:
f = self.browse_fields
elif self.method == Method.retrieve:
f = self.retrieve_fields
f = f or [self.pk_ref()] + list(self.model.__fields__.keys())
return Select(f)
def select(self) -> Select:
if self.method == Method.retrieve:
event_link = Func('event_link', V('cat.slug'), V('e.slug'),
V('e.public'),
funcs.cast(self.settings.auth_key,
'TEXT')).as_('link')
return Select(self.retrieve_fields + (event_link, ))
return super().select()
def select(self) -> Select:
f = None
if self.action == Action.browse:
f = self.browse_fields
else:
assert self.action == Action.retrieve, self.action
f = self.retrieve_fields
f = f or [self.pk_ref()] + list(self.Model.__fields__.keys())
return Select(f)
async def query(self, *, message_id=None, tags=None, query=None):
where = Var('method') == self.request.match_info['method']
pg_pool: BuildPgPool = self.app['pg']
if self.session.company != '__all__':
company_id = await get_create_company_id(pg_pool, self.session.company)
where &= Var('company_id') == company_id
if message_id:
where &= Var('id') == message_id
elif tags:
where &= Var('tags').contains(tags)
elif query:
return await self.query_general(where, query)
# count is limited to 10,000 as it speeds up the query massively
count, items = await asyncio.gather(
pg_pool.fetchval_b(
"""
select count(*)
from (
select 1
from messages
where :where
limit 10000
) as t
""",
where=where,
),
pg_pool.fetch_b(
"""
:select
from messages m
join message_groups j on m.group_id = j.id
where m.id in (
select id from messages
where :where
order by id desc
limit 100
offset :offset
)
order by m.id desc
""",
select=Select(self._select_fields()),
where=where,
offset=self.get_arg_int('from', 0) if self.offset else 0,
),
)
return {'count': count, 'items': [dict(r) for r in items]}
async def query_general(self, where, query):
async with self.app['pg'].acquire() as conn:
items = await conn.fetch_b(
"""
:select
from messages m
join message_groups j on m.group_id = j.id
where m.id in (
select id from messages
where :where and vector @@ plainto_tsquery(:query)
order by id desc
limit 100
offset :offset
)
order by m.id desc
""",
select=Select(self._select_fields()),
tz=self.get_dt_tz(),
query=query,
where=where,
offset=self.get_arg_int('from', 0) if self.offset else 0,
)
return {'count': len(items), 'items': [dict(r) for r in items]}
async def check_item_permissions_query(self, pk):
yield Select([self.pk_ref()])
yield self.from_()
yield self.join()
yield self.where_pk(pk)
yield Limit(Var('1'))
async def browse_count_query(self):
yield Select(funcs.count('*', as_='count_'))
yield self.from_()
yield self.join()
yield self.where()