4 ms·
You might be over thinking this... something like this might work out just fine. (I wouldn't necessarily do things this way, but rather keep a list of clauses a
by mbreese 12y ago
You might be over thinking this... something like this might work out just fine. (I wouldn't necessarily do things this way, but rather keep a list of clauses and join them with " AND " to avoid keeping track of the "WHERE"s and "AND"s, but you get the point...)
sql = 'SELECT * FROM products'
args = []
if title:
sql += ' WHERE title LIKE %?%'
args.append(title)
if upc:
if args:
sql += ' AND'
else:
sql += ' WHERE'
sql += ' upc LIKE %?%'
args.append(upc)
if price_min:
if args:
sql += ' AND'
else:
sql += ' WHERE'
sql += ' price >= ?'
args.append(price_min)
if price_max:
if args:
sql += ' AND'
else:
sql += ' WHERE'
sql += ' price <= ?'
args.append(price_max)
if order_by_name:
sql += ' ORDER BY name'
elif order_by_price_low:
sql += ' ORDER BY price'
elif order_by_price_high:
sql += ' ORDER BY price DESC'
conn.query(sql, args)
- ad_hominem 12y agoThis is a query builder. If you're using your programming language to dynamically assemble the SQL statement fragments at runtime, then you're using a query builder regardless of if it is a library with a fancy DSL that assembles an in-memory SQL AST or some kind of ad-hoc string concatenation you rolled yourself like this. The question I'm asking is pointed towards the people who are implying that you can "just use SQL" as static statements that are not dynamically assembled. Like a static function or prepared statement that takes some parameters, and at runtime you only pass in those parameters - not rejigger the actual SQL statement fragments.
- mbreese 12y agoYes, logically, it's a query builder. But it's one that doesn't force any extra dependencies in my project and is very easy to troubleshoot. Seriously, how is this different from "just use SQL". SQL was never really intended to be used by itself (except for manually typing queries into a console). Yes, what I wrote is code that writes code, but it's far different from a larger ORM or querying framework.
- ad_hominem 12y agoDisregarding my edit and how your query builder is irrelevant to my question anyway, if I change the requirement to allow the user to sort by multiple columns instead of one, that case statement is going to either combinatorially explode or you'll also have to add some ad-hoc SQL escaping to guard against SQL injection since you can't use bound parameters in ORDER BY clauses. It's easy to see how libraries like Sequel get invented to help people do this, and I don't really see what the problem is when the DSL stays close to SQL semantics.
- mbreese 12y agoDealing with multiple ORDER BY clauses isn't any more difficult, but you'd have to figure out how to get the user input into some kind of list. That will require validation too... Just using an ORM doesn't let you ignore input validation. You wouldn't trust a user to use their own column names in a WHERE clause anymore than you'd trust them in an ORDER BY clause. Trying to differentiate between a DSL that interfaces with SQL and SQL is nonsensical. SQL is the original DSL.
- rdtsc 12y ago> SQL was never really intended to be used by itself. Hmm I thought that is how it was intended to be used? If it didn't, it would look like datalog, lisp or some binary protocol with prefixed lengths and whatnot. It was intended to be typed in by analysts at a console. Who would then print the report on the dot matrix printer and mail to the headquarters or something of that sort.
- mbreese 12y agoThat data has to get into the RDMBS somehow... I guess a better wording may have been to say that SQL was never intended to be used in isolation. It can be, but you've always had interfaces to other languages / platforms. SQL is a data manipulation and retrieval language - the rest of the business logic has to be performed somewhere else (excepting complex stored procedures).
- jbergens 12y ago
- falcolas 12y agoI've done this quite a bit as well: string building is pretty well understood, and works remarkably well.
- jimbokun 12y agoNow how is this an improvement over the Sequel example? I understand the impulse to "Just write SQL." But in practice, with all the string concatenation needed to generate actual queries, you can't really see what the SQL will be without running all the code in your head anyway.
- mbreese 12y agoIn practice, I'd reduce this to a query building function in my own code, so the readability would be much better. The benefit is that you don't need to include another library for it to work. Well, that and it is a strategy that works for all languages, not just Ruby.
- scott_s 12y agoAnd the downside is now you need to spend effort maintaing and debugging your custom query builder.
- IanCal 12y agoYeah but a lot of it is quite generic, so could be reused in a few places and therefore make that extra effort worthwhile. In fact, it might be handy in a few places so you could package it up and then have a single place you have to maintain the query building code for several projects. Wait a minute...
- mbreese 12y agoI think you're over estimating the amount of effort that I spend on this.
- adrianmsmith 12y agoI know this isn't central to your point, but I thought I'd say it anyway. These days I have taken to doing: sql = "SELECT * FROM products WHERE TRUE" .... if upc: sql += ' AND upc LIKE %?%' .... This saves having to worry about "AND" and "WHERE", and the extra "WHERE TRUE" results in the same execution plan (thus the same performance).
- clebio 12y agoAwesome. That's such a straight-forward solution to one of the crappy edge-cases that SQL is so bad about (ending lists without commas, like JSON requires too, being another).
- chaz 12y agoI also add an extra column to my SELECTS: SELECT users.id, users.email, 1 FROM users I can just use "--" to comment out any line without having to manage the last comma. Especially useful when building aggregations.