Skip to content

Instantly share code, notes, and snippets.

@benoror
Created October 5, 2023 16:08
Show Gist options
  • Save benoror/e7fcab5dc9e3fbf2e6994b585d7cbcbd to your computer and use it in GitHub Desktop.
Save benoror/e7fcab5dc9e3fbf2e6994b585d7cbcbd to your computer and use it in GitHub Desktop.
Modularize a long/complex SQL query in Ruby
def my_big_ish_report
my_long_query = ```sql
SELECT a,b,c FROM ... AS q_a
JOIN (
SELECT ... ' Sub-query 1
WHERE client_id = 'foobar'
) sq_1 ON q_a.fkey_id = sq_1.id
INNER JOIN (
SELECT x,y,z FROM ... ' Sub-query 2
JOIN (
SELECT ... ' Sub-query 1 ' Sas as Sub-query 1 above ^
WHERE client_id = 'foobar'
)
)
...etc...
```
results = ActiveRecord::Execute::SQL(my_long_query)
# ...
end
# Modularize with methods & interpolation
def my_client_subquery(client_id) #name it more semantically according to it's responsibility
```
SELECT ... ' Sub-query 1
WHERE client_id = '#{client_id}'
```
end
def my_big_ish_report
client_id = 'foobar' # Make sure to sanitze if is user input, to avoid SQL injections
my_long_query = ```sql
SELECT a,b,c FROM ... AS q_a
JOIN (#{my_client_subquery(client_id)}) sq_1 ON q_a.fkey_id = sq_1.id
INNER JOIN (
SELECT x,y,z FROM ... ' Sub-query 2
JOIN (#{my_client_subquery(client_id)})
)
...etc...
```
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment