self relationship via intermediate table

[kosta]

Hello everyone!

I've designed invitation model

class User(Base):

__tablename__ = 'user'

id = Column(Integer, primary_key=True)

name = Column(String(64))

email = Column(String(64))

class Invitation(Base):

__tablename__ = 'invitation'

id = Column(Integer, primary_key=True)

sender_id = Column(Integer, ForeignKey('user.id'))

invitee_id = Column(Integer, ForeignKey('user.id'), unique=True)

sender = relationship('User', foreign_keys=[sender_id], backref='invite_list')

invitee = relationship('User', foreign_keys=[invitee_id], backref='invited_by', uselist=False)

email = Column(String)

phone = Column(String)

token = Column(String)

My logic is:

1. Create a new record in Invitation table: sender_id - current user, email or phone and unique generated token.

2. Create a new record in User table keep received token, commit it.

3. Find a record in Invitation table by filter token and update filed invitee_id == new_user.id

My problem is backref return value for invited_by - return (of course) Invitation record. 

My question is whether I've possibility return for invited_by User record via Invitation table or not?

[Mike Bayer]

you can find any record via anything, what SQL would you like to emit please ?



>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> 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 sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[kosta]

Hello Mike, 

Thank you for your response!

First of all, I'm apologize, I have lack knowledge in sql. I guess my SQL should be as:

Get all users invited by specific user:

SELECT u.name AS "sender", i.name AS "invitee"

FROM invitation inv

LEFT JOIN user u ON inv.sender_id=u.id

LEFT JOIN user i ON inv.invitee_id=i.id

WHERE inv.sender_id=?;

Get user who invited specific user:

SELECT u.name AS "invited_by", i.name AS "invitee"

FROM invitation inv

LEFT JOIN user u ON inv.sender_id=u.id

LEFT JOIN user i ON inv.invitee_id=i.id

WHERE inv.invitee_id=?;

понедельник, 18 марта 2019 г., 17:48:26 UTC+3 пользователь Mike Bayer написал:

[Mike Bayer]

On Tue, Mar 19, 2019 at 10:23 AM kosta <naum...@gmail.com> wrote:
>
> Hello Mike,
> Thank you for your response!
>
>
> First of all, I'm apologize, I have lack knowledge in sql. I guess my SQL should be as:
> Get all users invited by specific user:
>
> SELECT u.name AS "sender", i.name AS "invitee"
> FROM invitation inv
> LEFT JOIN user u ON inv.sender_id=u.id
> LEFT JOIN user i ON inv.invitee_id=i.id
> WHERE inv.sender_id=?;
>
> Get user who invited specific user:
>
> SELECT u.name AS "invited_by", i.name AS "invitee"
> FROM invitation inv
> LEFT JOIN user u ON inv.sender_id=u.id
> LEFT JOIN user i ON inv.invitee_id=i.id
> WHERE inv.invitee_id=?;

here's the form of the first one and the second is basically the same idea:

from sqlalchemy.orm import aliased

u = aliased(User, "u")
i = aliased(User, "i")

q = session.query(
u.name.label("sender"),
i.name.label("invitee")
).select_from(Invitation).\
outerjoin(u, Invitation.sender).\
outerjoin(i, Invitation.invitee).\
filter(Invitation.sender_id=5)

[Konstantin Naumov]

Great, thank you!

I thought that I can use a field of relationship() - “invited_by" in User model for emitting the similar query, but I'm getting an empty list.

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/gv8QmGnl6lg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Mike Bayer]

On Fri, Mar 22, 2019 at 10:53 AM Konstantin Naumov <naum...@gmail.com> wrote:
>
> Great, thank you!
>
> I thought that I can use a field of relationship() - “invited_by" in User model for emitting the similar query, but I'm getting an empty list.

take a look at SQL being emitted with echo=True

[kosta]

Sure, thanks! 

пятница, 22 марта 2019 г., 19:39:06 UTC+3 пользователь Mike Bayer написал: