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

`https://stackoverflow.com/questions/47243397/sqlalchemy-joinedload-filter-column` — Ashkan Goleh Pour, Oct 24 '22 at 10:30
Thanks. I have read this before and it does not work also with contains_eager. same error message. Not that I am using AsyncSession and not Session. — Udemytur, Oct 24 '22 at 10:42
query = ( select(SubscriptionPlan) .join(SubscriptionPlan.plans_pricing) .join(SubscriptionPlan.options_plan) .options(contains_eager(SubscriptionPlan.options_plan), contains_eager(SubscriptionPlan.plans_pricing)) .where(PlanPricing.pricing_id == pricing_id) ) items = await session.execute(query) items = items.scalars().all() — Udemytur, Oct 24 '22 at 10:42

score 2 · Answer 1 · answered Jul 03 '23 at 08:52

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()

score -1 · Accepted Answer · edited Feb 08 '23 at 18:37

I misunderstood sqlalchemy's loading technique. Nevertheless, my request was not well formatted, so I changed it to be that way, and it is currently functioning.

plan_pricing_aliased1 = aliased(PlanPricing)
plan_pricing_aliased2 = aliased(PlanPricing)
query = (
    select(
        plan_pricing_aliased1.pricing_id, 
        plan_pricing_aliased2.pricing_id
    ).join_from(
        plan_pricing_aliased1,
        plan_pricing_aliased2,
        and_(
            plan_pricing_aliased2.currency == plan_pricing_aliased1.currency,
            plan_pricing_aliased2.plan_id == plan_pricing_aliased1.plan_id,
            plan_pricing_aliased2.pricing_frequency == plan_pricing_aliased1.pricing_frequency
        )
    ).where(
        and_(
            plan_pricing_aliased1.pricing_id == pricing_id,
            plan_pricing_aliased1.price > 0,
            plan_pricing_aliased2.price == 0
        )
    )
)
items = await session.execute(query)
items = items.first()
return items

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Oct 27 '22 at 23:35

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

2 Answers2