Re: [sqlalchemy] Writing ORM from a complex sql statement
Does something like this work?
query = db.session.query(PostsModel).join(pn, PostsModel.id ==
pn.c.post_id).filter(pn.c.pn_nodes == db.session.query(n.c.n_nodes))
I can't help feeling this is a very inefficient query though. I still
don't really understand your data model (I don't understand where
topics come into this). But if you've got a many-to-many relationship
between posts and nodes, and you want to find posts that are linked to
*all* of a set of nodes, perhaps you could start with something like
this:
SELECT post_id
FROM pn
WHERE node_id IN (...)
GROUP BY post_id
HAVING count(node_id) =
Without the HAVING clause, that query would return all the posts that
match at least one of the nodes. The HAVING clause ensures that the
posts match *all* of the nodes.
Simon
On Tue, Jun 11, 2019 at 3:51 AM Desmond Lim wrote:
>
> Okay, I have ORMed my sql statement:
>
> pn = db.session.query(PNModel.post_id,
> db.func.array_agg(PNModel.node_id.distinct()).label('pn_nodes')).group_by(PNModel.post_id)
> n =
> db.session.query(db.func.array_agg(NodesModel.id.distinct()).label('n_nodes')).filter(NodesModel.project_uuid
> ==
> project_uuid).filter(NodesModel.topic.in_(topic_list)).subquery()
> query = db.session.query(pn.c.post_id).filter(pn.c.pn_nodes ==
> db.session.query(n.c.n_nodes))
> results = query.all()
>
> This returns what my original SQL returned. My next step is to get all the
> posts of the post_id in the list
>
> post_id_list = [result[0] for result in results]
> posts = PostsModel.find_by_id_list(id_list=post_id_list)
>
> I'm just wondering, if there is an easy way to get the post object from the
> query instead of doing the above.
>
> Desmond
>
>
> On Tue, 11 Jun 2019 at 08:30, Desmond Lim wrote:
>>
>> Hi Simon,
>>
>> Sorry I think I really didn't make what I'm asking for clear.
>>
>> I have these codes
>>
>> nodes =
>> NodesModel.find_by_project_uuid_and_topic_list(project_uuid=project_uuid,
>> topic_list=node_dict['topics'])
>> node_id_list = [node.id for node in nodes]
>>
>> select_sql = text('WITH pn_o AS (SELECT post_id, ARRAY_AGG(DISTINCT node_id)
>> AS pn_nodes FROM pn GROUP BY post_id), '
>> 'n_o AS (SELECT ARRAY_AGG(DISTINCT node_id) as n_nodes
>> FROM pn WHERE node_id IN :input_tuple) '
>> 'SELECT post_id FROM pn_o WHERE pn_nodes = (SELECT n_nodes
>> FROM n_o)')
>>
>> params = {"input_tuple": tuple(node_id_list)}
>> results = db.session.execute(select_sql, params).fetchall()
>>
>> Which does what I want. In the end, in results, I get a list of posts that
>> have nodes that contain all the topics I need, in other words, the posts
>> contain all the topic words.
>>
>> I'm just wondering, if there is a way to ORM the select_sql as I would
>> prefer to work with the models than the raw SQL. And I'm doing it this way
>> because I want the server to do the heavy lifting.
>>
>> Desmond
>>
>> On 10 Jun 2019, 22:41 +0800, Simon King , wrote:
>>
>> 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 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
Re: [sqlalchemy] Writing ORM from a complex sql statement
Okay, I have ORMed my sql statement:
pn = db.session.query(PNModel.post_id,
db.func.array_agg(PNModel.node_id.distinct()).label('pn_nodes')).group_by(PNModel.post_id)
n =
db.session.query(db.func.array_agg(NodesModel.id.distinct()).label('n_nodes')).filter(NodesModel.project_uuid
==
project_uuid).filter(NodesModel.topic.in_(topic_list)).subquery()
query = db.session.query(pn.c.post_id).filter(pn.c.pn_nodes ==
db.session.query(n.c.n_nodes))
results = query.all()
This returns what my original SQL returned. My next step is to get all the
posts of the post_id in the list
post_id_list = [result[0] for result in results]
posts = PostsModel.find_by_id_list(id_list=post_id_list)
I'm just wondering, if there is an easy way to get the post object from the
query instead of doing the above.
Desmond
On Tue, 11 Jun 2019 at 08:30, Desmond Lim wrote:
> Hi Simon,
>
> Sorry I think I really didn't make what I'm asking for clear.
>
> I have these codes
>
> nodes =
> NodesModel.find_by_project_uuid_and_topic_list(project_uuid=project_uuid,
> topic_list=node_dict['topics'])
> node_id_list = [node.id for node in nodes]
>
> select_sql = text('WITH pn_o AS (SELECT post_id, ARRAY_AGG(DISTINCT
> node_id) AS pn_nodes FROM pn GROUP BY post_id), '
> 'n_o AS (SELECT ARRAY_AGG(DISTINCT node_id) as n_nodes
> FROM pn WHERE node_id IN :input_tuple) '
> 'SELECT post_id FROM pn_o WHERE pn_nodes = (SELECT
> n_nodes FROM n_o)')
>
> params = {"input_tuple": tuple(node_id_list)}
> results = db.session.execute(select_sql, params).fetchall()
>
> Which does what I want. In the end, in results, I get a list of posts that
> have nodes that contain all the topics I need, in other words, the posts
> contain all the topic words.
>
> I'm just wondering, if there is a way to ORM the select_sql as I would
> prefer to work with the models than the raw SQL. And I'm doing it this way
> because I want the server to do the heavy lifting.
>
> Desmond
>
> On 10 Jun 2019, 22:41 +0800, Simon King , wrote:
>
> 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 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',
Re: [sqlalchemy] Writing ORM from a complex sql statement
Hi Simon,
Sorry I think I really didn't make what I'm asking for clear.
I have these codes
nodes =
NodesModel.find_by_project_uuid_and_topic_list(project_uuid=project_uuid,
topic_list=node_dict['topics'])
node_id_list = [node.id for node in nodes]
select_sql = text('WITH pn_o AS (SELECT post_id, ARRAY_AGG(DISTINCT
node_id) AS pn_nodes FROM pn GROUP BY post_id), '
'n_o AS (SELECT ARRAY_AGG(DISTINCT node_id) as n_nodes
FROM pn WHERE node_id IN :input_tuple) '
'SELECT post_id FROM pn_o WHERE pn_nodes = (SELECT
n_nodes FROM n_o)')
params = {"input_tuple": tuple(node_id_list)}
results = db.session.execute(select_sql, params).fetchall()
Which does what I want. In the end, in results, I get a list of posts that
have nodes that contain all the topics I need, in other words, the posts
contain all the topic words.
I'm just wondering, if there is a way to ORM the select_sql as I would
prefer to work with the models than the raw SQL. And I'm doing it this way
because I want the server to do the heavy lifting.
Desmond
On 10 Jun 2019, 22:41 +0800, Simon King , wrote:
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 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
Re: [sqlalchemy] Writing ORM from a complex sql statement
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 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 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 wrote:
>> >
>> > Hi there,
>> >
>> > I really have no idea how to do this via sqlalchemy. I have 2 tables:
>> >
>> > class NodesModel(db.Model):
>> >
Re: [sqlalchemy] Writing ORM from a complex sql statement
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 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 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"
Re: [sqlalchemy] Writing ORM from a complex sql statement
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 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.
