Brilliant, wasn't aware I could pass a function to primary join. Thank you very much (for this, sqlalchemy and your amazing support), João
On Mon, 28 Oct 2019, 16:33 Mike Bayer, <[email protected]> wrote: > 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 [email protected]. > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/757fd50e-c6e9-441d-9715-0f475c6ae984%40www.fastmail.com > <https://groups.google.com/d/msgid/sqlalchemy/757fd50e-c6e9-441d-9715-0f475c6ae984%40www.fastmail.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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAJGrhWaZdVGV0cqAv-bZmm2aFUx%2B6-BrgbaN0f17ww4AbAuvQQ%40mail.gmail.com.
