date:20170502

Re: [sqlalchemy] Postgres Composite Columns

2017-05-02 Thread Zac Goldstein

Ah ok, sorry.  I'll give your code a closer look then.

On Tuesday, May 2, 2017 at 6:45:23 PM UTC-7, Mike Bayer wrote:
>
> Well, I just pasted the example from that email I wrote, it still seems 
> to work, and "typemap" is just a local variable to that example, nothing 
> is "deprecated" there since that isn't part of SQLAlchemy.   There is a 
> deprecated parameter named "typemap" that's part of the 
> sqlalchemy.text() construct, but that's not the "typemap" used in the 
> composite example from my email, that's just something that happens to 
> have the same name. 
>
>
>
> On 05/02/2017 05:01 PM, Zac Goldstein wrote: 
> > Thanks for your response Mike.  I submitted an issue to the 
> > sqlalchemy-utils github around the time I made my SO post.  Mostly 
> > though I'm interested in finding a way around using 'typemap', which is 
> > what's used in every implementation of composite columns I can find. 
> >   e.g. the code you have suggested in the past uses it as 
> > well https://groups.google.com/d/msg/sqlalchemy/f9BPVHfdvbg/M88ruLo6lzQJ 
> > 
> > On Tuesday, May 2, 2017 at 12:57:38 PM UTC-7, Mike Bayer wrote: 
> > 
> > just a note that I'm not sure there are sqlalchemy-utils people 
> here, 
> > you might want to try pinging Konsta via 
> > https://github.com/kvesteri/sqlalchemy-utils 
> >  
> > 
> > 
> > 
> > On 05/02/2017 02:10 PM, Zac Goldstein wrote: 
> >  > Hello, 
> >  > 
> >  > I'm having some difficulty implementing postgres composite 
> columns. 
> >  >   I've been working off of the sqlalchemy-utils implementation: 
> >  > 
> > 
> http://sqlalchemy-utils.readthedocs.io/en/latest/_modules/sqlalchemy_utils/types/pg_composite.html
>  
> > <
> http://sqlalchemy-utils.readthedocs.io/en/latest/_modules/sqlalchemy_utils/types/pg_composite.html>.
>  
>
> > 
> >  >   I've also looked at relevant posts here, but they all break 
> > down at 
> >  > the same point. 
> >  > 
> >  > Specifically, all the implementations I have found use 'typemap' 
> > in the 
> >  > ___getattr__ function, but 'typemap' is now deprecated, and I'm 
> > having 
> >  > trouble properly resolving attribute lookups.  More details in my 
> SO 
> >  > post: 
> > 
> http://stackoverflow.com/questions/43727685/sqlalchemy-postgres-composite-type-column
>  
> > <
> http://stackoverflow.com/questions/43727685/sqlalchemy-postgres-composite-type-column>.
>  
>
> > 
> >  > 
> >  > Thanks 
> >  > 
> >  > 
> >  > 
> >  > -- 
> >  > 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+...@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

[sqlalchemy] Re: LIMIT the number of children in relationship dynamically

2017-05-02 Thread Isaac Martin

Yep, I ended up doing exactly what you describe. Thanks again!
>
>

-- 
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.

Re: [sqlalchemy] Postgres Composite Columns

2017-05-02 Thread Zac Goldstein

Thanks for your response Mike.  I submitted an issue to the 
sqlalchemy-utils github around the time I made my SO post.  Mostly though 
I'm interested in finding a way around using 'typemap', which is what's 
used in every implementation of composite columns I can find.  e.g. the 
code you have suggested in the past uses it as 
well https://groups.google.com/d/msg/sqlalchemy/f9BPVHfdvbg/M88ruLo6lzQJ

