# query produces list of restaurant ids, most recent inspection date
maxdate = select([restaurant.c.id.label("restaurant_id"), func.max(inspection.c.date).label('date')], inspection.c.restaurant_id==restaurant.c.id, correlate=False, group_by=[restaurant.c.id]).alias('maxdate')

# assemble that into the join
latest_restaurant_inspections = select(
    [restaurant, inspection.c.id, inspection.c.date, violation.c.code], 
    and_(
        inspection.c.date==maxdate.c.date,
        restaurant.c.id==maxdate.c.restaurant_id
        #violation.c.code=='c1'
        ),
        from_obj=[
            restaurant.join(inspection).join(inspection_violation).join(violation),
            maxdate
            ],
    )