Hello, i have been developing a Asset Management System with a golang server as the API backend which in turn talks to a MariaDB SQL Database. Searching / Filtering for Assets is one of the most important features of such a System and i am stumped on how to properly implement this search endpoint.
For various reasons the core of the database layout relations looks something like this:
|--> vendors
|--> products --> product_types
assets
|--> locations --> addresses --> customers
Going with such a layout means that i have to join allot of tables when Searching for Assets that are at for example are at a specific Address, Customer or have a specific product_type.
The Main Problem i am facing is that we need to search in multiple different ways. One time we need a full text search across almost all columns on all joined tables (all the tables above or more). the next time we need to search for all assets at a given location. After that its by product_type and username and the list goes on.
Till now i have basically written a new SQL query each time but that is not maintainable, nice to work with and not flexible enough if the user wants to filter in specific ways.
The only way to avoid this mess that i could think of is writing a dynamic SQL Query Generator. Which are words i never wanted to speak or think i could implement without exploits and bugs. The only thing that remotely looked like the solution on the Internet was GraphQL but nobody here likes the syntax of it and implementing it seams to be even more of a pain than generating SQL dynamically.
Any Advice would be very much appreciated.