Hi there,
I really have no idea how to do this via sqlalchemy. I have 2 tables:
class NodesModel(db.Model):
__tablename__ = 'nodes'
id = db.Column(db.BigInteger, primary_key=True)
topic = db.Column(db.String(20), nullable=False)
pn = relationship("PNModel", backref="nodes")
class PNModel(db.Model):
__tablename__ = 'pn'
post_id = db.Column(db.BigInteger, db.ForeignKey('posts.id'),
primary_key=True)
node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'),
primary_key=True)
I will get a list of nodes that have the topic (e.g. one, two). Then taking
the nodes, I'll select the PN that have both nodes (it can be 1 or more)
associated with it.
e.g.
[image: telegram-cloud-photo-size-5-6327616107510343927-x.jpg]
I'm doing it this way at this point:
nodes = NodesModel.find_by_topic_list(topic_list)
node_id_list = [node.id for node in nodes]
Then running this sql statement:
WITH pn_1 AS (SELECT post_id, ARRAY_AGG(DISTINCT node_id) AS nodes
FROM pn GROUP BY post_id),
nodes_1 AS (SELECT ARRAY_AGG(DISTINCT id) AS s_nodes
FROM pn WHERE node_id IN (dict(node_id_list))
SELECT DISTINCT post_id FROM pn_1
WHERE nodes_1 = (SELECT s_nodes FROM select_nodes);
There has to be a better and more elegant way to do this with SQLAlchemy
and I can't seem to figure it out. I've tried using subqueries and then I
realised that I'm actually calling the nodes table twice, which I shouldn't
have to.
Can anyone tell me how to use the nodes table to get the post_id form pn
(after which getting the post should be easy via ORM should be easy, I
think).
Thanks.
Desmond
--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAM%2BCzahgN2TF88p4__5W76DiYO3THD1tV2BgekvX913Mv6nU7Q%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
