unnest is a fancy thing that isn't easily supported by SQLAlchemy right now. there's a recipe at https://github.com/sqlalchemy/sqlalchemy/issues/3566#issuecomment-441931333 which hopefully is still in working order, you'd have to work with that for now.
On Thu, Mar 12, 2020, at 8:31 PM, Shyam Sunder wrote: > Probably a simple question, but I am unable to translate the SQL to Query API > function calls. > > I'm using PostgreSQL 11 and SQLAlchemy 1.3. > > The raw SQL query I want to translate is this: > `SELECT s.post_id, s.signature, count(a.query) AS score FROM post_signature AS s, unnest(s.words, $1) AS a(word, query) WHERE a.word = a.query GROUP BY s.post_id ORDER BY score DESC LIMIT 100;` > > I know to start off the query with: > session.query(model.PostSignature) > > but I don't understand how to specify the unnest part in the FROM clause, or > to then count the unnested values in the SELECT clause. > > There's also very little documentation on sa.func.unnest() in the official > documentation website. Can anyone provide me some help or understanding? > > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/c057a6a4-a9ed-4e44-9f48-1f1bba1392d4%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/c057a6a4-a9ed-4e44-9f48-1f1bba1392d4%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/00bfd722-aa86-49cd-8e5a-aaa52266b641%40www.fastmail.com.
