Insert existing query (as a string) into another query

Queries and Resources
1

Hi all,

I have quite a huge query with a lot of subqueries. I'd like to split it for easier debug/understanding.

I know we have MySQLResource.query property which is almost what I need to insert into another query

SELECT * FROM ({{ MySQLResource.query }}) mysubquery WHERE id IN (...)

but this thing inserts "\n" too and overall doesn't seem it was ment to work that way

I was trying to use SQLfromJson and use MySQLResource.dataArray instead, but this is something else. I need to work directly with Mysql.

Any suggestions?

Seem to me more like a feature request atm...

Thanks

2

Hey @egor and welcome to the community! Could you give a little more detail on what your master query looks like and what your use case is?

3

hi Justin,

SELECT * FROM (SELECT 
  orders.id, 
  orders.to_address_id,
  orders.user_id,
  orders.created_at, 
  orders.status_updated_at, 
  orders.priority,
  (SELECT title     FROM statuses WHERE orders.status_id = id) as status,
  (SELECT COUNT(*) AS cou FROM order_statuses WHERE order_statuses.`order_id` = orders.id AND order_statuses.`status_id` = 13) AS st_13_times,
	(SELECT email FROM users WHERE id = orders.user_id) as email
FROM orders
WHERE orders.deleted_at IS NULL
ORDER BY orders.id DESC ) # end of sub-select here
 orders WHERE 
    EXISTS (SELECT * FROM order_statuses WHERE order_id = orders.id AND status_id = 13)  AND (TRUE) # some more conditions here
4

Hey Egor!

All of our SQL queries are converted to prepared statements to prevent SQL injection, meaning that table/database names and SQL functions aren't able to be defined using a string created dynamically by default.

You can disable this setting in the resource setup, but keep in mind the potential of submitting dangerous SQL through any of the variables referenced in a query. If that's something you'd like to explore, I often recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.

​

1836×678 44.1 KB
​

1 Like
5

What about SQL Library? Why can't we use it as a building blocks for more complex Queries? :wink:

6

Hmmm… Would those normally be Python SQL Libraries? We don’t support python, but if there is a JS equivalent you could import a JS helper library (https://docs.retool.com/docs/custom-js-code) and then with prepared statements disabled if that generates SQL as a string it would work