Compare two columns

[kausti]

I posted this on stackoverflow here https://stackoverflow.com/questions/46703751/taffydb-compare-two-columns

Looks like the taffydb thread has been long inactive. Makes me wonder if I should be using it in production! Anyhow..posting the same question here. Should be simple enough for a binary yes/no answer at least. I really hope this thing kicks on. It's been very useful so far.

[{"variable":"ZENSARTECH","Date":"2017-10-11","value":740.05,"bbup":787.4447},
{"variable":"ZYDUSWELL","Date":"2017-10-11","value":885.7,"bbup":905.0995},
{"variable":"ECLERX","Date":"2017-10-11","value":1234.7,"bbup":1265.7899}]

const db = taffy(data);
const pdate = db().max("Date")
const fdb = db({Date: pdate})

Now what I want to do is get all the rows where value is greater than bbup column. So I want to compare two columns of the table rather than a column against a fixed value, the kind of examples shown in docs

fdata = fdb.filter({value:{gt:bbup}}) I tried the above but doesn't work. Is there a way to do this please? Seems like a very trivial SQL query.

Thanks!

[typicaljoe]

Thanks Kausti!

It is pretty trivial in a SQL context. But TaffyDB being fully JavaScript doesn't have a reference to the record in that context. In any case there is a pretty simple way around it using a function for your filter:

fdata = fdb(function () { return (this.value > this.bbup) ? true : false; });

This of the simple filter objects as an easy short hand way to get values from your app context into TaffyDB for searching. They are powerful, but virtually anything you can code you can do with the more powerful function (imagine something like filtering where the value column is greater than the bbup column and the value column is over 900 or the date is before a certain date).

Hope that helps!

[kausti]

typicaljoe, really appreciate your prompt response. Thank you very much. Cheers!

[typicaljoe]

By the way, the issue with providing a short hand access to the record within the filter object is the issue of moving from an object literal in JavaScript to some kind of quoted expression letting you access the hidden record object within Taffy.

For example we could support something like : {columnA:{gt:"this.columnB"}} where Taffy would look at this.columnB and figure out how to access the record itself. But consider something like: {columnA:{like:"this.columnB"}} - now Taffy has to decide if it looks for the text string within columnA as passed in or looks up the value of columnB and searches for that? And since values passed into a filter object often come form the application user such as through a free from search box it would be possible for someone to type "this.is my name" and get completely unexpected results. And heaven helped us if you actually stored a string such as "this.columnC" within the value of columnB.

I thought hard on this but ended up punting the feature to focus on keeping the API simple and making it "just work" in most use cases. Perhaps we should explore something like {columnA:{gt:{record:"columnB"}}} but it starts to get unreadable.

[kausti]

Very nicely explained! For someone experienced in python and R, javascript stumps me at times. Early days still. This is very useful for anyone facing the same challenge. I have posted link to this thread on stackoverflow and closed the question.

May be we can include this solution in the original docs?

Thanks.