Nested composite column types

Issue #4168 closed
Psyche NA
created an issue

According to the document

I have a Point class:

class Point(object):
    def __init__(self, x, y):
        self.x = x
        self.y = y

    def __composite_values__(self):
        return self.x, self.y

I could create a mapping to a table vertices, which represents two points as x1/y1 and x2/y2.

class Vertex(Base):
    __tablename__ = 'vertices'

    id = Column(Integer, primary_key=True)
    x1 = Column(Integer)
    y1 = Column(Integer)
    x2 = Column(Integer)
    y2 = Column(Integer)

    start = composite(Point, x1, y1)
    end = composite(Point, x2, y2)

But the problem is that I have two classes Point and Line:

class Point(object):
    def __init__(self, x, y):
        self.x = x
        self.y = y


class Line(object):
    def __init__(self, start: Point, end: Point):
        self.start = start
        self.end = end

I could not create a mapping to a table Shape like this:

class Shape(Base):
    __tablename__ = 'shapes'

    id = Column(Integer, primary_key=True)
    a1 = Column(Integer)
    a2 = Column(Integer)
    b1 = Column(Integer)
    b2 = Column(Integer)
    c1 = Column(Integer)
    c2 = Column(Integer)
    d1 = Column(Integer)
    d2 = Column(Integer)
    ...

    line1 = composite(Line, composite(Point, a1, a2), composite(Point, b1, b2))
    line2 = composite(Line, composite(Point, c1, c2), composite(Point, d1, d2))
    ...

Maybe the example above is not good enough. But the point is that could composite be nested?

Official response

  • Michael Bayer repo owner

    We can actually use a custom comparator directly with the composite, so I've illustrated a 2-level composite / comparator approach below.

    from sqlalchemy import Column, Integer, create_engine
    from sqlalchemy.orm import Session, composite
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.ext.hybrid import Comparator
    from sqlalchemy.orm import CompositeProperty
    
    
    Base = declarative_base()
    
    
    class Point(object):
        """A point object.
    
        Defines an x and a y.
    
        """
        def __init__(self, x, y):
            self.x = x
            self.y = y
    
        # comparison methods on the Point are only used with Line objects
        # with integer values, not SQL expressions.
        def __eq__(self, other):
            return isinstance(other, Point) and \
                other.x == self.x and \
                other.y == self.y
    
        def __ne__(self, other):
            return not self.__eq__(other)
    
    
    class Line(object):
        """A line object.
    
        defines a start Point and an end Point.
    
        includes a __composite_values__() method so that it is usable
        as a composite.
        See: http://docs.sqlalchemy.org/en/latest/orm/composites.html
    
        """
    
        def __init__(self, start, end):
            self.start = start
            self.end = end
    
        @classmethod
        def create_from_xy(cls, a1, a2, b1, b2):
            return cls(Point(a1, a2), Point(b1, b2))
    
        def __composite_values__(self):
            return self.start.x, self.start.y, self.end.x, self.end.y
    
        # comparison methods on the Line are only used with Line objects
        # with integer values, not SQL expressions.
        def __eq__(self, other):
            return isinstance(other, Line) and \
                other.start == self.start and \
                other.end == self.end
    
        def __ne__(self, other):
            return not self.__eq__(other)
    
    # now define SQL expression comparisons for Point and Line
    
    
    class PointComparator(Comparator):
        """Define Point SQL expressions.
    
        Basing this off the hybrid comparator, see
        http://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html#building-custom-comparators.
    
        """
        def __init__(self, x, y):
            self.x = x
            self.y = y
    
        # define comparisons given another Point, or PointComparator, object,
        # in terms of Core SQL expressions.
    
        def __eq__(self, other):
            return (self.x == other.x) & (self.y == other.y)
    
        def __ne__(self, other):
            return (self.x != other.x) | (self.y != other.y)
    
        # etc
    
    
    class LineComparator(CompositeProperty.Comparator):
        """Define a custom comparator for Line composite.
    
        This can also be done with hybrid methods as I mentioned earlier,
        but the CompositeProperty lets you pass a comparator directly, so we
        are using that here.  It isn't too different either way:
    
        See http://docs.sqlalchemy.org/en/latest/orm/composites.html#composite-operations
    
        """
    
        def __init__(self, *arg, **kw):
            super(LineComparator, self).__init__(*arg, **kw)
    
            # extract the point expressions from the composite expression.
            # this ordering comes from Line.__composite_values__().
            self.x1, self.y1, self.x2, self.y2 = self.__clause_element__().clauses
    
        # implement .start and .end
        @property
        def start(self):
            return PointComparator(self.x1, self.y1)
    
        @property
        def end(self):
            return PointComparator(self.x2, self.y2)
    
        # compare the PointComparators.   then turtles all the way down
        def __eq__(self, other):
            return (self.start == other.start) & (self.end == other.end)
    
        def __ne__(self, other):
            return (self.start != other.start) | (self.end != other.end)
    
        # etc.
    
    
    class Shape(Base):
        __tablename__ = 'shapes'
    
        id = Column(Integer, primary_key=True)
        x1 = Column(Integer)
        y1 = Column(Integer)
        x2 = Column(Integer)
        y2 = Column(Integer)
    
        line = composite(
            Line.create_from_xy, x1, y1, x2, y2, comparator_factory=LineComparator)
    
    # ready to go!
    
    e = create_engine("sqlite://", echo=True)
    Base.metadata.create_all(e)
    
    s = Session(e)
    
    s1, s2, s3, s4 = (
        Shape(line=Line(Point(2, 5), Point(10, 15))),
        Shape(line=Line(Point(19, 5), Point(3, 10))),
        Shape(line=Line(Point(2, 8), Point(17, 19))),
        Shape(line=Line(Point(19, 5), Point(6, 12))),
    )
    
    s.add_all([s1, s2, s3, s4])
    
    s.commit()
    
    shapes = s.query(Shape).\
        filter(Shape.line.start == Point(19, 5)).order_by(Shape.id).all()
    assert shapes == [s2, s4]
    
    shape = s.query(Shape).\
        filter(Shape.line == Line(Point(19, 5), Point(3, 10))).one()
    assert shape is s2
    

