Mine

Step 1
--------
Change to a subselect::

  select d.id, count(foo.id) as cnt from (select distinct d.id, h.host_link_id from device as d, host_links as h where h.device_id = d.id order by d.id, h.host_link_id) as foo, device as d where d.id = foo.id group by d.id;

Step 2
--------
Convert the subselect to sqlalchemy::

  selectable  = sqlalchemy.select( (model.ComputerLogicalDevice.c.id, model.HostLink.c.host_link_id), sqlalchemy.and_(model.ComputerLogicalDevice.c.id == model.HostLink.c.device_id), distinct = True, correlate=False).alias('foo')

# Note that correlate = False should only be needed for MySQL.  Postgres and sqlite are supposed to get this right without it.


Step 3
--------
Add the other pieces of the query:

result = sqlalchemy.select((model.ComputerLogicalDevice.c.description, selectable.c.id, selectable.c.host_link_id), sqlalchemy.and_(model.ComputerLogicalDevice.c.id==selectable.c.id))