On Tuesday, May 2, 2017 at 12:57:38 PM UTC-7, Mike Bayer wrote:
>
> just a note that I'm not sure there are sqlalchemy-utils people here, 
> you might want to try pinging Konsta via 
> https://github.com/kvesteri/sqlalchemy-utils 
>
>
>
> On 05/02/2017 02:10 PM, Zac Goldstein wrote: 
> > Hello, 
> > 
> > I'm having some difficulty implementing postgres composite columns. 
> >   I've been working off of the sqlalchemy-utils implementation: 
> > 
> http://sqlalchemy-utils.readthedocs.io/en/latest/_modules/sqlalchemy_utils/types/pg_composite.html.
>  
>
> >   I've also looked at relevant posts here, but they all break down at 
> > the same point. 
> > 
> > Specifically, all the implementations I have found use 'typemap' in the 
> > ___getattr__ function, but 'typemap' is now deprecated, and I'm having 
> > trouble properly resolving attribute lookups.  More details in my SO 
> > post: 
> http://stackoverflow.com/questions/43727685/sqlalchemy-postgres-composite-type-column.
>  
>
> > 
> > Thanks 
> > 
> > 
> > 
> > -- 
> > 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.

Re: [sqlalchemy] Can I get joined loading on a relationship to work from child to parent?

2017-05-02 Thread mike bayer




On 05/02/2017 02:59 PM, Randy Syring wrote:
|Is there any way to get relationship joined loading to work from child 
back up to the parent?|


in this case, since you have eager loaders configured, you can call 
refresh() on "c" which will unexpire the object since the commit() and 
also fire off configured eager loaders:


session.refresh(c)


without calling refresh(), the object makes use of the default object 
refresh which currently does not make use of eager loaders.   The 
behavior by which eager loaders fire off for a plain refresh is under 
review at https://gerrit.sqlalchemy.org/#/c/389/ / ticket 1763 for a 
possible inclusion in SQLAlchemy 1.3.


The 1.3 feature needs a lot more thought needs to be put into it. 
Namely, not every eager load strategy is appropriate for a single-row 
load (e.g. subquery loading, which is optimized for loading lots of 
relationships at once), suggesting that all eager loaders should detect 
that they are being called in the "refresh" case and adjust accordingly. 
 Also, the use case of "query(Class)" where lots of things are eager 
loaded is not necessarily the same use case as the access of attributes 
after a commit(); so I think the feature for ticket 1763 should come 
turned off by default and include lots of configurability (some thoughts 
on that in the ticket).










|
fromsqlalchemy importcreate_engine,Column,Integer,String,ForeignKey
fromsqlalchemy.orm importrelationship,sessionmaker
fromsqlalchemy.ext.declarative importdeclarative_base

engine =create_engine('sqlite:///:memory:')
Base=declarative_base()


classGrandParent(Base):
 __tablename__ ='grands'

 id =Column(Integer,primary_key=True)
 name =Column(String())


classParent(Base):
 __tablename__ ='parents'

 gp_id 
=Column(ForeignKey(GrandParent.id,ondelete='cascade'),nullable=False)

 gp =relationship(GrandParent,lazy='joined',innerjoin=True)

 id =Column(Integer,primary_key=True)
 name =Column(String())


classChild(Base):
 __tablename__ ='childs'

 parent_id 
=Column(ForeignKey(Parent.id,ondelete='cascade'),nullable=False)

 parent =relationship(Parent,lazy='joined',innerjoin=True)

 id =Column(Integer,primary_key=True)
 name =Column(String())


Base.metadata.create_all(engine)
engine.echo =True

Session=sessionmaker(bind=engine)
session =Session()

gp =GrandParent(name='foo')
p =Parent(name='bar',gp=gp)
c =Child(name='baz',parent=p)

session.add(c)
session.commit()

# How many SQL statements?  Why not 1 that would load Child, it's 
parent, and it's grand parent?

assertc.parent.gp.id
|

As the last comment says, any way to get that last statement to issue 
one join with all the inner joins to load the parent objects?


--
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.

Re: [sqlalchemy] Postgres Composite Columns

2017-05-02 Thread mike bayer

just a note that I'm not sure there are sqlalchemy-utils people here, 
you might want to try pinging Konsta via 
https://github.com/kvesteri/sqlalchemy-utils




On 05/02/2017 02:10 PM, Zac Goldstein wrote:

Hello,

I'm having some difficulty implementing postgres composite columns. 
  I've been working off of the sqlalchemy-utils implementation: 
http://sqlalchemy-utils.readthedocs.io/en/latest/_modules/sqlalchemy_utils/types/pg_composite.html. 
  I've also looked at relevant posts here, but they all break down at 
