more compact WHERE conditions

[leeoniya]

here's an idea for more compact where conditions.

1. placing the condition keywords (AND, OR, etc) at the end of the lines makes it easier to focus on the participating columns rather than on statement construction.
2. when IN is a simple list rather than a sub-query, having it compact saves a bunch of space. Even simple sub-queries like SELECTgroupFROMstuffWHERE type = 5 are better in-lined (though the heuristics for this could be fairly complex and probably add too much extra code).

   current

proposed

[jdorn]

Not sure I agree on point 1. In the proposed version, the "OR" kind of gets lost. If I just glanced at it, I would probably assume everything was "AND".

Right now, the logic for when to use inline parentheses allows at most 3 non-whitespace tokens. Your example has 5 (including commas). I used a small number since if you have long strings instead of just single characters, it's much harder to read in a single line:

WHERE 
  country IN ('Antigua and Barbuda', 'Bosnia and Herzegovina', 'Democratic Republic of the Congo')

vs.

WHERE 
  country IN (
    'Antigua and Barbuda', 
    'Bosnia and Herzegovina', 
    'Democratic Republic of the Congo'
  )

Maybe I can change it to have a max number of characters instead of using tokens. I'll have to play with some values and see how that looks.

[leeoniya]

yes the IN display is a tough one. i'd argue, though, that most of the time the contents of an IN clause will be country ids or some other, simple indexed property rather than full country names or very long strings. in my experience at least this has pretty much always been the case and if it wasnt, it was something waiting to be optimized. i think LIKE clauses are much more prone to the long stringness and fuzzy matching. i often wish there was a IN-LIKE() construct :)

for point 1, i think there are benefits to both views. with the current implementation it's harder to fish out all the columns that are involved. with the alternative it's harder to see the logic at-a-glance. the benefit of columns-first is that it's more in-line with the rest of the formatting where you expect the table or column to be on the left side of the expressions. when you have left-aligned AND or OR and names, it interrupts the 'flow' maybe.

[jdorn]

What do you think about this as a solution to the IN clause problem?

Desired formatting:

SELECT 
  * 
FROM 
  MyTable 
WHERE 
  Col IN ('value1', 'value2', 'some other value',
          'an even longer value', 'some', 'short', 
          'values', 'a', 'b', 'c', 'd', 'e', 'f')

Current formatting:

SELECT 
  * 
FROM 
  MyTable 
WHERE 
  Col IN (
    'value1', 
    'value2', 
    'some other value', 
    'an even longer value', 
    'some', 'short', 
    'values', 
    'a', 
    'b',
    'c', 
    'd', 
    'e', 
    'f'
  )

[leeoniya]

i think if there are spaces inside the values like 'an even longer value', the one-per-line is easier to read. but as i mentioned earlier, in the vast majority of the queries i've encountered, the IN is either indexed keys/enums with underscores rather than spaces or an entire sub-query. so while i'd prefer 'a', 'b', 'c', 'd', 'e', 'f', if the values were 'a b','c','d e','f', i'd rather see

'a b',
'c',
'd e',
'f'

but that's usually not the case.

i'd still like to see an option to have the compact & appended WHERE clause tho that puts all the column names on the left.

anyways, thanks for looking into this.