I used to be a fan of ORMs. Then I started using SQLAlchemy in production..

What I find captivating from the

[START COPY PASTE] Because, of course, you can't just use "normal" python when using SQLAlchemy - that's the benefit of using an ORM, right..? Jokes aside, it was by pure luck (of by sophisticated mastery) that old uid hybrid property was working: apparently, the + is somehow supported when doing a simple concat.

But for os_triplet of course things are more involved since there are foreign keys at play, and a lowercase transformation. So the result is that we need to also implement a hybrid_property.expression method that helps SQLAlchemy in translating our property into something that it can be used also when querying using the filter_by syntax. I will not tell you how long it took to figure this out, but I will tell you that it took waaay more time to figure out the "SQLAlchemy way" than the RAW SQL one..

I first had to figure out how this would work in raw SQL. Note that SQLlite has no CONCAT, and string concatenation is achieved via the || operator instead (https://www.sqlite.org/lang_expr.html#:~:text=The%20%7C%7C%20operator%20is%20%22concatenate,two%20strings%20of%20its%20operands.).

After a few attempts, this is where I landed:

SELECT LOWER(t2.short_name || '-' || t2.major || '.' || t2.minor || '-' || t1.architecture) FROM platforms t1 INNER JOIN os_versions t2 ON t1.os_version_id = t2.id;

Which would produce the expected value:

$ sqlite3 ./my.db

> SELECT LOWER(t2.short_name || '-' || t2.major || '.' || t2.minor || '-' || t1.architecture) FROM platforms t1 INNER JOIN os_versions t2 ON t1.os_version_id = t2.id ORDER BY LOWER(t2.short_name);
centos-7.9-x86_64
macos-12.2-x86_64
macos-12.3-x86_64
macos-12.4-x86_64
macos-12.5-x86_64
macos-10.15-x86_64
macos-13.0-x86_64
macos-13.1-x86_64
macos-13.2-x86_64
macos-12.6-x86_64
macos-13.3-x86_64
macos-13.4-x86_64
macos-13.5-x86_64
macos-13.6-x86_64
macos-14.0-x86_64
macos-14.1-x86_64
rocky-8.7-x86_64
rocky-8.8-x86_64

So now it was a matter of figuring out how to translate all of this into SQLAlchemy lingo.

I had a look at https://docs.sqlalchemy.org/en/14/orm/extensions/hybrid.html#sqlalchemy.ext.hybrid.hybrid_method.expression but to be honest there was no real explanation of how to handle complex cases where you need to query foreign keys. So I have to deeply thank user r-m-n from https://stackoverflow.com/a/60717579 , since that's the only real world example out there that I could use as a base to evolve the version that I'm using here.

[END COPY PASTE]