How do I use unnest and select using the Query API?

[Shyam Sunder]

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,

[Mike Bayer]

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.

--

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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c057a6a4-a9ed-4e44-9f48-1f1bba1392d4%40googlegroups.com.