select for list:integer, but in a specific position

[p a]

Hello! Long time no see:

I have a Field 'parameters' of type "list:integer", something like:

db.define_table('operation',
           Field('type', 'integer'),
           Field('parameters','list:integer')
)

The Field db.operation.parameters has a method 'contains' that I can use in queries, like in

db(db.operation.parameters.contains(5))

The generated sql uses "LIKE '%|5|%'" (it also uses ESCAPE, though I don't understand why). For example,

In: db(db.operation.parameters.contains(5))._count()
Out: "SELECT COUNT(*) FROM `operation` WHERE (LOWER(`operation`.`parameters`) LIKE '%|5|%' ESCAPE '\\');"

But I would like to specify that number 5 must be in the first position, for instance. I guess there is no hope in Google App Engine NoSQL and the like, but I only need to work with relational databases. The sql could go like:

SELECT COUNT(*) FROM `operation` WHERE (SUBSTR(`operation`.`parameters`,1,3) = '|5|');

And my questions:

• Is there "an obvious way to do it" (maybe more than one :-/ )?
• I have checked with pydal that the order of the integers is respected in insert, but can I trust that behaviour?

Regards

[p a]

I came up with a first version that works:

def operator_field_starts(field, first_int, query_env={}):
    '''bla bla
    '''
    if not field.type.startswith('list:'):
        raise AttributeError, "bla bla bla"
    dialect = field._dialect
    arg = '|'+dialect._like_escaper_default(str(first_int))+'|%'
    op = dialect.like
    return op(field, arg, escape='\\', query_env=query_env)

from pydal.objects import Query
def field_starts(field, first_int):
    db = field._db
    return Query(db, operator_field_starts, field, first_int)

and then it is used like this:

db( field_starts(db.operation.parameters, my_parameter) & (db.operation.canceled==True))._count()

I feel it would be more efficient to use SUBSTR instead of LIKE, so I'll go for a second version...

Regards

[p a]

And a second version with SUBSTR

def operator_field_starts(field, first_int, query_env={}):
    '''bla bla '''

    if not field.type != 'list:integer':
        raise AttributeError, "bla bla"
    dialect = field._dialect
    second = '|'+dialect._like_escaper_default(str(first_int))+'|'
    return "(%s = '%s')"%(
        dialect.substring(field, (1,len(second))),
        second
    )