the same point.


Specifically, all the implementations I have found use 'typemap' in the 
___getattr__ function, but 'typemap' is now deprecated, and I'm having 
trouble properly resolving attribute lookups.  More details in my SO 
post: http://stackoverflow.com/questions/43727685/sqlalchemy-postgres-composite-type-column.


Thanks



--
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.

[sqlalchemy] Can I get joined loading on a relationship to work from child to parent?

2017-05-02 Thread Randy Syring

Is there any way to get relationship joined loading to work from child back 
up to the parent?

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///:memory:')
Base = declarative_base()


class GrandParent(Base):
__tablename__ = 'grands'

id = Column(Integer, primary_key=True)
name = Column(String())


class Parent(Base):
__tablename__ = 'parents'

gp_id = Column(ForeignKey(GrandParent.id, ondelete='cascade'), nullable=
False)
gp = relationship(GrandParent, lazy='joined', innerjoin=True)

id = Column(Integer, primary_key=True)
name = Column(String())


class Child(Base):
__tablename__ = 'childs'

parent_id = Column(ForeignKey(Parent.id, ondelete='cascade'), nullable=
False)
parent = relationship(Parent, lazy='joined', innerjoin=True)

id = Column(Integer, primary_key=True)
name = Column(String())


Base.metadata.create_all(engine)
engine.echo = True

Session = sessionmaker(bind=engine)
session = Session()

gp = GrandParent(name='foo')
p = Parent(name='bar', gp=gp)
c = Child(name='baz', parent=p)

session.add(c)
session.commit()

# How many SQL statements?  Why not 1 that would load Child, it's parent, 
and it's grand parent?
assert c.parent.gp.id

As the last comment says, any way to get that last statement to issue one 
join with all the inner joins to load the parent objects?

-- 
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] Postgres Composite Columns

2017-05-02 Thread Zac Goldstein

Hello,

I'm having some difficulty implementing postgres composite columns.  I've 
been working off of the sqlalchemy-utils implementation: 
http://sqlalchemy-utils.readthedocs.io/en/latest/_modules/sqlalchemy_utils/types/pg_composite.html.
 
 I've also looked at relevant posts here, but they all break down at the 
same point.

Specifically, all the implementations I have found use 'typemap' in the 
___getattr__ function, but 'typemap' is now deprecated, and I'm having 
trouble properly resolving attribute lookups.  More details in my SO 
post: 
http://stackoverflow.com/questions/43727685/sqlalchemy-postgres-composite-type-column.

Thanks



-- 
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.

Re: [sqlalchemy] LIMIT the number of children in relationship dynamically

2017-05-02 Thread mike bayer




On 05/02/2017 12:38 AM, Isaac Martin wrote:
Thank you very much for your response. This solution isn't working for 
me, and I'm 99% sure I'm not translating what you've written into 
something that works for my use case. My situation is somewhat more 
complicated than I initially wrote. My first question was in the vain 
hope that there was some easy way using declarative to tell anything 
loaded through a relationship() to load with a LIMIT. Obviously that 
isn't the case.


we can absolutely do that:


def message_join():
subq = select([Messages.date]).\
where(Messages.user_id == User.id).\
order_by(Messages.date.desc()).\
limit(1).offset(10).correlate(User).as_scalar()

return and_(User.id == Messages.user_id, Messages.date > subq)


class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
messages = relationship(
'Messages', order_by='desc(Messages.date)',
primaryjoin=message_join)


class Messages(Base):
__tablename__ = 'message'
id = Column(Integer, primary_key=True)
user_id = Column(ForeignKey('user.id'))
date = Column(Date)



done, works great with joinedload(), lazy loading, etc:

q = s.query(User).options(joinedload(User.messages))





In my situation, I have a relationship which I have defined by hand. 
There are not foreign keys between the messages and the objects being 
loaded (Connections). When I try to use your lateral solution I get an 
error thrown that there are no foreign key relationships, which is true.


Here is my model edited down to what I believe are the minimal relevant 
fields. I'm trying to return Connections with a limited number of 
objects in the Connection.messages field:


