Hello!
I would like to filter out of my query (containing just one column) the rows with null values (are they truly NULL or are they empty strings?).
The "Labkey" SQL does not seem to allow clause of the form:
WHERE <curElemMod> IS NOT NULL
does it?
Well, it does allow it, but does not produce what I would expect (it gets me zero rows as the result). Btw, the backend db is PostgreSQL for me.
So then I decided and tried using the store filters, here is my code:
tempSQL = 'SELECT DISTINCT FCSFiles.Keyword."' + curElemOrig + '" AS ' + curElemMod + ' FROM FCSFiles ORDER BY ' + curElemMod;
tempStore = new LABKEY.ext.Store({
autoLoad: true,
filterArray: [
LABKEY.Filter.create(curElemMod, LABKEY.Filter.Types.NOT_MISSING)
],
schemaName: 'flow',
sql: tempSQL
});
FYI, curElemMod is curElemOrig with all of the spaces replaced by underscores.
This also does not seem to eliminate the null rows as they still show up in the comboBox (and cannot be readily selected) being driven by the above store.
Please, advise.
Thanks.
-Leo |
|
Ben Bimber responded: |
2012-05-14 19:57 |
hi leo,
this is difficult to answer without seeing your data, but postgres will treat empty strings differently than null. could you try:
WHERE <curElemMod> != ''
and see if this excludes the records you expect? if this is true, as a general rule i would recommend converting empty strings to null as these sort of problems will creep in. |
|
Matthew Bellew responded: |
2012-05-15 08:26 |
In this case, "keyword" is from a LabKey defined table, and can not be null. I wouldn't have expected there to be any empty strings, but we must have read an FCS file that had an empty keyword name. Weird. My recommendation is the same as Ben's using col != '' in SQL should work. I'm not sure we have a LABKEY.Filter way to express that since we try not to use empty strings because it's too confusing.
Matt |
|
Leo Dashevskiy responded: |
2012-05-15 11:59 |
Thanks guys for the answers.
Here is the original SQL:
SELECT DISTINCT FCSFiles.Keyword."Sample" AS Sample
FROM FCSFiles
ORDER BY Sample
resulting in:
Sample
Comp
PBS
_
(the last row is blank, I used _ to highlight that)
Though, this query:
SELECT count(DISTINCT FCSFiles.Keyword."Sample") AS Sample
FROM FCSFiles
ORDER BY Sample
results in:
Sample
2
I guess count() does not include null/empty rows (I'm still not 100% clear if it's empty or null despite what you say...)
Whereas:
SELECT DISTINCT FCSFiles.Keyword."Sample" AS Sample
FROM FCSFiles
WHERE Sample != ''
ORDER BY Sample
give the following error:
ERROR: invalid input syntax for integer: "" Position: 1093
Though, if on top of the first query in the table view I was to apply a LABKEY filter type: "Is Not Blank", then I get exactly what I need (the same result as the first query, but without the last row!) and the info message about the filter is: "Filter: (Sample IS NOT NULL)", but that does not seem to mean in SQL "Sample IS NOT NULL", because that still results in an empty query.
I was about to think that I got my answer: I exported the query and the filter into JavaScript and got the following filter applied to the selectRows() Labkey call: LABKEY.Filter.create('Sample', '', LABKEY.Filter.Types.NOT_MISSING)
which is not what I used first (was missing the 2nd argument ''), but if I was to apply the same filter to my store, it still does not work.
Feel free, if you wish, to log into the test server (use your hutch credentials), go to "Test Flow" project and browse the files and play with the query browser:
http://dhcp157184.fhcrc.org:8080/labkey
Thanks.
-Leo |
|
Matthew Bellew responded: |
2012-05-15 12:58 |
My mistake, I thought you were querying the keyword names as opposed to the values. FCSFiles.Keyword.Sample could be null or empty (if it's empty, we should fix the code to turn those into nulls).
Note that in this query
SELECT DISTINCT FCSFiles.Keyword."Sample" AS Sample
FROM FCSFiles
WHERE Sample != ''
ORDER BY Sample
The "Sample" in the in WHERE clause is different than the "Sample" in the SELECT or ORDER BY. The "Sample" in the WHERE clause is a synonym for FCSFiles.Sample which is different than FCSFiles.Keyword.Sample. The "Sample" in the ORDER BY matches first against the columns in the SELECT list, per standard SQL rules. If it doesn't match anything it will fall-back to trying the usual matching rules and look for "FCSFiles.Sample".
Matt |
|
Leo Dashevskiy responded: |
2012-05-15 13:29 |
Ok Matt, yeah, thanks, I forgot my SQL basics a little bit and was mislead by my own post, I guess.
WHERE FCSFiles.Keyword."Sample" != ''
works just fine. |
|
Leo Dashevskiy responded: |
2012-05-21 16:44 |
Ok, so I am now wondering: is it possible in LABKEY.ext.Store to have the null/empty values replaced by something more meaningful, like "null" or "N/A" ?
Either on the server side (via a config or an SQL hack) or on the client side manipulating the store itself?
Because the way it is right now, if they're empty, then there is no way to select these rows via (Lov)ComboBox!
Thanks. |
|
Ben Bimber responded: |
2012-05-21 18:38 |
hi leo,
rather than throw a placeholder in the value ( is probably what you'd want), the way you generall accomplish something like this is using custom Ext render tpl:
http://docs.sencha.com/ext-js/3-4/#!/api/Ext.form.ComboBox-cfg-tpl
however, if you use LABKEY.ext.ComboBox there's a build-in way to handle this. On your store, set nullRecord to something like:
config.nullRecord = {
displayColumn: 'myDisplayColumn,
nullCaption: "[none]"
};
this will cause the store to add a placeholder record with a null value and '[none]' as the display value. however, with a LOVCombo, that may not be what you're looking for (multi-select being different than a traditional combo).
next you'd want to explore the tpl of your combo. this is what renders the value. something like:
tpl: new Ext.XTemplate(
'<tpl for=".">' +
'<div class="x-combo-list-item">{[values["' + l.keyColumn + '"]!==null ? values["' + l.displayColumn + '"] : "'+ (Ext.isDefined(this.lookupNullCaption) ? this.lookupNullCaption : '[none]') +'"]}' +
'</tpl>'+
' </div></tpl>'
)
should do it. that's one was written for the more general case and could be simplified if you have a fixed name for the display/value columns or the null caption. this tpl could also be modified to show more than 1 column, so you could show both the display column and the raw value. |
|
Leo Dashevskiy responded: |
2012-05-22 10:47 |
Ok, Ben, I will investigate your suggestion.
So there is such thing as LABKEY.ext.ComboBox?
I have not seen any mentioning of it in the documentation, is there any? |
|
Ben Bimber responded: |
2012-05-22 10:50 |
it's not technically part of the public API; however, it actually overrides the xtype 'combo', so if you use:
{
xtype: 'combo',
....other config...
}
it will create one. you can see the code in FormPanel.js, which is part of the API.
The majority of the options I described above are standard Ext, and your best bet if the Ext doc for those. If you need multi-select (ie. LOVCombo), then using the LABKEY.ext.Combo really wouldnt apply here. |
|
Leo Dashevskiy responded: |
2012-05-23 12:04 |
Ben (or anyone else),
while we touched on this subject,
could you, please, explain to me the ideas of a template (example above for instance) and a plug-in (comboBox in FormPanel.js, I believe) in Ext JS, specifically what powerful customization do they allow for ?
Thanks. |
|
Ben Bimber responded: |
2012-05-23 16:53 |
Hi Leo,
These two questions are really ext-specific and I would encourage you to check out Ext's documentation and forums. The template I describe is a config option available with Ext comboboxes which allows you to define a custom template (ie. the code behind how that HTML is generated) for each item in the drop-down menu. templates are quite useful, but you need to be careful when overriding Ext's default templates. If you want to learn about them, I encourage you to read the following:
http://docs.sencha.com/ext-js/3-4/#!/api/Ext.Template
http://docs.sencha.com/ext-js/3-4/#!/api/Ext.DataView
The latter is probably the most appropriate place to learn to use them.
To learn more about plugins, I also encourage you to check out Ext's doc. In this instance, we created a plugin to customize the Ext combobox. This particular plugin is predominantly used to auto-size the width of the pick-list. While plugins are sometimes the appropriate mechanism, it's far more common that subclassing an Ext component is going to get you what you need. |
|
Leo Dashevskiy responded: |
2012-06-21 15:58 |
Ben, in your post on the 21st of May above seems like there is a mismatch in the markup:
tpl: ... <tpl><div> ... </tpl></div></tpl>
isn't there?
Could you, please, provide a fixed one, if there is or assert that it is written correctly and should be used as-is?
Also, what exactly in that expression is the "l." is it "this" if I put this under the tpl config of the combo?
And by the way nullRecord, mentioned in the same post, is an undocumented config option for the Labkey.ext.Store?
Thanks. |
|
Ben Bimber responded: |
2012-06-21 18:21 |
Hi Leo,
Yes, the original post is probably a typo. I extracted that code from a more complex template, and probably forgot to remove one of the </tpl> tags.
My earlier post was assuming you were using Ext3. In Ext3, LabKey overrode the default Ext combo to add a few behaviors. Primarily, this involved the ability of the list to automatically resize itself based on the width of the items, and there are options surrounding blank records. These include the ability to set a custom caption (ie. nullCaption), and the ability to have the combo automatically add a blank record to the store if no record was present. These are not really part of our public API and probably not included with those docs.
In Ext4, I think we will do things a little differently. In Ext4, more of these options are built into the combo, or available with pretty good UX components. Therefore there is less need to create a labkey-specific component. For your work, understanding how Ext Xtemplates work can be really helpful, and I'd encourage you to take a look at the example above and be sure you understand what it's trying to do. Ext's docs are pretty good for them as well. Many extensions of fields can be handled cleanest by customizing the field tpl. |
|
Leo Dashevskiy responded: |
2012-06-21 18:34 |
Yeah, so I was trying to understand the example above and was wondering if you could, please, explain:
1) what is "l.",lower case L, seen together with "keyColumn" and "displayColumn"? and what are those two columns, configs of the store? I do not see them in the Ext's store, are they custom ones of the Labkey.ext.store?
2) what should be the correct expression for that example above? which div/tpl/anything else should be removed to make it correct?
am I correct to interpret that it should be this:
tpl:'<tpl for=".">' +
'<div class="x-combo-list-item">{[values["' +
this.keyColumn + '"]!==null ? values["' + this.displayColumn + '"] : "' +
(Ext.isDefined(this.lookupNullCaption) ? this.lookupNullCaption : '[other]') +'"]}' +
'</div>' +
'</tpl>',
if that's the case, then I almost understand, what it's doing, except for the bit in question 1) |
|
Ben Bimber responded: |
2012-06-21 20:21 |
Hi Leo,
I think you get it. As I said earlier, this template was extracted from more complex code. For the reasons you've been finding, perhaps it isnt the best example. The 'l' makes sense in the context where this template came from. Your replacement with 'this' is probably going to be correct.
The best way to approach this sort of problem is usually to see what template the Ext component you are extending uses by default. These templates frequently change between Ext revisions. Assuming you are using Ext3, you can find it here:
http://docs.sencha.com/ext-js/3-4/source/Combo.html#Ext-form-ComboBox-cfg-tpl
That is the template on which my example is based. You'll see that the example takes the Ext code and augments with additional options. To make an Ext4 version, you would use a similar approach.
To your first question: displayColumn and lookupColumn are attributes on LabKey Field metadata objects. See our docs on LABKEY.Query.FieldMetaData for more. In your example, you probably want to be using the Combo's displayField / valueField properties. That's what Ext's default template does. |
|
Leo Dashevskiy responded: |
2012-06-21 20:23 |
Ok, I think, I got it, thanks Ben! |
|
|
|