• Sign In
  1. Messages

Lookup to core.Containers table different from other regular tables?

LabKey Support Forum (Inactive)

View Message

view list print
Lookup to core.Containers table different from other regular tables? Leo Dashevskiy  2016-05-26 13:40
Status: Closed
 
Hi guys!

I've got a module based query that has the need for the container column.

https://test.immunespace.org/query/Studies/executeQuery.view?schemaName=study&query.queryName=GEE_studyCohortsInfo

Simply including "container" as an extra column pulled in the container's EntityId, but I need the container's name.

So I modified the query's metadata to make that column a lookup (foreign key) and the column is now a proper clickable link with the correct text in the rendered view and in the Schema Browser it is a link, which expands properly to show all of the available columns of the core.Containers table.

But I'm unable to include the name of the container in the query as part of another column either by specifying "container.Name", which results in an error: "Unknown field", or by using "container", which pulls in the EntityId, not the name.

My query XML contains the following:

            <table tableName='GEE_studyCohortsInfo' tableDbType='TABLE'>
                <columns>
                    <column columnName='Container'>
                        <fk>
                            <fkColumnName>EntityId</fkColumnName>
                            <fkDbSchema>core</fkDbSchema>
                            <fkDisplayColumnName>DisplayName</fkDisplayColumnName>
                            <fkTable>Containers</fkTable>
                        </fk>
                    </column>
                </columns>
            </table>

I need to include the following column: cohort || ' (' || <container name> || ')'

By the way, it appears that a module based query behaves differently than a UI based one:

Here is a link to a copy of the query (with the metadata included):
https://test.immunespace.org/query/Studies/begin.view?schemaName=study&queryName=Test#sbh-qdp-%26study%26Test
The lookup does not work. I played around with <fkDisplayColumnName useRawValue/>, but that didn't help.

What am I doing wrong?

Thanks.
-Leo
 
 
Jon (LabKey DevOps) responded:  2016-05-26 16:01
Hi Leo,

It looks like your XML between the test version to the actual one are slightly off:

Working version for https://test.immunespace.org/query/Studies/executeQuery.view?schemaName=study&query.queryName=GEE_studyCohortsInfo :

<?xml version="1.0" encoding="UTF-8"?><tables xmlns="http://labkey.org/data/xml">
            <table tableDbType="TABLE" tableName="GEE_studyCohortsInfo">
                <columns>
                    <column columnName="Container">
                        <fk>
                            <fkColumnName>EntityId</fkColumnName>
                            <fkDbSchema>core</fkDbSchema>
                            <fkDisplayColumnName>DisplayName</fkDisplayColumnName>
                            <fkTable>Containers</fkTable>
                        </fk>
                    </column>
                </columns>
            </table>
        </tables>

The non-working version for https://test.immunespace.org/query/Studies/executeQuery.view?schemaName=study&query.queryName=Test :

<tables xmlns='http://labkey.org/data/xml'>
            <table tableName='GEE_studyCohortsInfo' tableDbType='NOT_IN_DB'>
                <columns>
                    <column columnName='Container'>
                        <fk>
                            <fkColumnName>EntityId</fkColumnName>
                            <fkDbSchema>core</fkDbSchema>
                            <fkDisplayColumnName>DisplayName</fkDisplayColumnName>
                            <fkTable>Containers</fkTable>
                        </fk>
                    </column>
                </columns>
            </table>
        </tables>

Can you modify your XML to the one that works and see if it makes a difference?

Regards,

Jon
 
Leo Dashevskiy responded:  2016-05-26 16:17
Jon, I made the SQL and the XML codes identical, except for the query name.

Still, the UI based one behaves differently than the file-based one, and I cannot obtain the container name (see the display column).

Thanks.
-Leo
 
Jon (LabKey DevOps) responded:  2016-05-26 16:26
Hi Leo,

Yes, I do see the query/table names are different, but I also see that your XML in the test version is missing the XML declaration as well.

<?xml version="1.0" encoding="UTF-8"?>

