Object material that is completely transparent, but occludes other objects
I think what you want is a holdout shader.  A holdout shader is like a transparent emission shader: wherever it is, it writes transparency (alpha 0).  It still gets occluded itself.

With the use of a holdout shader, you can then alpha layer your footage that describes both background and foreground objects.
Holdout shaders work both in Eevee and Cycles.