On Tue, Mar 19, 2019 at 10:23 AM kosta <naumo...@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)
>
>
> понедельник, 18 марта 2019 г., 17:48:26 UTC+3 пользователь Mike Bayer написал:
>>
>> On Sat, Mar 16, 2019 at 9:33 AM kosta <naum...@gmail.com> wrote:
>> >
>> > 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?
>>
>> 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.
>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
