Query Script

aallman

I am creating a script that starts with a modal window and runs a couple of stored procedures and automatically joins the resulting tables. These are fairly large tables, and I want to add an option for the user to run something similar to the Query Builder, where they can select columns as filters and input the data they want filtered. 

How would I go about doing this before the tables are open? I want to minimize the time it takes to run the procedures and join the tables, so filtering the data after the final table is open isn't really what I want to do. 

Any suggestions would be greatly appreciated!

pmroz

You can add prompts to your modal window to prefilter the data.  Here's a simple example that allows a user to pre-select products and locations before the query runs.  I've hardcoded the list of values to choose from, but ideally you would get this dynamically with SQL queries.

// Hardcode or get dynamically from the database
product_list = {"AAAA", "BBBB", "CCCC", "DDDD"};

// Hardcode or get dynamically from the database
location_list = {"China", "England", "Germany", "Italy", "Japan", "USA"};

nw = new window("Get Data", << modal,
	panel box("Pre-Filter",
		hlistbox(
			product_cb  = listbox(product_list),
			location_cb = listbox(location_list)
		),
	),
	panel box("Actions",
		hlistbox(
			button box("OK",
				ok_pushed = 1;
				selected_products  = product_cb  << get selected;
				selected_locations = location_cb << get selected
			),
			button box("Cancel",
				ok_pushed = 0;
			)
		)
	)
);

if (ok_pushed,
	product_in_list = "";
	location_in_list = "";

	print(selected_products, selected_locations);
	if (nitems(selected_products) > 0,
		product_in_list = "t.products IN ('" || 
			concat items(selected_products, "', '") || "')";
	);
	if (nitems(selected_locations) > 0,
		location_in_list = "t.locations IN ('" || 
			concat items(selected_locations, "', '") || "')";
	);

	print(product_in_list, location_in_list);
	// run SQL query
);

 

Output from log:

{"AAAA", "CCCC"}
{"England", "Germany", "Italy"}
"t.products IN ('AAAA', 'CCCC')"
"t.locations IN ('England', 'Germany', 'Italy')"