[sqlalchemy] Help translating query to sqlalchemy

Post by j***@facturedo.com
Hi everyone,

the ARRAY() call with a subquery inside of it is not really a standard
SQL syntax, it looks like you're trying to do a "nested select".
I've only known one proprietary database (Akiban, later FoundationDB)
that did that kind of query as a normal feature. I would assume in
this case you're on Postgresql.

Anyway, if you *really* need the SQL to be like that, you can use
sqlalchemy.dialects.postgresql.ARRAY with a scalar select inside of
it:

col = postgresql.ARRAY(select([t.c.title]).select_from(...).where(...).as_scalar())

Post by j***@facturedo.com
SELECT p.id, p.title,
(
SELECT ARRAY(SELECT t.title
FROM tags_posts pt
JOIN tags t ON t.id=pt.tag
WHERE pt.post = p.id)
) AS post_tags
FROM posts p
ORDER BY p.title;
Thanks for any guidance. I've been stuck on this for a couple of days.
--
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.
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 ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

j***@facturedo.com

2018-11-20 17:18:35 UTC

Thanks for the answer. The SQL does not need to be like that, my SQL
knowledge is basic and that was the solution a colleague gave me. I want to
get the posts with the tags in one query, but I couldn't figure a solution.

I tested what you suggested but I see my knowledge of sqlalchemy is still
not enough to solve this problem. I'll stick with the raw text solution
right now.

Many thanks for the help.

Post by j***@facturedo.com
Hi everyone,
I started with sqlalchemy a couple of weeks ago and I still have plenty

to learn. I'm trying to translate this query to the expression language,
the ARRAY() call with a subquery inside of it is not really a standard
SQL syntax, it looks like you're trying to do a "nested select".
I've only known one proprietary database (Akiban, later FoundationDB)
that did that kind of query as a normal feature. I would assume in
this case you're on Postgresql.
Anyway, if you *really* need the SQL to be like that, you can use
sqlalchemy.dialects.postgresql.ARRAY with a scalar select inside of
col =
postgresql.ARRAY(select([t.c.title]).select_from(...).where(...).as_scalar())

Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.

Post by j***@facturedo.com
---
You received this message because you are subscribed to the Google

Groups "sqlalchemy" group.

Post by j***@facturedo.com
To unsubscribe from this group and stop receiving emails from it, send

<javascript:>.

Post by j***@facturedo.com
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Mike Bayer

2018-11-20 17:37:28 UTC

if you're learning SQL then don't go anywhere near things like ARRAY.
Not because ARRAY is so "dangerous" but more because it is giving in
to the temptation to not think in terms of relational algebra. You
likely want a JOIN for this query:

SELECT p.id, p.title, t.title FROM
posts AS p JOIN post_tags AS pt ON p.id=pt.post
JOIN tags AS t ON pt.tag=t.id
ORDER BY p.title

I tested what you suggested but I see my knowledge of sqlalchemy is still not enough to solve this problem. I'll stick with the raw text solution right now.
Many thanks for the help.

Post by j***@facturedo.com
Hi everyone,

the ARRAY() call with a subquery inside of it is not really a standard
SQL syntax, it looks like you're trying to do a "nested select".
I've only known one proprietary database (Akiban, later FoundationDB)
that did that kind of query as a normal feature. I would assume in
this case you're on Postgresql.
Anyway, if you *really* need the SQL to be like that, you can use
sqlalchemy.dialects.postgresql.ARRAY with a scalar select inside of
col = postgresql.ARRAY(select([t.c.title]).select_from(...).where(...).as_scalar())

--
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.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

j***@facturedo.com

2018-11-20 21:50:02 UTC

Thanks. I initially tried with joins with queries similar to the one you
showed, but it returns many rows per post with each tag. I wanted to get
one post with the tags aggregated but I couldn't come up with a query that
works. I asked for help and they gave me that query I showed but couldn't
express it in sqlalchemy.

Right now I'll keep using raw sql with text, but I think I'll have to
paginate and limit, so maybe then I'll use the join you showed and
aggregate in python.

Post by j***@facturedo.com
Thanks for the answer. The SQL does not need to be like that, my SQL

knowledge is basic and that was the solution a colleague gave me. I want to
get the posts with the tags in one query, but I couldn't figure a solution.
if you're learning SQL then don't go anywhere near things like ARRAY.
Not because ARRAY is so "dangerous" but more because it is giving in
to the temptation to not think in terms of relational algebra. You
SELECT p.id, p.title, t.title FROM
posts AS p JOIN post_tags AS pt ON p.id=pt.post
JOIN tags AS t ON pt.tag=t.id
ORDER BY p.title

Post by j***@facturedo.com
I tested what you suggested but I see my knowledge of sqlalchemy is

still not enough to solve this problem. I'll stick with the raw text
solution right now.

Post by j***@facturedo.com
Many thanks for the help.
El martes, 20 de noviembre de 2018, 1:25:13 (UTC-5), Mike Bayer

Post by j***@facturedo.com
Hi everyone,
I started with sqlalchemy a couple of weeks ago and I still have

plenty to learn. I'm trying to translate this query to the expression
language, but I have not been able to get what I need (right now I'm using

Post by Mike Bayer
the ARRAY() call with a subquery inside of it is not really a standard
SQL syntax, it looks like you're trying to do a "nested select".
I've only known one proprietary database (Akiban, later FoundationDB)
that did that kind of query as a normal feature. I would assume in
this case you're on Postgresql.
Anyway, if you *really* need the SQL to be like that, you can use
sqlalchemy.dialects.postgresql.ARRAY with a scalar select inside of
col =

postgresql.ARRAY(select([t.c.title]).select_from(...).where(...).as_scalar())

days.

Post by j***@facturedo.com
--
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.

Post by j***@facturedo.com
---
You received this message because you are subscribed to the Google

Groups "sqlalchemy" group.