On Thursday, June 6, 2013 2:40:57 PM UTC-7, Charlie Clark wrote:
>
> Am 06.06.2013, 23:36 Uhr, schrieb Michael Bayer
> <[email protected]<javascript:>>:
>
>
> >
> > On Jun 6, 2013, at 5:18 PM, Charlie Clark
> > <[email protected] <javascript:>> wrote:
> >
> >> Am 06.06.2013, 20:21 Uhr, schrieb Andy <[email protected] <javascript:>>:
>
> >>
> >>>
> >>> IOW I have things and groups. The rel table is a many-to-many
> relation
> >>> between things and groups. A thing also may have a favorite group; if
>
> >>> so,
> >>> there has to be a rel between that thing and its favorite group.
> >>
> >> Are favourites optional? Why not normalise to Favourites with strict
> >> 1:1 with things and groups?
> >
> > by putting the FK constraint to the composite primary key of "rel", it
> > guarantees that the "favorite" item is a member of the thing->groups
> > collection.
>
> I understand that I just wonder whether every thing has a favourite or
> not, in which case the structure is not fully normalised and that is how I
>
> would do it because it makes the projections easier. Well, to my mind at
> least. And, wouldn't it resolve the join problem?
>
>
I may be misunderstanding the question, but the reason that having a
favorite is optional is because I'm using mysql and mysql doesn't supported
deferred constraints. So if favorite were "NOT NULL", then there would be
no way to create the thing.
Just to muddy the waters some more, that manytomany would be nice, too --
my funny foreign key constraint screws it up impressively.
--Andy
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
#!/usr/bin/python
from sqlalchemy import *
from sqlalchemy import orm
import sqlalchemy as sa
import sqlalchemy.events
# This could be improved.
def _explicit_rel_to_parent(parent_class, parent_table,
child_table,
using = None, # Specify instead of cols below
remote_col_name = None, local_col_obj = None,
backref_name=None, post_update=False):
if using is not None:
if remote_col_name is not None or local_col_obj is not None:
raise ValueError('using is mutually exclusive with explicit cols')
remote_col_name = using
local_col_obj = child_table.columns[using]
remote_col_obj = parent_table.columns[remote_col_name]
backref_obj = (_ca_backref(backref_name) if backref_name is not None
else None)
return orm.relationship(parent_class,
foreign_keys=[local_col_obj],
primaryjoin=(remote_col_obj == local_col_obj),
backref=backref_obj, post_update=post_update)
metadata = sa.MetaData()
group = sa.Table(
'group', metadata,
Column('group_id', Integer, primary_key=True)
)
class Group(object):
pass
sa.orm.mapper(Group, group)
thing = sa.Table(
'thing', metadata,
Column('thing_id', Integer, primary_key=True, autoincrement='ignore_fk'),
Column('favorite_group_id', Integer, nullable=True),
# Comment this out for remote_side to be correct
ForeignKeyConstraint(['favorite_group_id', 'thing_id'],
['rel.group_id', 'rel.thing_id'],
use_alter=True, name='foobar')
)
class Thing(object):
pass
rel = sa.Table(
'rel', metadata,
Column('thing_id', Integer,
ForeignKey('thing.thing_id'),
primary_key=True, nullable=False, index=True),
Column('group_id', Integer,
ForeignKey('group.group_id'),
primary_key=True, nullable=False, index=True),
mysql_engine='INNODB')
sa.orm.mapper(Thing, thing, properties = {
'groups' : orm.relationship(
Group, secondary=rel,
primaryjoin=(thing.c.thing_id == rel.c.thing_id),
# Uncommenting this get it wrong regardless of foreign keys
#remote_side=(rel.c.thing_id, rel.c.group_id),
# Why is this correct?
foreign_keys=[rel.c.thing_id, rel.c.group_id],
backref='things'),
'favorite_group' : _explicit_rel_to_parent(
parent_class=Group, parent_table=group,
child_table=thing, remote_col_name='group_id',
local_col_obj=thing.c.favorite_group_id,
post_update=True),
})
print "Thing's properties:"
for p in orm.class_mapper(Thing).iterate_properties:
if isinstance(p, orm.properties.RelationshipProperty):
print '%s:' % p.key
print ' remote_side = %r' % p.remote_side
print ' secondaryjoin = %s' % p.secondaryjoin
