Heh, no worries. :-)
I wonder, just out of interest, is there any great performance difference
between searching for records containing region.id 'x' (as below) rather
than returning all rows with region.id 'x' from a junction table?
Chris
On Sat, Apr 14, 2012 at 11:34 PM, villas <villa...@gmail.com> wrote:
> 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
>>>>>>
>>>>>
>>>>
>>
