On Tue, Nov 20, 2018 at 12:18 PM <[email protected]> wrote: > > 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 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. > > El martes, 20 de noviembre de 2018, 1:25:13 (UTC-5), Mike Bayer escribió: >> >> On Mon, Nov 19, 2018 at 11:02 PM <[email protected]> wrote: >> > >> > 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 text): >> >> 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()) >> >> >> >> >> > 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. >> > To unsubscribe from this group and stop receiving emails from it, send an >> > email to [email protected]. >> > To post to this group, send email to [email protected]. >> > 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
