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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 [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.
>>
>> --
>> 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/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 [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%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 [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/CAFHwexeWqM1m4aXWpuJnzfrUvgzBpq1JOVd_PJ1w-08TDTwzig%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