Although version 1.0 is optional to provide the declaration, I've seen odd behavior when it isn't included sometimes.

Can you add that line into your XML and give it another try?

Regards,

Jon
 
Leo Dashevskiy responded:  2016-05-26 16:41
Jon, I added the XML declaration to the XML metadata portion of the UI based "Test" query and that didn't make it return the same contents as the file based "GEE_studyCohortsInfo" one.
 
Jon (LabKey DevOps) responded:  2016-05-27 11:33
Thanks Leo,

Just wanted to make sure that both queries were as close as possible here.

Let me see if I can reproduce this locally on my end.

Thanks for your patience.

Regards,

Jon
 
Jon (LabKey DevOps) responded:  2016-05-27 22:12
Hi Leo,

I figured it out and it's surprisingly a simple fix.

So while trying to figure out a repro to do locally, I decided to do the following to the URL and add in a containerFilter.

https://test.immunespace.org/query/Studies/executeQuery.view?schemaName=study&query.queryName=Test&query.containerFilterName=CurrentAndSubfolders

https://test.immunespace.org/query/Studies/executeQuery.view?schemaName=study&query.queryName=Test&query.containerFilterName=AllFolders

So when I used either of these, the query worked successfully and the lookups functioned without any issue.

Then I looked at your GEE_studyCohortsInfo query and selected the option for Views > Customize Views and looked at the filters and compared it to Test and found that Test wasn't using a container filter.

If you enable a container filter and save it for all users to use, this will fix the issue.

Regards,

Jon
 
Leo Dashevskiy responded:  2016-05-31 09:02
Jon,

While I appreciate this piece of info (didn't know about the container scoping control within the customize view UI), the original main question was totally about something else: I am not able to pull in a column from the core.containers table (the table being looked up by the container column) into my own query. The column I need is the "Name" or "DisplayName", just the textual string of the container. Specifying "container.Name" as a column in my query results in an error, as noted above.
Please, help with addressing that.

Thanks.
-Leo
 
Jon (LabKey DevOps) responded:  2016-05-31 14:18
Hi Leo,

Since the XML metadata gets applied after the query is parsed, trying to use the Foreign Key defined in the XML on the same query you're authoring causes the field to not be available at the time of application. One way to get around it is to have your query use the Container.Name syntax rather than just Container if you need the name value to appear instead.

Regards,

Jon
 
Leo Dashevskiy responded:  2016-05-31 14:26
That's what I'm saying: using Container.Name does not work for me, it results in an error or may be I'm not understanding exactly, what you're saying.

Please, see for yourself: modify the "Test" query in such a way that it work and a simple textual string (not a link) with just the name of the container appear as a column in this query.

Thanks.
-Leo
 
Matthew Bellew responded:  2016-05-31 15:21
I believe the problem here has nothing to do with the XML, but the SQL. The difference is that the query that doesn't work explicitly selects "container.name", while the query that does work selects "container". Note, that the .xml file applies metadata to the _result_ of the query. So the lookup defined in the .xml is not available to the SQL in the .sql file. This explains why container.name is not valid.

Note that the lookup is available/known by the data region which is why the GEE_studyCohortsInfo grid displays name.
 
Leo Dashevskiy responded:  2016-05-31 15:30
Ok, Matt, thanks, so is there a -clean- way to pull in the container's name without JOIN-s or is that my only option then?
 
Matthew Bellew responded:  2016-05-31 15:52
Well, the real question is why isn't the "container" column in HM_InputSamplesQuery already a lookup? I think it's because it is defined as

   mat.container.entityid as container,

rather than just
 
   mat.container as container

If this column is defined as a lookup, then that definition should just carry though and your outer query should be able to select container.name.

Matt
 
Leo Dashevskiy responded:  2016-05-31 16:15
Kudos to Matt! ;)

I modified the source query snapshot HM_InputSamplesQuery to have the container column be not just a textual string, but a lookup to containers and now the cascading query using it can pull in any columns of the related table!