belongs DAL syntax to search word contains in list:reference type Field

[黄祥]

table

db.define_table('address',

    Field('address'),

    format = lambda r: f'{r.address}' )

db.define_table('customer',

    Field('name'),

    Field('address', 'list:reference address'),

    format = lambda r: f'{r.name}' )

objective

search customer address by type the words that contains address e.g. 'boulevard'

already tried

db(db.customer.address.belongs(db.address.address.contains('boulevard') ) ).select()

no error occured but the result is not expected (blank)

any idea to achieve it ?

thanks

[Massimo Di Pierro]

this should work:

nested_select = db(db.address.address.contains('boulevard'))._select(db.address.id)

rows = db(db.customer.address.belongs(nested_select) ).select()

Notice the _select instead of select makes it a nested select.

Without _ it would give the same result with two explicit selects.

[黄祥]

nested_select = db(db.address.address.contains('boulevard') )._select(db.address.id)

result in browser 

SELECT "address"."id" FROM "address" WHERE (LOWER("address"."address") LIKE '%boulevard%' ESCAPE '\');

rows = db(db.customer.address.belongs(nested_select) ).select()

result in browser

nothing (no error occured but the resuls is not expected)

select = db(db.address.address.contains('boulevard') ).select(db.address.id)
result in browser (only this work as expected)
return db.address.id

any idea ?

thanks

[Ben Lawrence]

Yes I can confirm this (nested select)::

    address_rows = db(db.address.address == user.address)._select(db.address.id)
    assert 1 == len(address_rows)
    customer_rows = db(db.customer.address_list.belongs(address_rows)).select()
    assert 1 == len(customer_rows)

The first assert would be 1 == 83 ( a large number). The second assert would be 1 == 0 (no customer rows).  However (not a nested select)::

    address_rows = db(db.address.address == user.address).select(db.address.id)
    assert 1 == len(address_rows)
    customer_rows = db(db.customer.address_list.belongs(address_rows)).select()
    assert 1 == len(customer_rows)

gives correct answer.