/class Connection(Base):/
/__tablename__ = 'Connection'/
/
/
/user_1_id = Column(Integer, ForeignKey('User.uid'))/
/user_2_id = Column(Integer, ForeignKey('User.uid'))/
/messages =  relationship('Message',/
/ secondary="join(MessageRecipient, Message, 
MessageRecipient.message_id == Message.uid)",/
/ primaryjoin= 'or_(Connection.user_1_id == 
MessageRecipient.recipient_id,'/
/   'Connection.user_2_id == 
MessageRecipient.recipient_id)',/

/ order_by="Message.created.desc()")/
/
/
/class MessageRecipient(Base):/
/__tablename__ = 'MessageRecipient'/
/
/
/recipient_id = Column(Integer, ForeignKey('User.uid'))/
/
/
/class Message(Base):/
/__tablename__ = 'Message'/
/
/
/created = Column(DateTime, default=func.current_timestamp())/
/body = Column(String(2000))
/
/
/

Now, I'm using Postgres, so I did attempt to translate your lateral 
example:


/subq = Message.query.\/
/filter(and_(Message.uid == MessageRecipient.message_id, 
or_(MessageRecipient.uid == Connection.user_1_id,/
/MessageRecipient.uid == 
Connection.user_2_id))).\/

/order_by(Message.created.desc()).limit(1).subquery().lateral()/
/
/
/q = Connection.query.outerjoin(subq). \/
/options(contains_eager(Connection.messages, alias=subq))/
/
/
The above throws an error:
Can't find any foreign key relationships between 'Connection' and 
'%(140122530861688 anon)s'.


I also tried your non-lateral example
/subq = self.db.session.query(Message.created). \/
/filter(and_(Message.uid == MessageRecipient.message_id, 
or_(MessageRecipient.recipient_id == Connection.user_1_id,/
/MessageRecipient.recipient_id == 
Connection.user_2_id))).\/

/order_by(Message.created.desc()). \/
/limit(1).offset(10).correlate(Connection).as_scalar()/
/
/
/q = self.db.session.query(Connection).join(/
/MessageRecipient,/
/or_(MessageRecipient.recipient_id == Connection.user_1_id,/
/MessageRecipient.recipient_id == Connection.user_2_id)).\/
/join( Message,/
/and_(MessageRecipient.message_id == Message.uid, 
Message.created > subq)/

/).options(contains_eager(Connection.messages)).all()/
/
/
The above does not throw an error, but it also does not return any 
entities. I'm going to keep working on the non-lateral example because I 
don't understand your example well enough to confidently say I'm being 
faithful to it. If you have other advice based on this new information 
then I'd really appreciate it.  If I figure out what I was doing wrong 
I'll post here for posterity.


On Mon, May 1, 2017 at 7:34 PM, mike bayer > wrote:




On 05/01/2017 08:05 PM, Isaac Martin wrote:


I am building an api which can return children of resources if
the user requests it. For example, |user| has |messages|. I want
the query to be able to limit the number of |message| objects
that are returned.

Re: [sqlalchemy] LIMIT the number of children in relationship dynamically

2017-05-02 Thread Isaac Martin

Well that was a very silly mistake. End of the day oversight is what
happened here. I didn't notice that "offset" was set to 10 and my unit test
only had 2 values. When I set offset to 0 it worked great.

Sincerest thanks for this! I hadn't seen as_scalar used before. I'm looking
forward to trying subqueries like this myself in the future.

On Mon, May 1, 2017 at 9:38 PM, Isaac Martin  wrote:

