sqlalchemy joining two models with key substring

Question

I Have two oracle based tables. table foo has:

val1,domain

x1,domain1.com

x2,domain2.com

---

table bar has:

val2,hostname

A,host1.domain1.com

B,host2.domain2.com

---

based on those tables i created a flask-sqlalchemy model

            class Foo(db.Model):
                __tablename__ = 'foo'
                val1 =  db.Column(db.String(256))
                domain  =  db.Column(db.String(256),primary_key=True)


                def __repr__(self):
                    return "<Foo(val1='%s', domain='%s')>" % (self.val1, self.domain)




            class Bar(db.Model):
                __tablename__ = 'bar'
                val2 =  db.Column(db.String(256),  primary_key=True)
                hostname =  db.Column(db.String(256),  primary_key=True)


                @hybrid_property
                def domain(self):
                    return self.hostname.split('.', 1)[1]

                def __repr__(self):
                    return "<Bar hostname='%s', domain='%s')>" % (self.hostname, self.domain)

need to join the tables based on domain part i tried to use @hybrid_property to generate the domain part but didnt managed to build a relation based on it as i cannot set @hybrid_property to be a foreign key . please advice how i can build a relation or join the two tables based on the domain part

Answer 1

A relationship between 2 tables based on one column containing a substring of the other column can be performed using the SQL LIKE operator. The different ways that this can be implemented in pure SQL are discussed at https://stackoverflow.com/a/1386213/10548137. The following code implements this in Sqlalchemy for your specific use case.

class Foo(db.Model):
    __tablename__ = 'foo'
    val1 =  db.Column(db.String(256))
    domain  =  db.Column(db.String(256),primary_key=True)
    bars = db.relationship("Bar", primaryjoin='db.foreign(Bar.hostname).like("%."+Foo.domain)', viewonly=True, uselist=True)


class Bar(db.Model):
    __tablename__ = 'bar'
    val2 =  db.Column(db.String(256),  primary_key=True)
    hostname =  db.Column(db.String(256),  primary_key=True)
    foo = db.relationship("Foo", primaryjoin='Bar.hostname.like("%."+db.foreign(Foo.domain))', viewonly=True, uselist=False)