I'm still not sure I understand. Here's an example that does what I *think* you're asking for:
import sqlalchemy as sa import sqlalchemy.orm as saorm from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() pn = sa.Table( "pn", Base.metadata, sa.Column("post_id", sa.Integer, sa.ForeignKey("posts.id")), sa.Column("node_id", sa.Integer, sa.ForeignKey("nodes.id")), ) class Node(Base): __tablename__ = "nodes" id = sa.Column(sa.Integer, autoincrement=True, primary_key=True) project_uuid = sa.Column(sa.Text, nullable=False) topic = sa.Column(sa.Text(20), nullable=False) posts = saorm.relationship("Post", secondary=pn, backref="nodes") class Post(Base): __tablename__ = "posts" id = sa.Column(sa.Integer, autoincrement=True, primary_key=True) project_uuid = sa.Column(sa.Text, nullable=False) if __name__ == "__main__": engine = sa.create_engine("sqlite://") Base.metadata.create_all(bind=engine) Session = saorm.sessionmaker(bind=engine) projectid = "42a8e275-f153-452b-99d3-645bdd513ca9" topics = ['a', 'b', 'c', 'd'] nodes = [Node(project_uuid=projectid, topic=t) for t in topics] posts = [Post(project_uuid=projectid) for i in range(11)] import random random.seed(0) for node in nodes: node.posts = random.sample(posts, 4) session = Session() session.add_all(nodes + posts) session.flush() node_ids = [1, 2] print("Looking for posts with node IDs: %s" % node_ids) posts = ( session.query(Post) .join("nodes") .filter(Node.id.in_(node_ids)) .all() ) for p in posts: print("Post %s: Node IDs %s" % (p.id, [n.id for n in p.nodes])) Simon On Mon, Jun 10, 2019 at 1:22 PM Desmond Lim <limwen...@gmail.com> wrote: > > Hi Simon, > > The tables and sample data are below. > > So first I search the nodes table for a and b, getting the node_id of them > (in this example, 1 and 2). > > Then using the SQL statement, I would get all posts that have 1 and 2 as the > node_id. So in the example data, 100, 103, 108 will be returned as these > posts contain the nodes 1 and 2. > > Hope this helps. > > Desmond > > ------------- > > CREATE SEQUENCE IF NOT EXISTS nodes_id_seq; > > CREATE TABLE "public"."nodes" ( > "id" int8 NOT NULL DEFAULT nextval('nodes_id_seq'::regclass), > "project_uuid" uuid NOT NULL, > "topic" varchar(20) NOT NULL, > PRIMARY KEY ("id") > ); > > CREATE SEQUENCE IF NOT EXISTS posts_id_seq; > > > CREATE TABLE "public"."posts" ( > "id" int8 NOT NULL DEFAULT nextval('posts_id_seq'::regclass), > "project_uuid" uuid NOT NULL, > PRIMARY KEY ("id") > ); > > CREATE TABLE "public"."pn" ( > "post_id" int8 NOT NULL, > "node_id" int8 NOT NULL, > PRIMARY KEY ("post_id","node_id") > ); > > INSERT INTO "public"."nodes" ("id", "project_uuid", "topic", "size", > "given_id") VALUES > ('1', '42a8e275-f153-452b-99d3-645bdd513ca9', 'a'), > ('2', '42a8e275-f153-452b-99d3-645bdd513ca9', 'b'), > ('3', '42a8e275-f153-452b-99d3-645bdd513ca9', 'c'), > ('4', '42a8e275-f153-452b-99d3-645bdd513ca9', 'd') > > INSERT INTO "public"."posts" ("id", "project_uuid") VALUES > ('100', '42a8e275-f153-452b-99d3-645bdd513ca9'), > ('101', '42a8e275-f153-452b-99d3-645bdd513ca9'), > ('102', '42a8e275-f153-452b-99d3-645bdd513ca9'), > ('103', '42a8e275-f153-452b-99d3-645bdd513ca9'), > ('104', '42a8e275-f153-452b-99d3-645bdd513ca9'), > ('105', '42a8e275-f153-452b-99d3-645bdd513ca9'), > ('106', '42a8e275-f153-452b-99d3-645bdd513ca9'), > ('107', '42a8e275-f153-452b-99d3-645bdd513ca9'), > ('108', '42a8e275-f153-452b-99d3-645bdd513ca9'), > ('109', '42a8e275-f153-452b-99d3-645bdd513ca9'), > ('110', '42a8e275-f153-452b-99d3-645bdd513ca9') > > INSERT INTO "public"."pn" ("post_id", "node_id") VALUES > ('1', '100'), > ('2', '100'), > ('3', '100'), > ('2', '101'), > ('3', '101'), > ('4', '101'), > ('5', '101'), > ('3', '102'), > ('4', '102'), > ('1', '103'), > ('2', '103'), > ('3', '103'), > ('2', '104'), > ('4', '104'), > ('1', '105'), > ('4', '105'), > ('3', '105'), > ('3', '106'), > ('4', '106'), > ('4', '107'), > ('1', '108'), > ('2', '108'), > ('3', '109'), > ('1', '109'), > ('4', '109'), > ('2', '110'), > ('3', '110') > > ALTER TABLE "public"."posts" ADD FOREIGN KEY ("project_uuid") REFERENCES > "public"."projects"("uuid"); > ALTER TABLE "public"."pn" ADD FOREIGN KEY ("post_id") REFERENCES > "public"."posts"("id"); > ALTER TABLE "public"."pn" ADD FOREIGN KEY ("node_id") REFERENCES > "public"."nodes"("id"); > > On Mon, 10 Jun 2019 at 17:08, Simon King <si...@simonking.org.uk> wrote: >> >> Hi Desmond. >> >> I don't really understand your table structure. Could you present it >> in the form of a standalone script that we can run (including sample >> data)? >> >> Thanks, >> >> Simon >> >> On Mon, Jun 10, 2019 at 2:57 AM Desmond Lim <limwen...@gmail.com> wrote: >> > >> > 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. >> > >> > >> > >> > 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 sqlalchemy+unsubscr...@googlegroups.com. >> > To post to this group, send email to sqlalchemy@googlegroups.com. >> > 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. >> >> -- >> 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 post to this group, send email to sqlalchemy@googlegroups.com. >> 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/CAFHwexcmpOTTms0-A9QbiT_DTQey8e-Wzze4W5HoDXZdZ7eJWA%40mail.gmail.com. >> 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > 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%2BCzahCwZ7_yKbFTPvYX4kXTz1bZ%3DoTXGVw1g0eO26qeGBGpg%40mail.gmail.com. > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. 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/CAFHwexeWqM1m4aXWpuJnzfrUvgzBpq1JOVd_PJ1w-08TDTwzig%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.