Comments (7)

  1. Michael Bayer repo owner

    they don't support nesting and to implement this would be very complicated. you're much better off just using hybrid attributes which can get you 95% of what composites do and can be organized in any way you'd like.

  2. Psyche NA reporter

    Thanks for your reply ^-^ Finally I found a solution for this feature.

    In descriptor_props.py

    # line 293
    # assert self.key not in dict_
    dict_[self.key] = self.composite_class(
        *[state.dict[key] for key in
           self._attribute_keys]
    )
    

    self.composite_class is the class type predefined in composite function. So I created a classmethod in Line, and pass it to composite like this:

    class Line(object):
        def __init__(self, start: Point, end: Point):
            self.start = start
            self.end = end
    
        @classmethod
        def create(cls, a1, a2, b1, b2):
            return cls(Point(a1, a2), Point(b1, b2)
    
    class Shape(Base):
        __tablename__ = 'shapes'
    
        id = Column(Integer, primary_key=True)
        a1 = Column(Integer)
        a2 = Column(Integer)
        b1 = Column(Integer)
        b2 = Column(Integer)
        ...
    
        line1 = composite(Line.create, a1, a2, b1, b2)
        line2 = composite(Line.create, c1, c2, d1, d2)
    

    This solution worked.

  3. Michael Bayer repo owner

    sure, if that gets you what you need, technically Point is no longer a "composite" from an ORM point of view, meaning you cant query from it or anything like that....although, you could put a hybrid onto Line so that you could still do Shape.line1.start == Point(5, 6).

  4. Michael Bayer repo owner

    you can get nesting of composites pretty much using classmethods and/or @hybrid_property on your top composite, this is much simpler than trying to build nesting all the way into the composite descriptor which is already difficult to extend.

  5. Steven Harrison

    Hello @Michael Bayer. I am facing a similar challenge as indicated above. I would still like to query by one of the nested composites, so was interested in the solution you alluded to using classmethods and / or @hybrid_property. I've been reading through the docs but haven't been able to get my head around how to do this.

    Could you shed some light on these two approaches?

  6. Michael Bayer repo owner

    We can actually use a custom comparator directly with the composite, so I've illustrated a 2-level composite / comparator approach below.

    from sqlalchemy import Column, Integer, create_engine
    from sqlalchemy.orm import Session, composite
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.ext.hybrid import Comparator
    from sqlalchemy.orm import CompositeProperty
    
    
    Base = declarative_base()
    
    
    class Point(object):
        """A point object.
    
        Defines an x and a y.
    
        """
        def __init__(self, x, y):
            self.x = x
            self.y = y
    
        # comparison methods on the Point are only used with Line objects
        # with integer values, not SQL expressions.
        def __eq__(self, other):
            return isinstance(other, Point) and \
                other.x == self.x and \
                other.y == self.y
    
        def __ne__(self, other):
            return not self.__eq__(other)
    
    
    class Line(object):
        """A line object.
    
        defines a start Point and an end Point.
    
        includes a __composite_values__() method so that it is usable
        as a composite.
        See: http://docs.sqlalchemy.org/en/latest/orm/composites.html
    
        """
    
        def __init__(self, start, end):
            self.start = start
            self.end = end
    
        @classmethod
        def create_from_xy(cls, a1, a2, b1, b2):
            return cls(Point(a1, a2), Point(b1, b2))
    
        def __composite_values__(self):
            return self.start.x, self.start.y, self.end.x, self.end.y
    
        # comparison methods on the Line are only used with Line objects
        # with integer values, not SQL expressions.
        def __eq__(self, other):
            return isinstance(other, Line) and \
                other.start == self.start and \
                other.end == self.end
    
        def __ne__(self, other):
            return not self.__eq__(other)
    
    # now define SQL expression comparisons for Point and Line
    
    
    class PointComparator(Comparator):
        """Define Point SQL expressions.
    
        Basing this off the hybrid comparator, see
        http://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html#building-custom-comparators.
    
        """
        def __init__(self, x, y):
            self.x = x
            self.y = y
    
        # define comparisons given another Point, or PointComparator, object,
        # in terms of Core SQL expressions.
    
        def __eq__(self, other):
            return (self.x == other.x) & (self.y == other.y)
    
        def __ne__(self, other):
            return (self.x != other.x) | (self.y != other.y)
    
        # etc
    
    
    class LineComparator(CompositeProperty.Comparator):
        """Define a custom comparator for Line composite.
    
        This can also be done with hybrid methods as I mentioned earlier,
        but the CompositeProperty lets you pass a comparator directly, so we
        are using that here.  It isn't too different either way:
    
        See http://docs.sqlalchemy.org/en/latest/orm/composites.html#composite-operations
    
        """
    
        def __init__(self, *arg, **kw):
            super(LineComparator, self).__init__(*arg, **kw)
    
            # extract the point expressions from the composite expression.
            # this ordering comes from Line.__composite_values__().
            self.x1, self.y1, self.x2, self.y2 = self.__clause_element__().clauses
    
        # implement .start and .end
        @property
        def start(self):
            return PointComparator(self.x1, self.y1)
    
        @property
        def end(self):
            return PointComparator(self.x2, self.y2)
    
        # compare the PointComparators.   then turtles all the way down
        def __eq__(self, other):
            return (self.start == other.start) & (self.end == other.end)
    
        def __ne__(self, other):
            return (self.start != other.start) | (self.end != other.end)
    
        # etc.
    
    
    class Shape(Base):
        __tablename__ = 'shapes'
    
        id = Column(Integer, primary_key=True)
        x1 = Column(Integer)
        y1 = Column(Integer)
        x2 = Column(Integer)
        y2 = Column(Integer)
    
        line = composite(
            Line.create_from_xy, x1, y1, x2, y2, comparator_factory=LineComparator)
    
    # ready to go!
    
    e = create_engine("sqlite://", echo=True)
    Base.metadata.create_all(e)
    
    s = Session(e)
    
    s1, s2, s3, s4 = (
        Shape(line=Line(Point(2, 5), Point(10, 15))),
        Shape(line=Line(Point(19, 5), Point(3, 10))),
        Shape(line=Line(Point(2, 8), Point(17, 19))),
        Shape(line=Line(Point(19, 5), Point(6, 12))),
    )
    
    s.add_all([s1, s2, s3, s4])
    
    s.commit()
    
    shapes = s.query(Shape).\
        filter(Shape.line.start == Point(19, 5)).order_by(Shape.id).all()
    assert shapes == [s2, s4]
    
    shape = s.query(Shape).\
        filter(Shape.line == Line(Point(19, 5), Point(3, 10))).one()
    assert shape is s2
    
  7. Steven Harrison

    Thank you @Michael Bayer. This is so helpful!

    The Comparator and CompositeProperty class look like the missing key. I look forward to diving into the docs and really getting to understanding this code.

    By the time I get to to 2nd level of nesting I was finding most of my Value Objects were single attribute classes. So I had landed on building a generic Custom Type for those. With the 1st level of nesting as a standard composite.

    And for more complicated (multiple attribute) classes I could create a more specific Custom type for that class. I really like your solution though, so it's nice to have that extra level tool in the tool belt. Thanks again.

    class UnaryValueObjectType(TypeDecorator):
        impl = Numeric
    
        def __init__(self, class_of_value_object, type):
            self.class_of_value_object = class_of_value_object
            self.type = type
            super(UnaryValueObjectType, self).__init__()
    
        def load_dialect_impl(self, dialect):
            return dialect.type_descriptor(self.type)
    
        def process_bind_param(self, value_object, dialect):
            if isinstance(value_object, self.class_of_value_object) and value_object is not None:
                value = value_object.value
            return value
    
        def process_result_value(self, value, dialect):
            if value is not None:
                value_object = self.class_of_value_object(value)
            return value_object
    

    and

    Column("country_name", UnaryValueObjectType(CountryName, String))
    
  8. Log in to comment