With the Rohan scene, you can forgive them because that first part does actually involve cavalry succeeding at what cavalry is supposed to do, and that is scare the shit out of the guys they are charging toward. When the Orcs break and run, everything is cool.
Reality breaks down when they keep going and start running over large numbers of Orcs. Horses won't do that of course, since numerous high speed collisions tend to hurt. And even if we say they are magical Middle Earth horses with, like, better health insurance or something, keeping up momentum like they do would still be physically impossible with all those collisions. Imagine barreling through a field of deer with a car. If you've ever seen a person hit a deer, you know how ridiculous this is. And a car has more horsepower than a horse does.
Still, that scene is fucking boss no matter how unrealistic it is.
So yeh, cavalry will have a difficult time with spears, a worse time with pikes, but they are super scary. Also, they have to slow down or stop if they engage in combat, unless they are Mongols. Mongols used to hang out of their saddle and shoot underneath their horse so the horse would shield them from enemy fire. Those guys were beast.
At the same time, don't just sit down and follow RTS logic. RTS games are rock paper scissors because they are games and need balance, but the real world doesn't work that way. In the real world, formations are vital. Pikes in a wall are virtually unstoppable, but hard to maneuver and completely unwieldy on rough terrain. A single pikeman, on the other hand, is at a disadvantage because his weapon is a pain in the ass and is useless if an attacked slips passed the tip.