Oh my, forgot to enclose PREFIX by double quote. The below code should work:
PHP Code:
$stmt = $adopts->join("adoptables_conditions", "adoptables_conditions.id = adoptables.id")
->select("adoptables", array(), constant("PREFIX")."adoptables.whenisavail = 'shop'");