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.
