Yes, it would store the ids of the regions between vertical bars,
something like this: |4|7|23|. So say you are looking for the house-types
in China (which is id = 7). It would be something like this:
if you already have the record id no.7, then just this:
houses = db(db.house_types.regions.contains('|7|')).select()
Or, you might have the find the record no. first:
china_rec = db(db.regions.name == 'China').select(db.regions.id).first()
houses =
db(db.house_types.regions.contains('|'+str(china_rec.id)+'|')).select()
As long as your requirements are not too complex it seems to work well.
See also the book. DAL chapter, search for list:reference.
Regards, D
On Saturday, 14 April 2012 21:27:34 UTC+1, leftcase wrote:
>
> Hi David,
>
> Thanks for your reply.
>
> I did come across the list:reference option while trying to figure this
> out. I couldn't figure this out though:
>
> Say I add the following to my house-type table:
>
> Field('regions', 'list:reference region')
>
> If I generate a form using SQLFORM, it presents me with a multiselect
> region option where I can select as many regions as I need. Reading around,
> it seems that the region field would then store something like this for
> instance:
>
> 'england', 'france', 'US', 'china'
>
> How do I then create a query to show all house-type records with a region
> of 'china' for instance?
>
> I'm not an experienced user of development frameworks like this. Is it
> usually this difficult to accomplish this kind of thing? Seems to me it
> would be a pretty common requirement?
>
> Thanks in advance, :-)
>
> Chris
>
> On Sat, Apr 14, 2012 at 8:52 PM, villas wrote:
>
>> I would just mention that if you only have a few regions/housetypes, you
>> might consider de-normalising the data and using list:reference and work
>> with the jQuery.multiselect (if you have any problems with that, you might
>> like to read my other thread on the topic).
>> Best regards, David
>>
>> On Saturday, 14 April 2012 18:31:58 UTC+1, leftcase wrote:
>>>
>>> Hi all,
>>>
>>> I'm pretty new to web2py and web app development and I've spend some
>>> time trying to figure out the best way to do this.
>>>
>>> I have two tables, house-types and regions. A house-type can exist in
>>> many regions, and a region can have many house types:
>>>
>>> db.define_table(
>>> 'region',
>>> Field('name', 'string', length=512, required=True),
>>> format = '%(name)s')
>>>
>>> db.define_table(
>>> 'house-type',
>>> Field('title', 'string', length=512), #title of the newbuild
>>> property listing
>>> Field('vendor',db.vendor, required=True),
>>> Field('bedrooms', 'integer'),
>>> Field('price', 'double',required=True),
>>> Field('description', 'text',length=65536, required=True),
>>> Field('live', 'boolean', default=False)
>>> )
>>>
>>> If I understand correctly, in order to create the many-to-many
>>> relationship I should create a junction table like the following:
>>>
>>> db.define_table(
>>> 'houses_and_regions',
>>> Field('house', db.house-type),
>>> Field('region', db.region))
>>>
>>> And then I should use SQLFORM to construct a form which updates both
>>> house-type and houses_and_region tables when I create or modify a new house.
>>>
>>> What I'm struggling with is how to create a form which allows me to
>>> select multiple regions for a house. I wondered if anyone could give me any
>>> examples?
>>>
>>> Thanks in advance!
>>>
>>> Chris
>>>
>>
>
