# search for keys in a given building, 
# issued to persons with a given status
IssuedKey->set_sql(building_id_person_status =>
  'SELECT id FROM IssuedKey, Person
   WHERE Person.person_id=IssuedKey.person_id
   AND IssuedKey.building_id=? AND Person.status=?');

my (@keys) = IssuedKey->search_building_id_person_status(
  $building, $status);
[download]

The example above is not very general because it only searches those specific fields, but sometimes it is all you need.

For something like this, I would agree that set_sql is a good approach. There are things databases are pretty good at, and I think this is one of them. Note that if you are going to reference columns from another table in a join, you'll need to add them as TEMP columns in the class with the sql code.