[sqlalchemy] Setting join_depth on Query

[Alex Rothberg]

Following up 
on 
https://groups.google.com/forum/#!searchin/sqlalchemy/join_depth%7Csort:date/sqlalchemy/WstKKbEFaRo/hL910npaBQAJ
 
and 
https://stackoverflow.com/questions/4381712/how-do-you-dynamically-adjust-the-recursion-depth-for-eager-loading-in-the-sqlal,
 
is there any way to set the join_depth on the query object rather than on 
the relationship?

Right now I have:
class Geography(db.Model):
id = db.Column(UUID, default=uuid.uuid4, primary_key=True)
name = db.Column(db.String(), nullable=False, unique=True)
parent_geography_id = db.Column(UUID, db.ForeignKey(id))
children = db.relationship(
lambda: Geography,
lazy="joined",
join_depth=3,
backref=backref("parent", remote_side=[id]),
)

however if I would like to customize the join_depth on the query.

A related issue is that if I then take Geography.query.options(raiseload("*", 
sql_only=True)), the join_depth seems to be lost and I just get an 
exception. Also printing the query when the options is set shows that the 
join_depth is not used.

-- 
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] aggregate_order_by() on multiple columns

[pierre collet]

Thanks Mike for the trick :)

I have created an issue on Bitbucket as you asked here: 
https://bitbucket.org/zzzeek/sqlalchemy/issues/4337/aggregate_order_by-doesnt-accept-multiple
.

Cheers,
Pierre

On Wednesday, September 19, 2018 at 3:26:32 PM UTC+2, Mike Bayer wrote:
>
> On Wed, Sep 19, 2018 at 5:56 AM pierre collet  > wrote: 
> > 
> > Hello, 
> > 
> > I am using SQLAlchemy on a PostgreSQL database and I was very pleased to 
> find the aggregate_order_by function (http://docs.sqlalchemy.org/ccept 
> ten/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.aggregate_order_by).
>  
>
> > 
> > Though, I am wondering why the "order_by" argument of the function takes 
> only one column while Postgresql definitely accepts to order by a set of 
> columns: 
> > 
> > For instance: 
> > SELECT array_agg(status ORDER BY pk_col1, pk_col2) FROM 
> table_with_pk_on_two_columns GROUP BY col_x; 
> > works fine on my PostgreSQL 9.6 database. 
> > 
> > Am I missing something or is this simply not yet implemented? 
>
> order_by can be turned into a "*order_by" if you can help by adding a 
> bug report , for now you can send in a ClauseList: 
>
> >>> from sqlalchemy.dialects.postgresql import aggregate_order_by 
> >>> from sqlalchemy import column 
> >>> from sqlalchemy.dialects import postgresql 
> >>> from sqlalchemy.sql.expression import ClauseList 
> >>> elem = aggregate_order_by(column('a'), ClauseList(column('b').asc(), 
> column('c').desc())) 
> >>> print(elem.compile(dialect=postgresql.dialect())) 
> a ORDER BY b ASC, c DESC 
>
>
>
>
> > 
> > Thank you. 
> > Pierre. 
> > 
> > 
> > 
> > -- 
> > 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] aggregate_order_by() on multiple columns

[Mike Bayer]

On Wed, Sep 19, 2018 at 5:56 AM pierre collet  wrote:
>
> Hello,
>
> I am using SQLAlchemy on a PostgreSQL database and I was very pleased to find 
> the aggregate_order_by function (http://docs.sqlalchemy.org/ccept 
> ten/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.aggregate_order_by).
>
> Though, I am wondering why the "order_by" argument of the function takes only 
> one column while Postgresql definitely accepts to order by a set of columns:
>
> For instance:
> SELECT array_agg(status ORDER BY pk_col1, pk_col2) FROM 
> table_with_pk_on_two_columns GROUP BY col_x;
> works fine on my PostgreSQL 9.6 database.
>
> Am I missing something or is this simply not yet implemented?

order_by can be turned into a "*order_by" if you can help by adding a
bug report , for now you can send in a ClauseList:

>>> from sqlalchemy.dialects.postgresql import aggregate_order_by
>>> from sqlalchemy import column
>>> from sqlalchemy.dialects import postgresql
>>> from sqlalchemy.sql.expression import ClauseList
>>> elem = aggregate_order_by(column('a'), ClauseList(column('b').asc(), 
>>> column('c').desc()))
>>> print(elem.compile(dialect=postgresql.dialect()))
a ORDER BY b ASC, c DESC




>
> Thank you.
> Pierre.
>
>
>
> --
> 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] aggregate_order_by() on multiple columns

[pierre collet]

Hello,

I am using SQLAlchemy on a PostgreSQL database and I was very pleased to 
find the aggregate_order_by function (http://docs.sqlalchemy.org/ccept 
ten/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.aggregate_order_by
 

).

Though, I am wondering why the "order_by" argument of the function takes 
only one column while Postgresql definitely accepts to order by a set of 
columns:

For instance:
SELECT array_agg(status ORDER BY pk_col1, pk_col2) FROM 
table_with_pk_on_two_columns GROUP BY col_x;
works fine on my PostgreSQL 9.6 database.

Am I missing something or is this simply not yet implemented?

Thank you.
Pierre.



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