def _columns(cls, tables, withs): move = tables['move'] from_location = tables['move.from_location'] to_location = tables['move.to_location'] currency = tables['move.company.currency'] sign = Case((from_location.type.in_(cls._to_location_types()) & to_location.type.in_(cls._from_location_types()), -1), else_=1) cost = cls._column_cost(tables, withs, sign) revenue = cls._column_revenue(tables, withs, sign) profit = revenue - cost margin = Case((revenue != 0, profit / revenue), else_=Null) return [ cls._column_id(tables, withs).as_('id'), Literal(0).as_('create_uid'), CurrentTimestamp().as_('create_date'), cls.write_uid.sql_cast(Literal(Null)).as_('write_uid'), cls.write_date.sql_cast(Literal(Null)).as_('write_date'), move.company.as_('company'), cls.cost.sql_cast(Round(cost, currency.digits)).as_('cost'), cls.revenue.sql_cast(Round(revenue, currency.digits)).as_('revenue'), cls.profit.sql_cast(Round(profit, currency.digits)).as_('profit'), cls.margin.sql_cast(Round(margin, cls.margin.digits[1])).as_('margin'), currency.id.as_('currency'), ]
def table_query(cls): Opportunity = Pool().get('sale.opportunity') opportunity = Opportunity.__table__() return opportunity.select( Max(opportunity.create_uid).as_('create_uid'), Max(opportunity.create_date).as_('create_date'), Max(opportunity.write_uid).as_('write_uid'), Max(opportunity.write_date).as_('write_date'), opportunity.company, Count(Literal(1)).as_('number'), Sum( Case((opportunity.state.in_( cls._converted_state()), Literal(1)), else_=Literal(0))).as_('converted'), Sum( Case((opportunity.state.in_(cls._won_state()), Literal(1)), else_=Literal(0))).as_('won'), Sum( Case((opportunity.state.in_(cls._lost_state()), Literal(1)), else_=Literal(0))).as_('lost'), Sum(opportunity.amount).as_('amount'), Sum( Case((opportunity.state.in_( cls._converted_state()), opportunity.amount), else_=Literal(0))).as_('converted_amount'), Sum( Case((opportunity.state.in_( cls._won_state()), opportunity.amount), else_=Literal(0))).as_('won_amount'))
def __register__(cls, module_name): cursor = Transaction().connection.cursor() table_h = cls.__table_handler__(module_name) sample = cls.__table__() super().__register__(module_name) if table_h.column_exist('changed_oil'): cursor.execute(*sample.update([sample.oil_changed], [Case((sample.changed_oil == Literal(True), 'yes'), else_='no')])) table_h.drop_column('changed_oil') if table_h.column_exist('changed_oil_filter'): cursor.execute(*sample.update([sample.oil_filter_changed], [Case((sample.changed_oil_filter == Literal(True), 'yes'), else_='no')])) table_h.drop_column('changed_oil_filter') if table_h.column_exist('changed_air_filter'): cursor.execute(*sample.update([sample.air_filter_changed], [Case((sample.changed_air_filter == Literal(True), 'yes'), else_='no')])) table_h.drop_column('changed_air_filter') if table_h.column_exist('hours_equipment'): cursor.execute(*sample.update([sample.ind_equipment], [sample.hours_equipment])) table_h.drop_column('hours_equipment') if table_h.column_exist('hours_component'): cursor.execute(*sample.update([sample.ind_component], [sample.hours_component])) table_h.drop_column('hours_component') if table_h.column_exist('hours_oil'): cursor.execute(*sample.update([sample.ind_oil], [sample.hours_oil])) table_h.drop_column('hours_oil')
def get_access(cls, models): 'Return access for models' # root user above constraint if Transaction().user == 0: return defaultdict(lambda: defaultdict(lambda: True)) pool = Pool() Model = pool.get('ir.model') UserGroup = pool.get('res.user-res.group') cursor = Transaction().cursor user = Transaction().user model_access = cls.__table__() ir_model = Model.__table__() user_group = UserGroup.__table__() access = {} for model in models: maccess = cls._get_access_cache.get((user, model), default=-1) if maccess == -1: break access[model] = maccess else: return access default = {'read': True, 'write': True, 'create': True, 'delete': True} access = dict((m, default) for m in models) cursor.execute(*model_access.join( ir_model, 'LEFT', condition=model_access.model == ir_model.id ).join(user_group, 'LEFT', condition=user_group.group == model_access.group).select( ir_model.model, Max(Case((model_access.perm_read, 1), else_=0)), Max(Case((model_access.perm_write, 1), else_=0)), Max(Case((model_access.perm_create, 1), else_=0)), Max(Case((model_access.perm_delete, 1), else_=0)), where=ir_model.model.in_(models) & ((user_group.user == user) | (model_access.group == Null)), group_by=ir_model.model)) access.update( dict((m, { 'read': r, 'write': w, 'create': c, 'delete': d }) for m, r, w, c, d in cursor.fetchall())) for model, maccess in access.iteritems(): cls._get_access_cache.set((user, model), maccess) return access
def get_access(cls, models): 'Return fields access for models' # root user above constraint if Transaction().user == 0: return defaultdict(lambda: defaultdict( lambda: defaultdict(lambda: True))) pool = Pool() Model = pool.get('ir.model') ModelField = pool.get('ir.model.field') UserGroup = pool.get('res.user-res.group') user = Transaction().user field_access = cls.__table__() ir_model = Model.__table__() model_field = ModelField.__table__() user_group = UserGroup.__table__() accesses = {} for model in models: maccesses = cls._get_access_cache.get((user, model)) if maccesses is None: break accesses[model] = maccesses else: return accesses default = {} accesses = dict((m, default) for m in models) cursor = Transaction().connection.cursor() cursor.execute(*field_access.join(model_field, condition=field_access.field == model_field.id ).join(ir_model, condition=model_field.model == ir_model.id ).join(user_group, 'LEFT', condition=user_group.group == field_access.group ).select( ir_model.model, model_field.name, Max(Case((field_access.perm_read == True , 1), else_=0)), Max(Case((field_access.perm_write == True, 1), else_=0)), Max(Case((field_access.perm_create == True, 1), else_=0)), Max(Case((field_access.perm_delete == True, 1), else_=0)), where=ir_model.model.in_(models) & ((user_group.user == user) | (field_access.group == Null)), group_by=[ir_model.model, model_field.name])) for m, f, r, w, c, d in cursor.fetchall(): accesses[m][f] = {'read': r, 'write': w, 'create': c, 'delete': d} for model, maccesses in accesses.iteritems(): cls._get_access_cache.set((user, model), maccesses) return accesses
def order_function(cls, tables): table, _ = tables[None] operator = Equal if not null_first: operator = NotEqual field = Column(table, field_name) return [Case((operator(field, Null), 0), else_=1), field]
def get_lines_to_pay(cls, invoices, name): pool = Pool() Move = pool.get('account.move') Line = pool.get('account.move.line') Account = pool.get('account.account') line = Line.__table__() account = Account.__table__() move = Move.__table__() invoice = cls.__table__() cursor = Transaction().connection.cursor() _, origin_type = Move.origin.sql_type() lines = super(Invoice, cls).get_lines_to_pay(invoices, name) for sub_ids in grouped_slice(invoices): red_sql = reduce_ids(invoice.id, sub_ids) query = invoice.join(move, condition=((move.origin == Concat('account.invoice,', Cast(invoice.id, origin_type)))) ).join(line, condition=(line.move == move.id) ).join(account, condition=( (line.account == account.id) & Case((invoice.type == 'out', account.kind == 'receivable'), else_=account.kind == 'payable'))).select( invoice.id, line.id, where=(line.maturity_date != None) & red_sql, order_by=(invoice.id, line.maturity_date)) cursor.execute(*query) for invoice_id, line_id in cursor.fetchall(): if line_id not in lines[invoice_id]: lines[invoice_id].append(line_id) return lines
def convert_order(self, name, tables, Model): if getattr(Model, 'order_%s' % name, None): return super(Selection, self).convert_order(name, tables, Model) assert name == self.name table, _ = tables[None] selections = Model.fields_get([name])[name]['selection'] if not isinstance(selections, (tuple, list)): if not is_instance_method(Model, selections): selections = getattr(Model, selections)() else: selections = [] column = self.sql_column(table) if not self.sort: else_ = len(selections) + 1 selections = ((k, i) for i, (k, v) in enumerate(selections)) else: else_ = column whens = [] for key, value in selections: whens.append((column == key, value)) if whens: return [Case(*whens, else_=else_)] else: return [column]
def search_state(cls, name, clause): pool = Pool() Purchase = pool.get('purchase.purchase') PurchaseLine = pool.get('purchase.line') request = cls.__table__() purchase_line = PurchaseLine.__table__() purchase = Purchase.__table__() _, operator_, state = clause Operator = fields.SQL_OPERATORS[operator_] state_case = Case( ((purchase.state == 'cancel') & (request.exception_ignored == False), 'exception'), ((purchase.state == 'cancel') & (request.exception_ignored == True), 'cancel'), (purchase.state == 'done', 'done'), (request.purchase_line != Null, 'purchased'), else_='draft') state_query = request.join( purchase_line, type_='LEFT', condition=request.purchase_line == purchase_line.id).join( purchase, type_='LEFT', condition=purchase_line.purchase == purchase.id).select( request.id, where=Operator(state_case, state)) return [('id', 'in', state_query)]
def __str__(self): quantifier = 'DISTINCT ' if self.distinct else '' has_filter = Flavor.get().filter_ expression = self.expression if self.filter_ and not has_filter: from sql.conditionals import Case expression = Case((self.filter_, self._case_expression)) order_by = '' if self.order_by: order_by = ' ORDER BY %s' % ', '.join(map(str, self.order_by)) aggregate = '%s(%s%s%s)' % (self._sql, quantifier, expression, order_by) within = '' if self.within: within = (' WITHIN GROUP (ORDER BY %s)' % ', '.join(map(str, self.within))) filter_ = '' if self.filter_ and has_filter: filter_ = ' FILTER (WHERE %s)' % self.filter_ window = '' if self.window: if self.window.has_alias: window = ' OVER "%s"' % self.window.alias else: window = ' OVER (%s)' % self.window return aggregate + within + filter_ + window
def aeat_party_expression(cls, tables): ''' Returns a couple of sql expression and tables used by sql query to compute the aeat party. ''' pool = Pool() Invoice = pool.get('account.invoice') table, _ = tables[None] is_invoice = table.origin.like(Invoice.__name__ + ',%') if 'invoice' in tables: invoice, _ = tables['invoice'] else: invoice = Invoice.__table__() tables['invoice'] = { None: (invoice, (is_invoice & (invoice.id == Cast( Substring(table.origin, Position(',', table.origin) + Literal(1)), Invoice.id.sql_type().base)))), } return Case((is_invoice, invoice.party), else_=Null), tables
def __register__(cls, module): pool = Pool() Subscription = pool.get('sale.subscription') TableHandler = backend.get('TableHandler') transaction = Transaction() cursor = transaction.connection.cursor() table = cls.__table__() subscription = Subscription.__table__() # Migration from 4.8: start_date required if TableHandler.table_exist(cls._table): table_h = cls.__table_handler__(module) if table_h.column_exist('start_date'): cursor.execute( *table.update([table.start_date], subscription.select(subscription.start_date, where=subscription.id == table.subscription), where=table.start_date == Null)) super(Line, cls).__register__(module) table_h = cls.__table_handler__(module) # Migration from 4.8: drop required on description table_h.not_null_action('description', action='remove') # Migration from 5.2: replace consumed by consumed_until if table_h.column_exist('consumed'): cursor.execute(*table.update([table.consumed_until], [ Case((table.consumed, Coalesce(table.next_consumption_date, table.end_date)), else_=Null) ])) table_h.drop_column('consumed')
def search_payment_amount(cls, name, clause): pool = Pool() Payment = pool.get('account.payment') Account = pool.get('account.account') _, operator, value = clause Operator = fields.SQL_OPERATORS[operator] table = cls.__table__() payment = Payment.__table__() account = Account.__table__() payment_amount = Sum(Coalesce(payment.amount, 0)) main_amount = Abs(table.credit - table.debit) - payment_amount second_amount = Abs(table.amount_second_currency) - payment_amount amount = Case((table.second_currency == Null, main_amount), else_=second_amount) value = cls.payment_amount.sql_format(value) query = table.join( payment, type_='LEFT', condition=(table.id == payment.line) & (payment.state != 'failed')).join( account, condition=table.account == account.id).select( table.id, where=account.kind.in_(['payable', 'receivable']), group_by=(table.id, account.kind, table.second_currency), having=Operator(amount, value)) return [('id', 'in', query)]
def get_active(cls, numbers, name): pool = Pool() Coupon = pool.get('sale.promotion.coupon') Sale = pool.get('sale.sale') Sale_Number = pool.get('sale.sale-sale.promotion.coupon.number') table = cls.__table__() coupon = Coupon.__table__() sale = Sale.__table__() sale_number = Sale_Number.__table__() context = Transaction().context cursor = Transaction().connection.cursor() party = context.get('party') query = (table.join(sale_number, 'LEFT', condition=table.id == sale_number.number).join( coupon, condition=table.coupon == coupon.id)) if party: query = query.join(sale, 'LEFT', condition=(sale_number.sale == sale.id) & (sale.party == party)) active = Case( ((coupon.number_of_use > 0) & (coupon.per_party), Count(sale.id) < coupon.number_of_use), ((coupon.number_of_use > 0) & ~Coalesce(coupon.per_party, False), Count(sale_number.sale) < coupon.number_of_use), else_=Literal(True)) else: active = Case(((coupon.number_of_use > 0) & ~Coalesce(coupon.per_party, False), Count(sale_number.sale) < coupon.number_of_use), else_=Literal(True)) query = query.select( table.id, active, group_by=[table.id, coupon.number_of_use, coupon.per_party]) result = {} for sub_numbers in grouped_slice(numbers): query.where = reduce_ids(table.id, map(int, sub_numbers)) cursor.execute(*query) result.update(dict(cursor.fetchall())) return result
def _column_id(cls, tables): line = tables['line'] template_category = tables['line.product.template_category'] # Pairing function from http://szudzik.com/ElegantPairing.pdf return Min( Case((line.id < template_category.id, (template_category.id * template_category.id) + line.id), else_=(line.id * line.id) + line.id + template_category.id))
def search_active(cls, name, clause): pool = Pool() Coupon = pool.get('sale.promotion.coupon') Sale = pool.get('sale.sale') Sale_Number = pool.get('sale.sale-sale.promotion.coupon.number') table = cls.__table__() coupon = Coupon.__table__() sale = Sale.__table__() sale_number = Sale_Number.__table__() context = Transaction().context party = context.get('party') _, operator, value = clause Operator = fields.SQL_OPERATORS[operator] query = (table .join(sale_number, 'LEFT', condition=table.id == sale_number.number) .join(coupon, condition=table.coupon == coupon.id)) if party: query = query.join(sale, 'LEFT', condition=(sale_number.sale == sale.id) & (sale.party == party)) active = Case( ((coupon.number_of_use > 0) & (coupon.per_party), Count(sale.id) < coupon.number_of_use), ((coupon.number_of_use > 0) & ~Coalesce(coupon.per_party, False), Count(sale_number.sale) < coupon.number_of_use), else_=Literal(True)) else: active = Case( ((coupon.number_of_use > 0) & ~Coalesce(coupon.per_party, False), Count(sale_number.sale) < coupon.number_of_use), else_=Literal(True)) query = query.select(table.id, group_by=[table.id, coupon.number_of_use, coupon.per_party], having=Operator(active, value)) return [('id', 'in', query)]
def order_quotation_state(tables): pool = Pool() Quotation = pool.get('purchase.request.quotation') quotation_line, _ = tables[None] quotation = Quotation.__table__() tables['purchase.request.quotation'] = { None: (quotation, quotation_line.quotation == quotation.id), } return [ Case((quotation.state == 'received', 0), else_=1), quotation.state ]
class TestConditionals(unittest.TestCase): table = Table('t') def test_case(self): case = Case((self.table.c1, 'foo'), (self.table.c2, 'bar'), else_=self.table.c3) self.assertEqual(str(case), 'CASE WHEN "c1" THEN %s ' 'WHEN "c2" THEN %s ' 'ELSE "c3" END') self.assertEqual(case.params, ('foo', 'bar'))
def _columns(cls, tables, withs): move = tables['move'] from_location = tables['move.from_location'] to_location = tables['move.to_location'] template = tables['move.product.template'] sign = Case((from_location.type.in_(cls._to_location_types()) & to_location.type.in_(cls._from_location_types()), -1), else_=1) return super()._columns(tables, withs) + [ move.product.as_('product'), Sum(sign * move.internal_quantity).as_('internal_quantity'), template.default_uom.as_('unit'), ]
def _active_query(cls): pool = Pool() Coupon = pool.get('sale.promotion.coupon') Sale = pool.get('sale.sale') Sale_Number = pool.get('sale.sale-sale.promotion.coupon.number') table = cls.__table__() coupon = Coupon.__table__() sale = Sale.__table__() sale_number = Sale_Number.__table__() context = Transaction().context party = context.get('party') query = (table.join(sale_number, 'LEFT', condition=table.id == sale_number.number).join( coupon, condition=table.coupon == coupon.id)) if party: query = query.join(sale, 'LEFT', condition=(sale_number.sale == sale.id) & (sale.party == party)) active = Case( ((coupon.number_of_use > 0) & (coupon.per_party), Count(sale.id) < coupon.number_of_use), ((coupon.number_of_use > 0) & ~Coalesce(coupon.per_party, False), Count(sale_number.sale) < coupon.number_of_use), else_=Literal(True)) else: active = Case(((coupon.number_of_use > 0) & ~Coalesce(coupon.per_party, False), Count(sale_number.sale) < coupon.number_of_use), else_=Literal(True)) query = query.select( group_by=[table.id, coupon.number_of_use, coupon.per_party]) return query, table, active
def convert_order(self, name, tables, Model): if getattr(Model, 'order_%s' % name, None): return super(Selection, self).convert_order(name, tables, Model) assert name == self.name table, _ = tables[None] selections = Model.fields_get([name])[name]['selection'] if not isinstance(selections, (tuple, list)): selections = getattr(Model, selections)() column = self.sql_column(table) whens = [] for key, value in selections: whens.append((column == key, value)) return [Case(*whens, else_=column)]
def _get_login(cls, login): result = cls._get_login_cache.get(login) if result: return result cursor = Transaction().connection.cursor() table = cls.__table__() cursor.execute(*table.select(table.id, table.password_hash, Case( (table.password_reset_expire > CurrentTimestamp(), table.password_reset), else_=None), where=(table.login == login) & (table.active == True))) result = cursor.fetchone() or (None, None, None) cls._get_login_cache.set(login, result) return result
def search_unread(cls, name, clause): pool = Pool() Read = pool.get('ir.note.read') user_id = Transaction().user table = cls.__table__() read = Read.__table__() _, operator, value = clause assert operator in ['=', '!='] Operator = fields.SQL_OPERATORS[operator] where = Operator(Case((read.user != Null, False), else_=True), value) query = table.join(read, 'LEFT', condition=(table.id == read.note) & (read.user == user_id) ).select(table.id, where=where) return [('id', 'in', query)]
def get_unread(cls, ids, name): pool = Pool() Read = pool.get('ir.note.read') cursor = Transaction().connection.cursor() user_id = Transaction().user table = cls.__table__() read = Read.__table__() unread = {} for sub_ids in grouped_slice(ids): where = reduce_ids(table.id, sub_ids) query = table.join(read, 'LEFT', condition=(table.id == read.note) & (read.user == user_id) ).select(table.id, Case((read.user != Null, False), else_=True), where=where) cursor.execute(*query) unread.update(cursor) return unread
def order_language(tables): table, _ = tables[None] return [Case((table.language == Null, 1), else_=0), table.language]
class TestConditionals(unittest.TestCase): table = Table('t') def test_case(self): case = Case((self.table.c1, 'foo'), (self.table.c2, 'bar'), else_=self.table.c3) self.assertEqual(str(case), 'CASE WHEN "c1" THEN %s ' 'WHEN "c2" THEN %s ' 'ELSE "c3" END') self.assertEqual(case.params, ('foo', 'bar')) def test_case_no_expression(self): case = Case((True, self.table.c1), (self.table.c2, False), else_=False) self.assertEqual(str(case), 'CASE WHEN %s THEN "c1" ' 'WHEN "c2" THEN %s ' 'ELSE %s END') self.assertEqual(case.params, (True, False, False)) def test_coalesce(self): coalesce = Coalesce(self.table.c1, self.table.c2, 'foo') self.assertEqual(str(coalesce), 'COALESCE("c1", "c2", %s)') self.assertEqual(coalesce.params, ('foo',)) def test_nullif(self): nullif = NullIf(self.table.c1, 'foo') self.assertEqual(str(nullif), 'NULLIF("c1", %s)') self.assertEqual(nullif.params, ('foo',))
def order_sequence(tables): table, _ = tables[None] return [Case((table.sequence == Null, 0), else_=1), table.sequence]
def table_query(cls): pool = Pool() Date = pool.get('ir.date') Location = pool.get('stock.location') Move = pool.get('stock.move') Product = pool.get('product.product') move = from_ = Move.__table__() transaction = Transaction() context = transaction.context database = transaction.database today = Date.today() if context.get('product_template') is not None: product_template = context['product_template'] if isinstance(product_template, int): product_template = [product_template] product = Product.__table__() from_ = move.join(product, condition=move.product == product.id) product_clause = product.template.in_(product_template or [-1]) product_column = Concat('product.template,', product.template) else: product = context.get('product', -1) if product is None: product = -1 if isinstance(product, int): product = [product] product_clause = move.product.in_(product or [-1]) product_column = Concat('product.product,', move.product) if 'warehouse' in context: warehouse = Location(context.get('warehouse')) if context.get('stock_skip_warehouse'): location_id = warehouse.storage_location.id else: location_id = warehouse.id else: location_id = -1 warehouse = With('id', query=Location.search([ ('parent', 'child_of', [location_id]), ], query=True, order=[])) date_column = Coalesce(move.effective_date, move.planned_date) quantity = Case((move.to_location.in_(warehouse.select( warehouse.id)), move.internal_quantity), else_=-move.internal_quantity) if database.has_window_functions(): cumulative_quantity_delta = Sum(quantity, window=Window( [product_column, date_column], order_by=[move.id.asc])) else: cumulative_quantity_delta = Literal(0) return (from_.select( move.id.as_('id'), Literal(0).as_('create_uid'), CurrentTimestamp().as_('create_date'), Literal(None).as_('write_uid'), Literal(None).as_('write_date'), product_column.as_('product'), date_column.as_('date'), move.id.as_('move'), move.origin.as_('origin'), quantity.as_('quantity'), cumulative_quantity_delta.as_('cumulative_quantity_delta'), move.company.as_('company'), where=product_clause & ((move.from_location.in_(warehouse.select(warehouse.id)) & ~move.to_location.in_(warehouse.select(warehouse.id))) | (~move.from_location.in_(warehouse.select(warehouse.id)) & move.to_location.in_(warehouse.select(warehouse.id)))) & ((date_column < today) & (move.state == 'done') | (date_column >= today)), with_=warehouse))
def sql_pairing(x, y): """Return SQL expression to pair x and y Pairing function from http://szudzik.com/ElegantPairing.pdf""" return Case((x < y, (y * y) + x), else_=(x * x) + x + y)
def convert_domain(self, domain, tables, Model): pool = Pool() Rule = pool.get('ir.rule') Property = pool.get('ir.property') IrModel = pool.get('ir.model') Field = pool.get('ir.model.field') cursor = Transaction().connection.cursor() name, operator, value = domain sql_type = self._field.sql_type().base property_cond = Rule.query_get('ir.property') property_ = Property.__table__() model_field = Field.__table__() model = IrModel.__table__() # Fetch res ids that comply with the domain join = property_.join(model_field, condition=model_field.id == property_.field) join = join.join(model, condition=model.id == model_field.model) cond = ((model.model == Model.__name__) & (model_field.name == name)) if property_cond: cond &= property_.id.in_(property_cond) cursor.execute(*join.select( Cast(Substring(property_.res, Position(',', property_.res) + Literal(1)), Model.id.sql_type().base), property_.id, # Use a Case because the condition created by get_condition # could result in an invalid Cast where=Case( (cond, self.get_condition(sql_type, domain, property_)), else_=(Literal(1) == Literal(0))))) props = cursor.fetchall() default = None for prop in props: if not prop[0]: default = prop[1] break if (not default or ((value is False or value is None) and operator in ['=', '!=']) or (operator in ['not like', 'not ilike', 'not in', '!='])): dom_operator = 'in' # default operator if (((value is False or value is None) and operator == '=') or ((value is not False and value is not None) and operator in [ 'not like', 'not ilike', 'not in', '!='])): dom_operator = 'not in' return [('id', dom_operator, [x[0] for x in props])] # Fetch the res ids that doesn't use the default value cursor.execute(*property_.select( Cast(Substring(property_.res, Position(',', property_.res) + Literal(1)), Model.id.sql_type().base), where=property_cond & (property_.res != Null))) fetchall = cursor.fetchall() if not fetchall: return [('id', 'in', [x[0] for x in props])] else: other_ids = [x[0] for x in fetchall] res_ids = Model.search(['OR', ('id', 'in', [x[0] for x in props]), ('id', 'not in', other_ids) ]) return [('id', 'in', res_ids)]