I am trying to use the many-2-many DAL syntax suggested by the manual
and the examples. In this approach, a field name is associated with a
TABLE name.
I have not been able to get this to work with MySQL. So, I have used
the more conventional approach of using an integer field to hold the
key value from the relationship table. Instead of dogs and persons I
have jokes and categories, but it's the same thing. The goal is to be
able to express relations for jokes with no category, jokes with one
or more categories, categories with one or more jokes, and categories
with no jokes. The ideal query returns all of these, if they exist.
Here is the model for my "conventional" approach:
jodb.define_table('joke',
Field('joketext', 'text',length=2048),
Field('created_on', 'datetime', default=request.now),
Field('created_by', jodb.auth_user, default=auth.user_id))
jodb.define_table('category',
Field('name', 'text'))
jodb.define_table('joke_category',
Field('joke_id', 'integer'),
Field('category_id', 'integer'))
jodb.category.name.requires = IS_NOT_EMPTY()
jodb.joke.joketext.requires = IS_NOT_EMPTY()
All was good except I couldn't get the query to work using the persons/
dogs many-to-many approach. I was not getting jokes with no
categories and categories with no jokes. So, I decided to follow the
manual more closely to see if I could try the queries from the
example.
Thus, I tried to change the field definitions in the relations table
to associate the id field with a TABLE as in:
jodb.define_table('joke_category',
Field('joke_id', jodb.joke),
Field('category_id', 'integer'))
Yes, I would need to do the same for category, but I wanted to do one
at a time because I had read somewhere that MySQL migrations did not
work with multiple pending changes (don't know if that is really
true...). Anyway, seemed easier to do one at a time. So, I made the
immediately preceding change in the model. In attempting to do the
migration, MySQL got an error 150. When this happens, either web2py or
MySQL will hang. So, I stopped everything.
Upon resuming everything, I tried to go back to the original way I had
it (above). This appears to cause a second migration with the
following result:
127.0.0.1.2012-01-14.19-48-00.189cb495-7851-429d-a0cb-bd23156431f1
<class 'gluon.contrib.pymysql.err.InternalError'> (1060, u"Duplicate
column name 'joke_id__tmp'")
VERSION
web2py™ (1, 99, 4, datetime.datetime(2011, 12, 14, 14, 46, 14),
'stable')
Python
TRACEBACK
Traceback (most recent call last):
File "c:\web2py\gluon\restricted.py", line 204, in restricted
exec ccode in environment
File "c:/web2py/applications/pyjokes/models/db.py", line 55, in
<module>
Field('category_id', 'integer'))
File "c:\web2py\gluon\dal.py", line 5097, in define_table
polymodel=polymodel)
File "c:\web2py\gluon\dal.py", line 728, in create_table
fake_migrate=fake_migrate)
File "c:\web2py\gluon\dal.py", line 816, in migrate_table
self.execute(sub_query)
File "c:\web2py\gluon\dal.py", line 1359, in execute
return self.log_execute(*a, **b)
File "c:\web2py\gluon\dal.py", line 1353, in log_execute
ret = self.cursor.execute(*a, **b)
File "c:\web2py\gluon\contrib\pymysql\cursors.py", line 108, in
execute
self.errorhandler(self, exc, value)
File "c:\web2py\gluon\contrib\pymysql\connections.py", line 184, in
defaulterrorhandler
raise errorclass, errorvalue
InternalError: (1060, u"Duplicate column name 'joke_id__tmp'")
Note the duplicate column name. It seems that the first migration
created a tmp field to move the column values over to the newly
created column. This hung, but the tmp column must have been
created. The second migration--to get back (which I erroneously
thought wouldn't happen at all, assuming the first migration had
failed completely--clearly needed to create the second tmp column,
which was a dupe.
I can get myself back to the "conventional" approach working.
So, my real question is: how can I formulate the query that returns
all jokes and categories with their relations including no
corresponding joke or category? Should I do this using the
"conventional' way or should I use the suggested web2py model
syntax.
I know--a long question for what is probably a shorter answer.
Many thanks.
