Feature - Add in list/not in list to comparison with upload callback for list

[prospectoria]

The gwt-Listbox where the comparison is defined, e.g. "is", "is not", "like", etc. Needs to have two options added for "in list" and "not in list". If either of these are selected, I need for the gwt-Suggestbox to change to a button that initiates a Javascript callback. The callback needs to be configurable so that I can add some javascript (and server code) that prompts the user to upload a file that has a list of values. I will use the list to create a temp table and do something like:

SELECT x,y,z FROM foo f WHERE EXISTS (SELECT 1 FROM temp_table t WHERE f.x = t.x)

or

SELECT x,y,z FROM foo f INNER JOIN temp_table t ON t.x = f.x

This should be added as a new type in meta.types, that would probably be ideal. If you have some input as to a better way to compare a big list against a query and use EXISTS/NOT EXISTS against that list, I'm open to suggestions. Our problem is that the list could be quite large which precludes us from using IN/NOT IN.

[salk31]

Not sure what db you are using but most dbs would rather you used one temp table with a key? So maybe sub clause something like EXISTS (SELECT 1 FROM upload_table ut WHERE f.x = t.x AND ut='someKeyPreviouslyReturnedFromServer'

I'm also assuming they don't want to re-use the list they upload as otherwise would be better to have some sort of "my lists" step prior to using the query builder...

[salk31]

Also do you know if you database will consider: WHERE f.id IN (SELECT t.id FROM temp_table t ...) is equivalent to EXISTS? Think most modern dbs can cope with both... Might make it easier to implement

[prospectoria]

Using mysql on the back-end, but may change that depending on how it performs once I glue all this together. Also yes, I would consider the WHERE x IN (select blah) equivalent to EXISTS, I believe, though, that mysql has a limit to using "IN/NOT IN". This one may be a bit tricky. On the front end, the user would select IN/NOT in and the suggest box would change to a button that would let them upload a file. I already have the file upload code, I just need a hook to add in my upload callback. Once the file is uploaded I can return some kind of identifier that can be used in the SQL that's generated, then when I build the query on the back end( by pulling apart and reconstructing the SQL), I can replace the identifier with the proper SQL. In other words, I don't think you need to build the SQL for the query, just allow a file upload and somehow flag it, so when I read the data I can build the query. Let me know if that makes sense or if you think a different approach would be better.

[prospectoria]

Closing this as the custom callback feature seems to have addressed the needs of this issue. You can now configure custom callbacks to call subsequent functions, etc. for file uploads, then generate your own SQL for the joins needed.