> Thank you very much for your response. This solution isn't working for me,
> and I'm 99% sure I'm not translating what you've written into something
> that works for my use case. My situation is somewhat more complicated than
> I initially wrote. My first question was in the vain hope that there was
> some easy way using declarative to tell anything loaded through a
> relationship() to load with a LIMIT. Obviously that isn't the case.
>
> In my situation, I have a relationship which I have defined by hand. There
> are not foreign keys between the messages and the objects being loaded
> (Connections). When I try to use your lateral solution I get an error
> thrown that there are no foreign key relationships, which is true.
>
> Here is my model edited down to what I believe are the minimal relevant
> fields. I'm trying to return Connections with a limited number of objects
> in the Connection.messages field:
>
> *class Connection(Base):*
> *__tablename__ = 'Connection'*
>
> *user_1_id = Column(Integer, ForeignKey('User.uid'))*
> *user_2_id = Column(Integer, ForeignKey('User.uid'))*
> *messages =  relationship('Message',*
> * secondary="join(MessageRecipient, Message,
> MessageRecipient.message_id == Message.uid)",*
> * primaryjoin= 'or_(Connection.user_1_id ==
> MessageRecipient.recipient_id,'*
> *   'Connection.user_2_id ==
> MessageRecipient.recipient_id)',*
> * order_by="Message.created.desc()")*
>
> *class MessageRecipient(Base):*
> *__tablename__ = 'MessageRecipient'*
>
> *recipient_id = Column(Integer, ForeignKey('User.uid'))*
>
> *class Message(Base):*
> *__tablename__ = 'Message'*
>
> *created = Column(DateTime, default=func.current_timestamp())*
>
> *body = Column(String(2000))*
>
>
> Now, I'm using Postgres, so I did attempt to translate your lateral
> example:
>
> *subq = Message.query.\*
> *filter(and_(Message.uid == MessageRecipient.message_id,
> or_(MessageRecipient.uid == Connection.user_1_id,*
> *MessageRecipient.uid ==
> Connection.user_2_id))).\*
> *
> order_by(Message.created.desc()).limit(1).subquery().lateral()*
>
> *q = Connection.query.outerjoin(subq). \*
> *options(contains_eager(Connection.messages, alias=subq))*
>
> The above throws an error:
> Can't find any foreign key relationships between 'Connection' and
> '%(140122530861688 anon)s'.
>
> I also tried your non-lateral example
> *subq = self.db.session.query(Message.created). \*
> *filter(and_(Message.uid == MessageRecipient.message_id,
> or_(MessageRecipient.recipient_id == Connection.user_1_id,*
> *MessageRecipient.recipient_id ==
> Connection.user_2_id))).\*
> *order_by(Message.created.desc()). \*
> *limit(1).offset(10).correlate(Connection).as_scalar()*
>
> *q = self.db.session.query(Connection).join(*
> *MessageRecipient,*
> *or_(MessageRecipient.recipient_id == Connection.user_1_id,*
> *MessageRecipient.recipient_id == Connection.user_2_id)).\*
> *join( Message,*
> *and_(MessageRecipient.message_id == Message.uid,
> Message.created > subq)*
> *).options(contains_eager(Connection.messages)).all()*
>
> The above does not throw an error, but it also does not return any
> entities. I'm going to keep working on the non-lateral example because I
> don't understand your example well enough to confidently say I'm being
> faithful to it. If you have other advice based on this new information then
> I'd really appreciate it.  If I figure out what I was doing wrong I'll post
> here for posterity.
>
> On Mon, May 1, 2017 at 7:34 PM, mike bayer 
> wrote:
>
>>
>>
>> On 05/01/2017 08:05 PM, Isaac Martin wrote:
>>
>>>
>>> I am building an api which can return children of resources if the user
>>> requests it. For example, |user| has |messages|. I want the query to be
>>> able to limit the number of |message| objects that are returned.
>>>
>>> I found a useful tip about limiting the number of objects in child
>>> collections here >> tions/9148316/how-to-limit-offset-sqlalchemy-orm-relations-
>>> result?noredirect=1=1>. Basically, it indicates the following flow:
>>>
>>> |classUser(...):# ...messages =relationship('Messages',order
>>> _by='desc(Messages.date)',lazy='dynamic')user

Re: [sqlalchemy] Postgres Composite Columns

[sqlalchemy] Re: LIMIT the number of children in relationship dynamically

Re: [sqlalchemy] Postgres Composite Columns

Re: [sqlalchemy] Can I get joined loading on a relationship to work from child to parent?

Re: [sqlalchemy] Postgres Composite Columns

[sqlalchemy] Can I get joined loading on a relationship to work from child to parent?

[sqlalchemy] Postgres Composite Columns

Re: [sqlalchemy] LIMIT the number of children in relationship dynamically

Re: [sqlalchemy] LIMIT the number of children in relationship dynamically

9 matches

Site Navigation

Mail list logo

Footer information