Hacker News Reader

Comment by megaman821 | Hacker News Reader

Question to the SQL-only people, how would you handle something dynamic? If I have a database of shoes and want people to be able to find them by brand, size, style, etc., what does that look like?

View on HN

If you don't want to maintain several queries, you could write something like

    SELECT *
    FROM shoes
    WHERE (CASE WHEN :brand_id IS NOT NULL THEN brand_id = :brand_id ELSE TRUE END)
    AND (CASE WHEN :size IS NOT NULL THEN size = :size ELSE TRUE END)
    AND (CASE WHEN :style IS NOT NULL THEN style = :style ELSE TRUE END)

That is pretty good. Much more readable than gluing a bunch of strings together.

And if you want to make it even more readable, those inner expressions can be wrapped into functions.

If statements that either add conditional statements or blank lines to the SQL block. There are a lot of tradeoffs to the pure SQL method but I prefer being able to look up exact snippets in the codebase to find things.

Reply on news.ycombinator.com