company i worked for 6 years ago did something similar years prior to that since ORMs werent readily on the market. their Entity had a map for all their columns, and using inheritance could build its general select statement with joins. then they had the concept of a CriteriaSet which did similar to what you want to do. it essentially just tacked on an extra piece on the end of the where clause.
say Product inherits from SecuredEntity (which adds a join to a security table and includes a condition in the where clause) which inherits from Entity (which handles the base data access code)
eg:
Code
CriteriaSet cs = new CriteriaSet();
cs.Add("Quantity", CriteriaType.EQUAL, userinput);
cs.Add("Id", CriteraType.IN_SQL, "select id from cart");
cs.Limit = 10;
List<Product> products = new Product().find(cs);
so a find sql without criteria would look similar to this (using the mapping and superclasses)
Code
select id, name, quantity, code, ....
from products p
join securitytable st on st.entity_guid = p.guid
where 1=1
and st.user_oid=1
and st.access > 0
limit 2147483647
then using the criteria set just did this:
Quote
select id, name, quantity, code, ....
from products p
join securitytable st on st.entity_guid = p.guid
where 1=1
and st.user_oid=1
and st.access > 0
and ((quantity = 4) and (id in (select id from cart)))
limit 10
they decided early on they weren't building an entire engine. they just wanted a tool to add extra stuff in the where clause. and they'd just nest CriteriaSets as needed. maybe it'll give you some ideas?
Quote
There is currently no good way to retrieve by complex queries
i just mentioned the insert because flat insert...values is very easy. SELECT is much more difficult. and when you start trying to write something that will handle your use cases, you'll quickly add more complexity.
eg:
select a, b, c, (select * from blah join boohoo ...) from (select * from nastytable union select * from nicetable) where some_id in (select id from another_table) and (some_id not in (select id from crap_table join asdf ....) or new date().add(3, 'd') < other_date) and not exists (select 1 from ....)
now the code you write via your sqlbuilder will be less readable than the sql. and you don't want to have this sql first, then sanitize it. if your unit tests aren't well maintained, then you'll be in for a nasty surprise when you add an extra feature to support something you want and it impacts existing code quietly.
Quote
other than hard-coding SQL strings (which is the most readable, but its a hardcoded string...)
i suggest just writing stored procedures then passing params.