Best Method for Searching Database with Power Table

I have a power table that I am using to display the results of a query, but I want the query to have a dynamic where clause. I was using a few toggle buttons to determine if a dropdown’s value is used in the where clause or not. It works for simple searches, but I have 5 search parameters and the where clause expression is getting hairy fast. I thought about hard coding the where clauses for a case switch, but for 5 variables that would be 32 possible combinations for the where clause and I may add more. What is the best method to accomplish this? I also tried using table filters but it is super laggy.

Possibly the Row Selector component could help here. But I think you can do 5 dropdowns as well. Not sure I follow you completely. Do you need to do something like this to allow cases where one dropdown filter is not used?

WHERE (Column1 = '{Root Container.Column1 Dropdown.selectedLabel}'
OR '{Root Container.Column1 Dropdown.selectedLabel}' = 'ALL')

AND (Column2 = '{Root Container.Column2.selectedLabel}'
OR '{Root Container.Column2.selectedLabel}' = 'ALL')

I was using this expression to use in a runPrepQuery, but it is too confusing and doesn’t catch every case

concat("where ", 
		if({Root Container.serialToggle.selected} || {Root Container.ackToggle.selected}, '', concat({Root Container.Start Date.time}, " between ",
		concat("'",{Root Container.Start Date.date},"' and '",{Root Container.End Date.date},"'"))),
		if({Root Container.userToggle.selected},concat(" and ",{Root Container.User.type}, ' = ', "'",{Root Container.User.selectedStringValue},"'"),''),
		if({Root Container.styleToggle.selected},concat(" and style = '",{Root Container.style.text},"'"),''),
		if({Root Container.serialToggle.selected},concat(" and serial = ",toStr({Root Container.serial.intValue})),''),
		if({Root Container.ackToggle.selected},concat(" and ackno = ",toStr({Root Container.ackno.intValue})),''),
		if({Root Container.fabricToggle.selected},concat(" and fabric = '",{Root Container.fabric.text},"'"),''))

This is the kind of thing that ought to not be a binding. I would run a prepQuery in an asynchronous thread, constructing the query in the script with the chosen criteria (and using ? parameters for the data). Even better would be to have the background thread use system.util.sendRequest to have the query constructed and run from the gateway, allowing the client to not need legacy database access.

1 Like

Grade time, how bad did I do?

def dynamicQuery(table, colList, paramList, timeCol, timeWindow, selectAll):
	"""
		table = name of table to query
		colList = list of columns to filter by
		paramList = list of parameters to match with colList
		timeCol = name of timestamp column
		timeWindow = list of datetime str for start and end
		selectAll = True selects all columns in table, false selects only columns included in colList
	"""
	# Create selection 
	if selectAll:
		columns = '*'
	else:
		columns = ','.join(colList)
	
	# create where clause
	clause = ''
	tupList = zip(colList, paramList)
	count = len(tupList) - 1
	for i, tuple in enumerate(tupList):
		clause += '%s = %s' % (tuple[0],tuple[1]) 
		if i < count:
			clause += ' and '
			
	# Add Time contraint
	if timeCol:
		clause += ' and %s between %s and %s' % (timeCol, timeWindow[0], timeWindow[1])
	
	# Construct query 
	query = "SELECT %s FROM %s WHERE %s" % (columns, table, clause)

Using tuple as a variable name is bad practice. You’re overriding the built-in tuple type. Alarm bells should go off if your variables get syntax highlighted

Consider comparing the lengths of colList and paramList to avoid unexpected behavior

Then I think making things more terse improves readability in this case

	# Create selection 
	if selectAll:
		columns = '*'
	else:
		columns = ','.join(colList)

could be written as

columns = '*' if selectAll else ','.join(colList)

and this monster

	clause = ''
	tupList = zip(colList, paramList)
	count = len(tupList) - 1
	for i, tuple in enumerate(tupList):
		clause += '%s = %s' % (tuple[0],tuple[1]) 
		if i < count:
			clause += ' and '

