sqlalchemy/community

Good morning! I have a SqlAlchemy problem that you guys might find interesting. The core of it is representing a sparse matrix of data using the ORM. Consider the following set up:

Base = declarative_base()


class Row(Base):
    __tablename__ = "row"
    id = Column(Integer, index=True, primary_key=True)
    organization_id = Column(Integer, ForeignKey("organization.id"), nullable=False)


class Column(Base):
    __tablename__ = "column"
    id = Column(Integer, index=True, primary_key=True)
    type = Column(String(length=32), nullable=False)
    name = Column(String(length=32), nullable=False)
    organization_id = Column(Integer, ForeignKey("organization.id"), nullable=False)


class Element(Base):
    __tablename__ = "element"
    row_id = Column(Integer, ForeignKey("row.id"), nullable=False, primary_key=True)
    row = relationship("Row", backref="elements")

    column_id = Column(Integer, ForeignKey("column.id"), nullable=False, primary_key=True)
    column = relationship("Column", backref="elements")
    type = association_proxy("column", "type")
    name = association_proxy("column", "name")

    # data is non-nullable because, if data is null in the matrix, the element row should 
    # just be deleted
    data = Column(String, nullable=False)

For a given Row, I'd like to get the non-sparse list of elements that compose that list. So far, I have been able to get the elements that are non-null as a relationship attribute using the following relationship:

RelatedElement = aliased(
    Element,
    select([Element])
    .select_from(
        join(
            Row,
            Column,
            Row.organization_id == Column.organization_id,
            full=True,
            isouter=True
        ).join(
            Element,
            and_(
                Row.id == Element.row_id,
                Column.id == Element.column_id,
            ),
            full=True
        )
    ).alias(),
)

Row.elements = relationship(
    RelatedElement,
    primaryjoin=Row.id == RelatedElement.row_id,
    viewonly=True
)

The purpose of the full outer join was to create a cross product between rows and columns. Unfortunately, when I test this relationship, I don't get the cross product. For example:

r = Row(organization_id=1)
c1 = Column(type="integer", name="Count", organization_id=1)
c2 = Column(type="string", name="Name", organization_id=1)
db.add_all([r, c1, c2])
db.commit()
for obj in [r, c1, c2]:
    db.refresh(obj)

e = Element(row=r, column=c1, data="1")
db.add(e)
db.commit()
db.refresh(e)
db.refresh(r)

# now, we'd like to get all the elements related to r: 
r.elements
> [Element(data="1", column=c1)]

# but what I really want is:
r.elements
> [Element(data="1", column=c1), Element(data=null, column=c2)]

I used the documentation here to get this far, but to no avail.

Any direction would be greatly appreciated!