3 ms·

I don't know about anti patterns but what I like to do is putting 1=1 after each WHERE to align ANDs nicely and this is enough to create huge dramas in PR revie

I don't know about anti patterns but what I like to do is putting 1=1 after each WHERE to align ANDs nicely and this is enough to create huge dramas in PR reviews.

ffsm8 1y ago
It's always perfectly aligned for me, because enter prefixes 2 whitespace in my ide in SQL files, ending with where a=1 And k=2 And v=3
- egeozcan 1y ago
  But the first condition looks special while it isn't and it sometimes leads to changes touching one too many lines.
DrewADesign 1y ago
> what I like to do is putting 1=1 after each WHERE to align ANDs nicely Frankly, that sounds like one of those things that totally makes sense in the author’s head, but inconsiderately creates terrible code ergonomics and needless cognitive load for anyone reading it. You know to just ignore those expressions when you’re reading it because you wrote it and know they have no effect, but to a busy code reviewer, it’s annoying functionless clutter making their job more annoying. “Wait, that should do nothing… but does it actually do something hackish and ‘clever’ that they didn’t comment? Let’s think about this for a minute.” Use an editor with proper formatting capability, and don’t use executable expressions for formatting in code that other people look at.
- MobiusHorizons 1y ago
  I use `WHERE true` for this. Very little cognitive load parsing that. And it makes AND conditions more copy pastable. Effectively the trailing comma of SQL where clauses
  - DrewADesign 1y ago
    I absolutely cannot see how this would do what IDE formatting can’t, but admittedly the last time I wrote any significant amount of SQL directly was in a still-totally-relevant Perl 5 application. Could you give an example or link to a file in a public repository or whatever that would show this practice in context?
    - MobiusHorizons 1y ago
      These were all adhoc analytics queries, not the sort of thing that get checked in. It means you can copy paste any where condition because they are all of the form AND <condition> otherwise one condition is of the form WHERE <condition 1> So adding it to a query that already has a where clause is a bit more awkward. I use this technique for some analytics queries that all rely on the same base table. It’s not uncommon to start with copying an old query and just adding or removing conditions and grouping/aggregating until I get the right data. Using this format also makes commenting out any condition trivial. WHERE true -- AND some_column = “some value” AND event = “SOME_EVENT_TYPE” AND EXISTS(SELECT * FROM UNNEST(array_column) as v WHERE v = “some value”) I don’t see how you could achieve this result with just IDE formatting.
- tombert 1y ago
  Using `WHERE 1=1` is such a common pattern that I seriously doubt it's realistically increasing "cognitive load". I've seen it used in dozens of places, in particular places that programmatically generate the AND parts of queries. I wasn't really that confused the first time I saw it and I was never confused any time after that.

I don't know about anti patterns but what I like to do is putting 1=1 after each WHERE to align ANDs nicely and this is enough to create huge dramas in PR reviews.

ffsm8 1y ago

It's always perfectly aligned for me, because enter prefixes 2 whitespace in my ide in SQL files, ending with where a=1 And k=2 And v=3

egeozcan 1y ago
But the first condition looks special while it isn't and it sometimes leads to changes touching one too many lines.

DrewADesign 1y ago

> what I like to do is putting 1=1 after each WHERE to align ANDs nicely Frankly, that sounds like one of those things that totally makes sense in the author’s head, but inconsiderately creates terrible code ergonomics and needless cognitive load for anyone reading it. You know to just ignore those expressions when you’re reading it because you wrote it and know they have no effect, but to a busy code reviewer, it’s annoying functionless clutter making their job more annoying. “Wait, that should do nothing… but does it actually do something hackish and ‘clever’ that they didn’t comment? Let’s think about this for a minute.” Use an editor with proper formatting capability, and don’t use executable expressions for formatting in code that other people look at.

MobiusHorizons 1y ago
I use `WHERE true` for this. Very little cognitive load parsing that. And it makes AND conditions more copy pastable. Effectively the trailing comma of SQL where clauses
- DrewADesign 1y ago
  I absolutely cannot see how this would do what IDE formatting can’t, but admittedly the last time I wrote any significant amount of SQL directly was in a still-totally-relevant Perl 5 application. Could you give an example or link to a file in a public repository or whatever that would show this practice in context?
  - MobiusHorizons 1y ago
    These were all adhoc analytics queries, not the sort of thing that get checked in. It means you can copy paste any where condition because they are all of the form AND <condition> otherwise one condition is of the form WHERE <condition 1> So adding it to a query that already has a where clause is a bit more awkward. I use this technique for some analytics queries that all rely on the same base table. It’s not uncommon to start with copying an old query and just adding or removing conditions and grouping/aggregating until I get the right data. Using this format also makes commenting out any condition trivial. WHERE true -- AND some_column = “some value” AND event = “SOME_EVENT_TYPE” AND EXISTS(SELECT * FROM UNNEST(array_column) as v WHERE v = “some value”) I don’t see how you could achieve this result with just IDE formatting.
tombert 1y ago
Using `WHERE 1=1` is such a common pattern that I seriously doubt it's realistically increasing "cognitive load". I've seen it used in dozens of places, in particular places that programmatically generate the AND parts of queries. I wasn't really that confused the first time I saw it and I was never confused any time after that.