How would I translate user-customizable advanced searches to SQL queries?

An abstract question deserves an abstract answer. :-) Alas, I'm going to give you an example of how another piece of software handles this problem, because I'm not very philosophical.

Request Tracker is a long-established browser-UI trouble ticketing system. It has a tremendously powerful ticket search mechanism that knows how to read every possible field associated with a ticket, and lets you effectively craft your entire SQL query via the web interface.

I couldn't find any screenshots of the search page, so perhaps you might want to look at the online demo of RT. Log in, then from the menu at the top-left of your window, select "Ticket" then "New Search".

Or better yet, to see how ALL searches are built on this search tool, click on a queue from the list on the right-hand side of the main demo screen (which shows up right after you log in). When you have a set of tickets showing, click on "Tickets" at the top of your window. (Not a menu option that comes up. Just click Tickets.)

The query builder here shows the structure of your query on the right, and lets you mix-and-match things to include in it using controls on the left. And if you're an "advanced" user and want to write your own query manually, or clean up something that the web UI is having problems with, you can click the Advanced link at the top and edit something that looks remarkably like SQL.

I won't get in to how to JOIN other tables in. I'm answer this as if it's a UI/UX question, not a data modelling one. :-)