Duplicating a relationship from one ORM model to a subquery model

[zedr...@gmail.com]

I am trying to (programmatically) create partial views of existing SQLAlchemy models, using a metaclass.

A streamlined example, trying to get a subquery view on a model Foo with a relationship bar, would look something like that:

class Bar(db.Model):

id = db.Column(db.Integer, primary_key=True)

class Foo(db.Model):

id = db.Column(db.Integer, primary_key=True)

bar_id = db.Column(db.Integer, db.ForeignKey("bar.id"))

bar = db.relationship(Bar, backref='foos')

class FooViewMeta(type(db.Model)):

def __new__(mcs, name, bases, attrs):

attrs["__table__"] = (select(Foo)

.with_only_columns(Foo.id, Foo.bar_id, Foo.bar)

.subquery())

attrs["bar"] = Foo.__mapper__.relationships["bar"]

attrs["__mapper_args__"] = {"exclude_properties": ["bar"]}

return super().__new__(mcs, name, (db.Model,), attrs)

class FooView(metaclass=FooViewMeta):

pass

(Omitting exclude_properties gives me a warning about conflicting columns)

I am able to load FooView and access its bar property fine.

Unfortunately, if I run:

q = Foo.query.options(joinedload(Foo.bar),)

q.first()

I get the following error, which tells me that this is not a correct way to duplicate the mapper for that relationship:

<class 'sqlalchemy.exc.ArgumentError'>: Mapped class Mapper[FooView(anon_1)] does

not apply to any of the root entities in this query, e.g. Mapper[Foo(foo)].

Please specify the full path from one of the root entities to the target attribute.

Clearly, the re-use of this relationship is confusing the mapper, who tries to use the newer subquery model and fails.

Conversely, if I use:

attrs["bar"] = getattr(Foo, "bar")

The code works for 1-1 relationship, but fails for lazy 1-many…

Is there a clean way to (programmatically) duplicate all relationship from an existing model, over to a new model (that targets the same table and selects a subset of columns as a subquery)?

Note: unfortunately, I am not looking for polymorphic subclassing here (the "view" classes are a subset of columns, not rows), so it does not seem like I can use any of SQLAlchemy mapper options for that…

[Mike Bayer]

On Thu, Sep 7, 2023, at 4:39 AM, zedr...@gmail.com wrote:

Is there a clean way to (programmatically) duplicate all relationship from an existing model, over to a new model (that targets the same table and selects a subset of columns as a subquery)?

relatonships are fixed to their parent class and have internal state that is dependent on it, so it can't be shared between two unrelated classes.

you would need to introspect the attributes of each relationship that are important to the application, such as the target class etc., and create a new relationship() that has those attributes.    Assuming the relationships dont have special join conditions, this would involve just looking for existing_class.__mapper__.attrs.relationship.entity, which is the mapper to which it refers, and placing this into a new relationship().

[zedr...@gmail.com]

Hi Mike,

Thanks a lot for taking the time to reply…

Indeed, I came to a similar conclusion and worked out what seems like a fairly clean way to copy relationships (assuming they do not use secondary_join).

The fact that I cannot seem to update the mapper before the class is created makes my code a bit more convoluted than I'd like… But this seems to work:

def copy_relationship(source_model, relationship_name):

source_relationship = getattr(source_model.__mapper__.relationships, relationship_name, None)

kwargs = {}

overlaps = [relationship_name]

for param_name in inspect.signature(relationship).parameters:

if value := getattr(source_relationship, param_name, None):

if param_name in ('backref', 'back_populates'):

overlaps.append(value)

elif param_name not in ('init', 'primaryjoin'):

kwargs[param_name] = value

return relationship(**kwargs, overlaps = ','.join(overlaps))

class FooViewMeta(type(db.Model)):

def __new__(mcs, name, bases, attrs):

attrs["__table__"] = (select(Foo).with_only_columns(Foo.id).subquery())

attrs["bazs"] = copy_relationship(Foo, 'bazs')

return super().__new__(mcs, name, (db.Model,), attrs)

def __init__(cls, name, bases, attrs):

super().__init__(name, bases, attrs)

for rel_name, rel_attr in cls.__mapper__.relationships.items():

expr = getattr(rel_attr, 'primaryjoin', None)

if not expr: continue

kwargs = {'operator': expr.operator}

for attr in ('left', 'right'):

side = getattr(expr, attr)

if ('__table__' in side) and (side.table == Foo.__table__) and (side.name in cls.__table__.c):

side = foreign(getattr(cls.__table__.c, side.name))

kwargs[attr] = side

rel_attr.primary_join = BinaryExpression(**kwargs)

class FooView(metaclass=FooViewMeta):

pass

Running the code above without providing an overlaps argument to the new relationship, results in a relationship conflict warning:

<console>:10: SAWarning: relationship 'FooView.bazs' will copy column anon_1.id to column baz.foo_id, 

which conflicts with relationship(s): 'Baz.foo' (copies foo.id to baz.foo_id). If this is not the intention,

consider if these relationships should be linked with back_populates, or if viewonly=True should be applied 

to one or more if they are read-only. For the less common case that foreign key constraints are partially 

overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.

To silence this warning, add the parameter 'overlaps="foo"' to the 'FooView.bazs' relationship. 

(Background on this warning at: https://sqlalche.me/e/20/qzyx)

As you can see in the code above, adding foreign to the join expression, does not solve the problem. 

All in all, I wish there was a more straightforward way to create models that inherit from an existing model, while loading/accessing only a subset of columns (basically, polymorphism, without a polymorphism key)… Will gladly take any suggestion on a different approach…

But at least this seems to work!

Thanks again,

-- 

Dave

[Mike Bayer]

send viewonly=True to these relationships you make.  these are not for writing anyway and that will resolve the overlaps warnings (the warning says as much).

--

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/3693fbd8-e845-46e8-ad11-b8611a236774n%40googlegroups.com.