def test_pg_select_bob_and_sally(orb, pg_sql, pg_db, User):
st = pg_sql.statement('SELECT')
q = orb.Query('username') == 'bob'
q &= orb.Query('username') == 'sally'
sql, data = st(User, orb.Context(where=q))
conn = pg_db.connection()
_, count = conn.execute(sql, data)
assert count == 0
def test_my_select_bob_or_sally(orb, my_sql, my_db, User):
st = my_sql.statement('SELECT')
q = orb.Query('username') == 'bob'
q |= orb.Query('username') == 'sally'
sql, data = st(User, orb.Context(where=q))
conn = my_db.connection()
records, count = conn.execute(sql, data)
assert count == 2 and records[0]['username'] in ('bob', 'sally') and records[1]['username'] in ('bob', 'sally')
def search(self, model, terms, **context):
search_context = context.get('context') or orb.Context(**context)
locale = search_context.locale
nodes = self.__parser.parseString(terms)
# separate into 2 categories general (searchable by any column) and specific (user gave a column)
general_nodes = [
node for node in nodes if not isinstance(node, ComparisonNode)
]
comparison_nodes = [
node for node in nodes if isinstance(node, ComparisonNode)
]
# build general search column matches
q = orb.Query()
if general_nodes:
expr = u'.*\s{0}'
pattern = u'(^|.*\s){0}'.format(general_nodes[0].pattern(
self.__thesaurus, locale))
pattern += ''.join(
expr.format(node.pattern(self.__thesaurus, locale))
for node in general_nodes[1:])
general_q = orb.Query()
searchable_columns = model.schema().columns(
flags=orb.Column.Flags.Searchable).values()
if not searchable_columns:
raise orb.errors.InvalidSearch(
'No searchable columns found for {0}'.format(
model.schema().name()))
for column in searchable_columns:
general_q |= orb.Query(column).asString().matches(
pattern, caseSensitive=False)
q &= general_q
# build comparison nodes
if comparison_nodes:
schema = model.schema()
for node in comparison_nodes:
column = schema.column(node[0])
value_node = node[-1]
value = value_node[0]
q &= orb.Query(column) == value
if not q.isNull():
context['where'] = q & context.get('where')
return model.select(**context)
def __call__(self, record, **options):
"""
Calls the getter lookup method for the database record.
:param record <View>
"""
reload = options.pop('reload', False)
# remove any invalid query lookups
if 'where' in options and orb.Query.testNull(options['where']):
options.pop('where')
# lookup the records with a specific model
options.setdefault('locale', record.recordLocale())
view = options.get('view') or self.viewFor(record)
if not view:
return None if self.unique else orb.RecordSet()
# return from the cache when specified
cache = self.cache(view)
cache_key = (record.id(), hash(orb.LookupOptions(**options)),
record.database().name())
if not reload and cache_key in self._local_cache:
out = self._local_cache[cache_key]
out.updateOptions(**options)
return out
self._local_cache.pop(cache_key, None)
# make sure this is a valid record
if not record.isRecord():
if self.unique:
return None
return orb.RecordSet()
# generate the reverse lookup query
reverse_q = orb.Query(self.columnName) == record
options['where'] = reverse_q & options.get('where')
options['db'] = record.database()
options['context'] = record.schema().context(self.func_name)
lookup = orb.LookupOptions(**options)
context = record.contextOptions(**options)
if self.unique:
output = view.selectFirst(lookup=lookup, options=context)
else:
output = view.select(lookup=lookup, options=context)
if isinstance(output, orb.RecordSet):
output.setSource(record)
output.setSourceColumn(self.columnName)
if cache and output is not None:
self._local_cache[cache_key] = output
cache.setValue(cache_key, True, timeout=self.cacheTimeout)
return output
def test_pg_select_bob(orb, User, pg_sql, pg_db):
st = pg_sql.statement('SELECT')
sql, data = st(User, orb.Context(where=orb.Query('username') == 'bob'))
conn = pg_db.connection()
records, count = conn.execute(sql, data)
assert count == 1 and records[0]['username'] == 'bob'
def myGroups(self, **context):
group_ids = GroupUser.select(
where=orb.Query('user') == self).values('group')
context['where'] = orb.Query('id').in_(group_ids) & context.get(
'where')
return Group.select(**context)