I'm having trouble getting a self-referential table to alias correctly.
In the example below, Jack was given a family code of 'FOO.' He shares
this with his family member Jill, who enters it as her PromoCode.
RtidPromoCodeFamilyCode JackBARFOO JillFOO
This is my sqlalchemy class for the table:
class AffinionCode(MssqlBase):
__tablename__ = "AffinionCodes"
Rtid = Column(String, ForeignKey('MemberFacts.Rtid'), primary_key=True)
PromoCode = Column(String)
FamilyCode = Column(String)
ReferAFriendCode = Column(String)
family_code_usages = relationship("AffinionCode",
primaryjoin=PromoCode == FamilyCode,
foreign_keys=FamilyCode,
remote_side=PromoCode,
backref='family_source')
friend_code_usages = relationship("AffinionCode",
primaryjoin=PromoCode == ReferAFriendCode,
foreign_keys=ReferAFriendCode,
remote_side=PromoCode,
backref='friend_source')
@hybrid_property
def family_code_usage_count(self):
return len([_ for _ in self.family_code_usages])
@family_code_usage_count.expression
def family_code_usage_count(cls):
return select([func.count(AffinionCode.Rtid)]). \
where(AffinionCode.PromoCode ==
cls.FamilyCode).label('family_code_usage_count')
@hybrid_property
def friend_code_usage_count(self):
return len([_ for _ in self.friend_code_usages])
@friend_code_usage_count.expression
def friend_code_usage_count(cls):
return select([func.count(AffinionCode.Rtid)]). \
where(AffinionCode.PromoCode ==
cls.ReferAFriendCode).label('friend_code_usage_count')
The problem is that this is producing queries like:
SELECT TOP 10 [AffinionCodes]. [Rtid] AS [AffinionCodes_Rtid],
[AffinionCodes]. [PromoCode] AS [AffinionCodes_PromoCode],
[AffinionCodes]. [FamilyCode] AS [AffinionCodes_FamilyCode],
[AffinionCodes]. [ReferAFriendCode] AS [AffinionCodes_ReferAFriendCode],
(SELECT COUNT([AffinionCodes]. [Rtid]) AS count_1
FROM [AffinionCodes]
WHERE [AffinionCodes]. [PromoCode] = [AffinionCodes]. [FamilyCode]) AS
family_code_usagesFROM [AffinionCodes]WHERE (SELECT COUNT([AffinionCodes].
[Rtid]) AS count_1
FROM [AffinionCodes] AS [AffinionCodes]
WHERE [AffinionCodes]. [PromoCode] = [AffinionCodes]. [FamilyCode]) > 1;
when I need it alias, like so:
SELECT TOP 10 [AffinionCodes]. [Rtid] AS [AffinionCodes_Rtid],
[AffinionCodes]. [PromoCode] AS [AffinionCodes_PromoCode],
[AffinionCodes]. [FamilyCode] AS [AffinionCodes_FamilyCode],
[AffinionCodes]. [ReferAFriendCode] AS [AffinionCodes_ReferAFriendCode],
(SELECT COUNT([AffinionCodes1]. [Rtid]) AS count_1
FROM [AffinionCodes] AS [AffinionCodes1]
WHERE [AffinionCodes1]. [PromoCode] = [AffinionCodes]. [FamilyCode]) AS
family_code_usagesFROM [AffinionCodes]WHERE (SELECT COUNT([AffinionCodes1].
[Rtid]) AS count_1
FROM [AffinionCodes] AS [AffinionCodes1]
WHERE [AffinionCodes1]. [PromoCode] = [AffinionCodes]. [FamilyCode]) > 1;
Any help would be greatly appreciated, thanks!
--
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.
For more options, visit https://groups.google.com/d/optout.
