SQLAlchemy relationship primaryjoin translation

Question

I am trying to use a relationship primary join to join two tables, where the join is the result of finding the name in table2 that the longest substring of the name in table1. The standard postgres SQL for this problem works - I'm trying to translate it to a SQLAlchemy join.

The sql is:

select distinct on (t1.name) t2.name
from table1 t1
join table2 t2
on t1.name like format('%%%s%%', t2.name) order by t1.name desc, char_length(t2.name) desc;

For each name in table1, it joins the name in table2 that is the longest substring of that name in table1.

The problem I am facing is translating it to a SQLALchemy relationship, that is

class Table1(db.Model):
    __tablename__ = 'table1'

...

name = db.Column(db.String)

...

t1t2= db.relationship("Table2", primaryjoin="...")

Answer 1

In theory you can do it as follows, although I wouldn't recommend using relationships based on such queries. Basically you need to get rid of order by in your query. This can be done via a subquery as follows:

select distinct on (t1.name) t2.name
from table1 t1
join table2 t2
on t1.name like format('%%%s%%', t2.name) and char_length(t2.name) = (select max(char_length(t3.name)) from table2 t3 where t1.name like format('%%%s%%', t3.name));

Keep in mind that this may join one row in table1 with several in table2.