You’re on the right track with what you want. And you’re right that Apple has done a lousy job of providing a well-thought out and well-integrated solution.
On macOS, Mail has a “photo picker” as a solution for giving you an inline point of access to your photo library. I hate it because the performance is abysmal. And, while you can search for dates, you can’t search for places or keywords, which are two of the key pieces of metadata I use for finding photos.
On macOS, iMovie also has access to your photos. That’s not any better. In both cases, the best way for success is to revert to using a feature that Apple has been trying hard to marginalize for years: Albums. Make an album with the pix you want in Photos, and then both tools above will work great. But it’s a pain in the butt making a throwaway Album every time you want to share a pic with anyone or anything. And Apple has been working VERY hard for years to save you from the need for making Albums (recall that they ditched Events) by enriching their search tools with lots of great metadata search abilities. But external apps are still 2nd class citizens, even those written by Apple. Very sloppy. Embarrassing from the company that, years back, insisted they wanted to be our digital hub.
On iOS, it’s gotten better recently. The current photo picker lets you search for keywords, places, people, dates, etc. But app consumption of that feature is shoddy; I’m not sure why that is.
So to circle back, I’m not sure the Finder will ever be the right place to search for pix outside of Photos. Apple would more likely build it into Spotlight. But what we really need is the in-context photos picker like we have in iOS to be available everywhere we want it: in web browsers, in Facebook, in Mail, in iMovie, etc.