could be written as

clause = ' and '.join([k + ' = ' + v for (k, v) in zip(colList, paramList)

Upon first read I thought it was awkward you used join earlier then decided not to for the ' and '. Then I noticed you were using a count with enumerate when the whole point of enumerate is that it keeps track of the count for you

Condensing these into one line helps them read like language and then you don’t have to keep track of so many moving pieces. Especially for stuff that just gets thrown away like count. Which is ambiguous, since “count” could be interpreted as iteration rather than length

and obviously if there’s human input you’re open to SQL injection and you’d want to redo this to use runPrepQuery

3 Likes

Not to mention that taking out the additional evaluation and assignment per loop will make the code more efficient.

In light of this I think even the zip can be removed:

clause = ' and '.join([k + ' = ?' for k in colList])
2 Likes
clause = ' and '.join([col + ' = ?' for col in colList])

Good call. That’s definitely the way to go. Then system.db.runPrepQuery(query, [paramList]) and you’ve made your code more readable and protected against SQL injection

That is pretty slick. Thanks for the suggestions. Note I had to remove the brackets around paramList because it is already a list

def dynamicQuery(db, table, colList, paramList, timeCol, timeWindow, selectAll):
	"""
		db = name of database schema
		table = name of table to query
		colList = list of columns to filter by
		paramList = list of parameters to match with colList
		timeCol = name of timestamp column
		timeWindow = list of datetime str for start and end
		selectAll = True selects all columns in table, false selects only columns included in colList
	"""
	# Create selection 
	if selectAll:
		columns = '*'
	else:
		columns = ','.join(colList)
	
	# create where clause
	clause = ' and '.join([k + ' = ?' for k in colList])
			
	# Add Time contraint
	if timeCol:
		clause += ' and %s between %s and %s' % (timeCol, timeWindow[0], timeWindow[1])
	
	# Construct query 
	query = "SELECT %s FROM %s WHERE %s" % (columns, table, clause)

	return system.db.runPrepQuery(query,paramList,database = db)
1 Like

You need ‘?’ parameters for your time window, too.

Finally, I recommend you use your DB flavor’s identifier quoting for column names. (Double quotes are the standard, which most DBs can be made to accept.)

3 Likes

So, you can take it a bit further if you really want to:

def dynamicQuery(db, table, colList, paramList, timeCol, timeWindow, selectAll):
	"""
		db = name of database schema
		table = name of table to query
		colList = list of columns to filter by
		paramList = list of parameters to match with colList
		timeCol = name of timestamp column
		timeWindow = list of datetime str for start and end
		selectAll = True selects all columns in table, false selects only columns included in colList
	"""
	# Create selection 
	columns = '*' if selectAll else ','.join(colList)

	# create where clause
	clause = ' and '.join([k + ' = ?' for k in colList])
			
	# Add Time contraint
	if timeCol:
		clause += ' and %s between ? and ?' % (timeCol)
	        paramList.extend(timeWindow)

	# Construct query 
	query = "SELECT %s FROM %s WHERE %s" % (columns, table, clause)

	return system.db.runPrepQuery(query,paramList,database = db)

Appreciate the pointers!

Also, I don’t know why, but this terrifies me :rofl:
image

4 Likes

Man, someone should really write a module that exposes DBQuery to scripting… :slight_smile:

5 Likes

Meh. A solution looking for a problem. I think that sort of SQL code generation should be restricted to situations where multiple DB flavors must be supported. Or for rather complicated cases. Otherwise the cure is worse than the disease.

1 Like

Yeah, that’s fair. I think it’d be great to expose it in some way, for stuff like Exchange resources (could drastically simplify Sales Engineering’s Ad Hoc Trend template for Vision, for instance), but it’s tricky to “expose” things like that without overwhelming new users.