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

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

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:

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