Only those who just can’t handle him.

Plenty of people does not mind working for him.

See this survey

https://insideevs.com/tesla-spacex-employees-confident-elon-...

I'd much rather have a shitty boss with great vision than a great boss with shitty mission (e.g. advertising or something).

Then how come the team does good work? "Firing constantly" sounds exactly like weeding out unproductive members (of course I now nothing about the workers' feelings, though).

No, a lot of very good folks are shed by Elon's companies all the time. He's just not an effective manager, Tesla is in great measure successful despite Elon, not because of him, and his limitations as a manager are becoming more and more of an obstacle. I have first hand knowledge of that.