Combine attributes from two objects in parent object

[Stephan Hügel]

I'd like to represent a Correspondent object, which can refer to two separate objects

Corr_1

- id

- email_address

Corr_2

- id

- email_address

Correspondent

- id

- corr_1.id

- corr_2.id

But I have two problems:

I'm using MySQL, so I can't use a check constraint to ensure that only one of the corr_*.id fields can be populated. Is it it a bad idea to enforce the constraint at the SQLAlchemy level, by doing 

if all(corr1.id, corr2.id) or not any(corr_1.id, corr2.id):

    raise …     

in my __init__?

My second problem relates to then being able to retrieve the related .email_address attribute. My naïve relationship above obviously won't work for that. Any pointers would be greatly appreciated.

-- 

steph

[Michael Bayer]

On May 25, 2012, at 4:53 PM, Stephan Hügel wrote:

I'd like to represent a Correspondent object, which can refer to two separate objects

Corr_1

- id

- email_address

Corr_2

- id

- email_address

Correspondent

- id

- corr_1.id

- corr_2.id

But I have two problems:

I'm using MySQL, so I can't use a check constraint to ensure that only one of the corr_*.id fields can be populated. 

Assuming corr_1 and corr_2 are two separate tables, if you have control over the schema of this application and you wish to have "email_address" be unique across the union of these two tables, the best practice here would be to have only one table of email addresses.   Otherwise you have to make due with what you're given.

Is it it a bad idea to enforce the constraint at the SQLAlchemy level, by doing 

if all(corr1.id, corr2.id) or not any(corr_1.id, corr2.id):

    raise …     

in my __init__?

the general idea is fine though I think you mean something more like this (note I'm using the exclusive or operator here though the all/any() approach would work too):

class Correspondent(Base):

    def __init__(self, corr_1, corr2):

        assert (corr_1 is None) ^ (coor_2 is None)

the important part is that when you create objects, the primary key/foreign key attributes like "id" and such aren't populated until a flush occurs, so we deal with references to the objects themselves.

My second problem relates to then being able to retrieve the related .email_address attribute. My naïve relationship above obviously won't work for that. Any pointers would be greatly appreciated.

usually @property achieves this:

class Correspondent(Base):

    @property

    def email_address(self):

        return self.corr_1.email_address if self.corr_1 else self.corr_2.email_address

[Stephan Hügel]

On Saturday, 26 May 2012 00:36:54 UTC+1, Michael Bayer wrote:

usually @property achieves this:

class Correspondent(Base):

    @property

    def email_address(self):

        return self.corr_1.email_address if self.corr_1 else self.corr_2.email_address

This worked perfectly, but I'm not sure how to query the email_address property with the correct join. I've created one-to-many relationships to Corr_1 and Corr_2, and If I do e.g. Correspondent.query.join(Corr_1).filter(Correspondent.email_address=="f...@bar.com").all(), it works fine. However, if I omit the join clause, I get an error:

Correspondent.query.filter(Correspondent.email_address=="f...@bar.com").all()

ArgumentError: filter() argument must be of type sqlalchemy.sql.ClauseElement or string

Is there a way to perform this query in a single step?