in labkey a query view can be given something like a container filter, hard coded in the SQL, which forces that query to return all results from the current folder plus children? here is the application:
one of our labs has a folder with workbooks. these workbooks store files. their system uses a master list, located in the parent folder of these workbooks. this list has a column called 'workbook', which is a pointer to the workbook itself.
what they want to do is something like:
SELECT * from expt_db e JOIN exp.datas d [from this folder plus all the child folders] on (e.workbook=d.container)
the point is to make a master list of files, joined to other metadata about the expt, contained in lists.expt_db. can such a thing be done?
if it cannot be done directly, one idea I had was to created a select query that is just 'select * from exp.datas', then change the default view to have a container filter that will show all folders. maybe if you join lists.expt_db to that it would accomplish the desired effect.
thanks for any help. |
| |
| jeckels responded: |
2011-06-03 13:15 |
Hi Ben,
We don't have a way to specify a container filter inside of a custom query. However, we've done work in the past and I recently made an improvement in 11.2 so that if you set a container/folder filter on the view, it should get propagated into the query and respected by the various subqueries. So, I think that your suggested alternative should work as you intend, at least in 11.2.
Thanks,
Josh |
| |
| Ben Bimber responded: |
2011-06-03 13:34 |
hi josh,
there isnt a way to specify a view in the SQL is there? therefore any time a query is referenced from a different query, it always uses the default view, right?
while it might be convenient here, isnt this behavior kinda of dangerous and potentially confusing? while it's not directly related to this situation, I had another example where the default view of a table had a filter applied. any lookups to that table were apparently using this filtered view as the lookup source, which meant some lookups appeared broken when they should not be. it just seems like the view is most appropriate as directing user-interaction, not necessary programmatic ones. |
| |
|
|