It might not work great with more complex usage so may not be worth it,
however, the lazyload can succeed here with some more explicit casts, also the
json attribute is the "foreign" part here because it's the part that references
something else.
Here's your POC
from sqlalchemy import cast
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.mutable import MutableDict
from sqlalchemy.orm import foreign
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
Base = declarative_base()
class Department(Base):
__tablename__ = "department"
department_id = Column(Integer, autoincrement=True, primary_key=True)
class Project(Base):
__tablename__ = "project"
project_id = Column(Integer, autoincrement=True, primary_key=True)
meta = Column(MutableDict.as_mutable(JSONB), nullable=False, default={})
department = relationship(
"Department",
viewonly=True,
primaryjoin=lambda: Department.department_id
== foreign(
cast(Project.meta, JSONB)["department_id"].astext.cast(Integer)
),
)
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)
d1 = Department(department_id=1)
s.add(d1)
s.add(Project(meta={"department_id": 1}))
s.commit()
p1 = s.query(Project).first()
assert p1.department is d1
On Mon, Oct 28, 2019, at 4:44 AM, João Miguel Neves wrote:
> Hi,
>
> I'm not sure this is supported or intended to work, but I'm trying to use a
> value inside a JSONB field in a relationship.
>
> class Department(DeclarativeBase):
> __tablename__ = "department"
> department_id = Column(Integer, autoincrement=True, primary_key=True)
>
> class Project(DeclarativeBase):
> __tablename__ = "project"
>
> project_id = Column(Integer, autoincrement=True, primary_key=True)
> meta = Column(MutableDict.as_mutable(JSONB), nullable=False, default={})
> department = relation(
> "Department",
> viewonly=True,
> primaryjoin="foreign(Department.department_id) ==
> Project.meta['department_id'].astext.cast(Numeric)",
> )
>
>
>
> When I try to access project.department I get the following error:
>
> ProgrammingError: (psycopg2.errors.AmbiguousFunction) operator is not unique:
> unknown ->> unknown
> LINE 3: ...vite": true, "send_project_record_invite": true}' ->> 'depar...
> ^
> HINT: Could not choose a best candidate operator. You might need to add
> explicit type casts.
>
> Is there a way to do this wth relationship? Fair enough if not supported, I
> can work around it with hybrid properties or column properties, was just
> trying a different way.
>
> Thanks for a great library,
> João
>
>
> --
> 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/CAJGrhWZ8sZ%2BGU0y7K9RSu03ie6Y-1Y-S3%3DwgYQ_meLG%3DK4qjxA%40mail.gmail.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/CAJGrhWZ8sZ%2BGU0y7K9RSu03ie6Y-1Y-S3%3DwgYQ_meLG%3DK4qjxA%40mail.gmail.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/757fd50e-c6e9-441d-9715-0f475c6ae984%40www.fastmail.com.
