I have written several queries that span hundreds of lines, primarily for "advanced" search functionality that lets you search by a huge array of potential parameters. Without using the query builder provided by the ORM the code would become intractable to mortals quickly. Having several dozen different parameters that are only set under certain special conditions is much easier if you can use your regular programming language to evaluate those conditions as opposed to writing everything in one big SQL statement even with named parameters.
Writing code like this here really gets old:
q = query(SELECT a FROM table WHERE (:PARAMETER_1_DISABLED OR table.field < :PARAMETER_1), ... 20 other parameters)
q.setParameter(PARAMETER_1_DISABLED, condition)
q.setParameter(PARAMETER_1, parameter)
.. 20 other parameters
compared to just
Type.list() {
if(condition) {
field < parameter
}
.. 20 other parameters
}
Also please don't tell me to avoid this problem by concatenating the SQL conditions to dynamically create the query... The end result is still ugly and might even introduce security problems.
Writing code like this here really gets old:
q = query(SELECT a FROM table WHERE (:PARAMETER_1_DISABLED OR table.field < :PARAMETER_1), ... 20 other parameters)
q.setParameter(PARAMETER_1_DISABLED, condition)
q.setParameter(PARAMETER_1, parameter)
.. 20 other parameters
compared to just
Type.list() {
}Also please don't tell me to avoid this problem by concatenating the SQL conditions to dynamically create the query... The end result is still ugly and might even introduce security problems.