OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

How to query from a joined table in SQLAlchemy

  • Thread starter Thread starter Peter Kronenberg
  • Start date Start date
P

Peter Kronenberg

Guest
I have 2 tables with a many-to-many relationship, so I have a join table between them. They are defined like this

Code:
from sqlalchemy import String, Integer, Boolean
from sqlalchemy.orm import mapped_column, relationship

from root.db.ModelBase import ModelBase


class Module(ModelBase):
    __tablename__ = 'module'
    pk = mapped_column(Integer, primary_key=True)
    description = mapped_column(String)
    is_active = mapped_column(Boolean)
    name = mapped_column(String, unique=True, nullable=False)
    ngen_cal_active = mapped_column(String)
    groups = relationship("ModuleGroup", secondary="module_group_member", back_populates="modules", lazy="joined")
---

from sqlalchemy import String, Integer, Boolean
from sqlalchemy.orm import mapped_column, relationship

from root.db.ModelBase import ModelBase


class ModuleGroup(ModelBase):
    __tablename__ = 'module_group'
    pk = mapped_column(Integer, primary_key=True)
    description = mapped_column(String)
    is_active = mapped_column(Boolean)
    name = mapped_column(String, unique=True, nullable=False)
    modules = relationship("Module", secondary="module_group_member", back_populates="groups", lazy="joined")
---

from sqlalchemy import String, Integer, Boolean, ForeignKey
from sqlalchemy.orm import mapped_column

from root.db.ModelBase import ModelBase


class ModuleGroupMember(ModelBase):
    __tablename__ = 'module_group_member'
    description = mapped_column(String)
    is_active = mapped_column(Boolean)
    module_pk = mapped_column(ForeignKey('module.pk'), primary_key=True,)
    module_group_pk = mapped_column(ForeignKey('module_group.pk'), primary_key=True)

If I query like this

Code:
query = select(Module).where(Module.name == 'Module3')  # type: ignore
results = session.execute(query).unique().all()
print('results', results)

It appears to work (although I don't know why it forces me to use unique() since there should only be 1 result) In the result, I see a groups object, which is defined by the relationship, so it all works fine.

However, if I want to get all of my modules, and just return the name and the associated groups, I tried something like this:

Code:
query = select(Module.name, Module.groups).where(Module.name == 'Module3') # type: ignore

and I get this error SAWarning: SELECT statement has a cartesian product between FROM element(s) "module_group", "module_group_member_1" and FROM element "module". Apply join condition(s) between each element to resolve.

I'm not sure what the problem is. I thought that SQLAlchemy would take care of any JOINS that need to be done
<p>I have 2 tables with a many-to-many relationship, so I have a join table between them. They are defined like this</p>
<pre><code>from sqlalchemy import String, Integer, Boolean
from sqlalchemy.orm import mapped_column, relationship

from root.db.ModelBase import ModelBase


class Module(ModelBase):
__tablename__ = 'module'
pk = mapped_column(Integer, primary_key=True)
description = mapped_column(String)
is_active = mapped_column(Boolean)
name = mapped_column(String, unique=True, nullable=False)
ngen_cal_active = mapped_column(String)
groups = relationship("ModuleGroup", secondary="module_group_member", back_populates="modules", lazy="joined")
---

from sqlalchemy import String, Integer, Boolean
from sqlalchemy.orm import mapped_column, relationship

from root.db.ModelBase import ModelBase


class ModuleGroup(ModelBase):
__tablename__ = 'module_group'
pk = mapped_column(Integer, primary_key=True)
description = mapped_column(String)
is_active = mapped_column(Boolean)
name = mapped_column(String, unique=True, nullable=False)
modules = relationship("Module", secondary="module_group_member", back_populates="groups", lazy="joined")
---

from sqlalchemy import String, Integer, Boolean, ForeignKey
from sqlalchemy.orm import mapped_column

from root.db.ModelBase import ModelBase


class ModuleGroupMember(ModelBase):
__tablename__ = 'module_group_member'
description = mapped_column(String)
is_active = mapped_column(Boolean)
module_pk = mapped_column(ForeignKey('module.pk'), primary_key=True,)
module_group_pk = mapped_column(ForeignKey('module_group.pk'), primary_key=True)
</code></pre>
<p>If I query like this</p>
<pre><code>query = select(Module).where(Module.name == 'Module3') # type: ignore
results = session.execute(query).unique().all()
print('results', results)
</code></pre>
<p>It appears to work (although I don't know why it forces me to use <code>unique()</code> since there should only be 1 result)
In the result, I see a <code>groups</code> object, which is defined by the relationship, so it all works fine.</p>
<p>However, if I want to get all of my modules, and just return the name and the associated groups, I tried something like this:</p>
<pre><code>query = select(Module.name, Module.groups).where(Module.name == 'Module3') # type: ignore
</code></pre>
<p>and I get this error <code>SAWarning: SELECT statement has a cartesian product between FROM element(s) "module_group", "module_group_member_1" and FROM element "module". Apply join condition(s) between each element to resolve.</code></p>
<p>I'm not sure what the problem is. I thought that SQLAlchemy would take care of any JOINS that need to be done</p>
 

Latest posts

I
Replies
0
Views
1
impact christian
I
Top