Oops I think I got confused with the vertical bars, try it with just a
plain integer:
houses = db(db.house_types.regions.contains(7)).select()
On Saturday, 14 April 2012 22:33:54 UTC+1, leftcase wrote:
>
> Thank you again David,
>
> I shall give it a go.
>
> Chris
>
> On Sat, Apr 14, 2012 at 10:29 PM, villas wrote:
>
>> 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
>>>>>
>>>>
>>>
>
