The unique() method must be invoked on this Result exception raised after SQLAlchemy Select

Question

I have an issue with SQLAlchemy and I cannot figure out the cause of this error:

so my class definition is:

class PricingFrequency(enum.Enum):
    month = 'month'
    year = 'year'


class PlanPricing(Base):
    __tablename__ = "PlansPricing"
    pricing_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    .....
    subscription_plan = relationship("SubscriptionPlan", back_populates="plans_pricing")
    plan_id = Column(UUID(as_uuid=True), ForeignKey("SubscriptionPlans.plan_id"))

    created_on = Column(DateTime, server_default=func.now())
    updated_on = Column(DateTime, server_default=func.now(), server_onupdate=func.now())


class SubscriptionPlanOption(Base):
    __tablename__ = "SubscriptionPlanOptions"
    option_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    .....
    subscription_plan = relationship("SubscriptionPlan", back_populates="options_plan")
    plan_id = Column(UUID(as_uuid=True), ForeignKey("SubscriptionPlans.plan_id"))

    created_on = Column(DateTime, server_default=func.now())
    updated_on = Column(DateTime, server_default=func.now(), server_onupdate=func.now())


class SubscriptionPlan(Base):
    __tablename__ = "SubscriptionPlans"
    plan_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    plan_name = Column(String)
    plan_description = Column(String)
    is_popular = Column(Boolean, default=False)

    plans_pricing: List[Any] = relationship("PlanPricing", back_populates="subscription_plan")  # , lazy='joined')
    options_plan: List[Any] = relationship("SubscriptionPlanOption",
                                           back_populates="subscription_plan")  # lazy='joined')

    created_on = Column(DateTime, server_default=func.now())
    updated_on = Column(DateTime, server_default=func.now(), server_onupdate=func.now())

When I make this query :

query = (
                select(SubscriptionPlan)
                .options(joinedload(SubscriptionPlan.options_plan, innerjoin=True),
                         joinedload(SubscriptionPlan.plans_pricing.and_(PlanPricing.pricing_id == pricing_id),
                                    innerjoin=True))
            )
            items = await session.execute(query)
            items = items.scalars().all()

I got this error message:

**The unique() method must be invoked on this Result, as it contains results that include joined eager loads against collections**

Note : session is AsyncSession

Can anyone explain the source of this issue? Thanks

Answer 1

You are getting this error:

sqlalchemy.exc.InvalidRequestError: The unique() method must be invoked on this Result, as it contains results that include joined eager loads against collections

Reason:
Quoting the documentation in Joined Eager Loading:

When including joinedload() in reference to a one-to-many or many-to-many collection, the Result.unique() method must be applied to the returned result, which will uniquify the incoming rows by primary key that otherwise are multiplied out by the join. The ORM will raise an error if this is not present.

This is not automatic in modern SQLAlchemy, as it changes the behavior of the result set to return fewer ORM objects than the statement would normally return in terms of number of rows. Therefore SQLAlchemy keeps the use of Result.unique() explicit, so there’s no ambiguity that the returned objects are being uniqified on primary key.

So, you need to chain unique() to your Result:

session.execute(query).unique()