Remix clone Hacker News

The thought process can be the same in SQL. You can start by writing SELECT * FROM Account; then add your JOIN to User, then add your predicates. Then you can refine it – here, if I’m understanding the code correctly, you’re using User for a JOIN but never return anything from that table, so you could turn it into a semi-join (WHERE EXISTS) and likely get a speed-up.

▲

iterateoften 4 days ago | parent [-]

> then add your predicates

How are you canonically storing these predicates in code to reuse over 5-10 queries?

▲

cess11 3 days ago | parent [-]

Why would you want to?

▲

iterateoften 3 days ago | parent [-]

The fundamentals of software design. DRY

If you have a complex predicate that defines a set of rows, and you use that set in many different queries, are you rewriting it each time?

▲

cess11 3 days ago | parent [-]

So you don't actually have an answer for me?

Maybe I do, maybe I don't. It depends. Most likely I won't, unless it's used in like 80 % or more of queries and the problem domain guarantees that it will basically never change and if it does it will for sure, absolutely, change everywhere at once.

I like easily testable SQL queries that are easy to read, right there in the code. No need to execute anything to get to the SQL that would execute in the database. 'But then I need to write thousands of queries?!' Yes, you do, and you'll have a chill and easy time whenever they change, and they will if your software gets use and your data grows. Because you can just search/replace on the SQL text, and don't need to figure out every place some mutilated piece might get called from and probably get it wrong a few times or need to figure out how to split it into two or more fragments.

In some cases you can figure out a robust way to generate SQL but it's rare, it mostly comes up in one-off scenarios.

	▲	Eikon 3 days ago \| parent [-]
		I completely agree. People often bring up edge cases like this, but they rarely occur in real-world scenarios. If you have such a complex predicate that’s used everywhere, you should probably be using a view anyway.