As long as there's something acting as a vapour barrier you'll avoid condensation, it doesn't need to be spray foam. You want vapour barrier in the warm third (by R value, not necessarily thickness) of the wall to avoid condensation. So hot places that mostly run AC will have their vapour barrier to the outside off the wall, where cooler places you tend to heat is the opposite.

You can do the joist ends with batts and poly, it's just really hard to get a good seal especially with I-joists. I think the foam stuff is R5 per inch? Might be a bit better than that but was targeting R20 so 4" inches thick, maybe a bit thinner if it's R7 per inch. R value is a measure of how well something resists heat transfer btw, higher number is better.

Check how things are built in your area as it can be very regional depending on climate and building codes.