It's difficult to tell from your code what your intention is. Is the
relationship between Fact and Info meant to be many-to-many? And likewise
the relationship between Text and Info?
Forgetting SQLAlchemy for a moment, what is the SQL that you want to
produce?
Does the script below do what you want?
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
facts_info = sa.Table(
"facts_info",
Base.metadata,
sa.Column(
"fact_id", sa.Integer, sa.ForeignKey("fact.id"), primary_key=True
),
sa.Column(
"info_id", sa.Integer, sa.ForeignKey("info.id"), primary_key=True
),
)
info_text = sa.Table(
"info_text",
Base.metadata,
sa.Column(
"info_id", sa.Integer, sa.ForeignKey("info.id"), primary_key=True
),
sa.Column(
"text_id", sa.Integer, sa.ForeignKey("text.id"), primary_key=True
),
)
class Fact(Base):
__tablename__ = "fact"
id = sa.Column(sa.Integer, primary_key=True)
fact = sa.Column(sa.String(500), nullable=False, unique=True)
created_at = sa.Column(sa.DateTime)
updated_at = sa.Column(sa.DateTime)
info = saorm.relationship(
"Info", secondary=facts_info, back_populates="facts"
)
class Info(Base):
__tablename__ = "info"
id = sa.Column(sa.Integer, primary_key=True)
filename = sa.Column(sa.String(50))
format = sa.Column(sa.String(10))
facts = saorm.relationship(
"Fact", secondary=facts_info, back_populates="info"
)
text = saorm.relationship(
"Text", secondary=info_text, back_populates="info"
)
class Text(Base):
__tablename__ = "text"
id = sa.Column(sa.Integer, primary_key=True)
text = sa.Column(sa.String(1000))
# Relationships
info = saorm.relationship(
"Info", secondary=info_text, back_populates="text"
)
if __name__ == "__main__":
engine = sa.create_engine("sqlite://", echo=True)
Base.metadata.create_all(engine)
Session = saorm.sessionmaker(bind=engine)
session = Session()
# two facts
facts = [Fact(fact="factone"), Fact(fact="facttwo")]
# three infos, first two are associated with both facts, third is
# only linked to second fact
infos = [
Info(filename="infoone", facts=facts),
Info(filename="infotwo", facts=facts),
Info(filename="infothree", facts=facts[1:]),
]
# three texts, first two linked to first info instance, third
# linked to third info instance
texts = [
Text(text="textone", info=[infos[0]]),
Text(text="texttwo", info=[infos[0]]),
Text(text="textthree", info=[infos[2]]),
]
session.add_all(facts + infos + texts)
session.flush()
# Joining to both facts_info and info_text in the same query
# doesn't really make sense, because it would end up producing a
# cartesian product between those tables. Instead we'll use a
# subquery against facts_info to select the info ids we are
# interested in.
info_ids = (
session.query(facts_info.c.info_id)
.filter(facts_info.c.fact_id == 1)
)
query = (
session.query(Info, Text)
.filter(Info.id.in_(info_ids))
.join(Info.text)
)
# Note that this only outputs Info objects that have at least one
# text object associated with them. If you want to include Info
# objects without a related Text object, change the
# ".join(Info.text)" to ".outerjoin(Info.text)"
for (info, text) in query.all():
print("Info(filename=%r) Text(text=%r)" % (info.filename,
text.text))
Hope that helps,
Simon
On Mon, Aug 9, 2021 at 10:48 PM 'timbecks' via sqlalchemy <
sqlalchemy@googlegroups.com> wrote:
> I am trying to figure out the correct join query setup within SQLAlchemy,
> but I can't seem to get my head around it.
>
> I have the following table setup (simplified, I left out the non-essential
> fields):
>
> [image: Unbenannt.png]
>
> [image: Unbenannt2.png]
>
> The facts are associated to info, info is associated to text. Text and
> facts aren't directly associated.
>
> I would like to join them all together but can't figure out to do so.
>
> In this example I would like to get all instaces of "Info" that are
> associated to Fact.id = 1 and all "Text" instances that are associated to
> that "Info" instance. I came up with
> select(Info, Text)
> .join(facts_info)
> .join(Facts)
> .join(info_text)
> .join(Text)
> here(Facts.id ==1)
>
> But it obviously gives me an error.
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/59a98354-4ecf-4b4b-b76b-a8ad67aae207n%40googlegroups.com
> <https://groups.google.com/d/msgid/sqlalchemy/59a98354-4ecf-4b4b-b76b-a8ad67aae207n%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexcBXcY%2BcWnQQ3PE%2BY6ggVRhV2CAwB6kEri1b4U8vO4XNw%40mail.gmail.com.
