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. El martes, 20 de noviembre de 2018, 12:37:44 (UTC-5), Mike Bayer escribió: > > On Tue, Nov 20, 2018 at 12:18 PM <[email protected] <javascript:>> > 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] <javascript:>. > > To post to this group, send email to [email protected] > <javascript:>. > > 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.
