• Sign In
  1. Messages

Storing and Retrieving a Matrix

LabKey Support Forum (Inactive)

View Message

view list print
Storing and Retrieving a Matrix bill c white  2014-02-12 10:25
Status: Closed
 
I need to store a matrix and retrieve it using the Javascript API. My solution was to upload a matrix to a list with a rowid column to insure the row ordering. I then specify a query with all columns except the rowid SELECTed and use the rowid in an ORDER BY clause. When I get this data back from the Javascript query, the columns are not in the SQL statement order, but some other order I can't determine. Do I need to specify config.columns? Doesn't seem like I need to here when I've already specified them in the SQL. Ideas?

    LABKEY.Query.selectRows({
      schemaName: 'lists',
      queryName: 'regress-regain-snprank-top-100',
      successCallback: onSuccess,
      errorCallback: onFailure
    });
 
 
jeckels responded:  2014-02-12 10:32
Hi Bill,

There are a number of cases where specifying an ORDER BY doesn't work in a way that's intuitive. If you add a sort: "RowId" to the config object you're passing in, you should get the ordering you want.

Thanks,
Josh
 
bill c white responded:  2014-02-12 10:36
Thanks Josh, but the row ordering is fine; it's the column order that is strange.
 
jeckels responded:  2014-02-12 10:42
Hi Bill,

Ah, yes, sorry for jumping to the wrong diagnosis.

In general, I don't recommend relying on particular column ordering. For example, in many cases, the server will include additional columns in the results (such as primary key or other values used to general URLs and other types of metadata). You may be able to specify a list explicitly with the "columns" property to tweak the ordering, but that may not be completely stable either. This is a byproduct of the fact that we're not executing the SQL you pass to the server directly against the database, as you might be used to from JDBC or other database APIs.

Instead, I'd recommend iterating to find columns based on their names. The ordering for each row should be stable within a given response from the server.

Thanks,
Josh
 
bill c white responded:  2014-02-12 10:49
Yikes! This might require me to take a different strategy once I get over 100 columns in the matrix. I guess I could relegate the matrix analysis to R, calling it from JS which seems to be possible, but I wanted to stay in JS given the interactive analysis and visualization I am doing on the matrix. Thanks.
 
bill c white responded:  2014-02-15 06:55
I used name-based column lookups and that works fine. Thanks for the pointer!