Foreign key from inside JSONB field

[João Miguel Neves]

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

[Mike Bayer]

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

--

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+...@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.

[João Miguel Neves]

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 

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/757fd50e-c6e9-441d-9715-0f475c6ae984%40www.fastmail.com.