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 On Thursday, 7 September 2023 at 13:33:05 UTC+1 Mike Bayer wrote: > > > 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(). > > > > > -- 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/3693fbd8-e845-46e8-ad11-b8611a236774n%40googlegroups.com.