simple udf implementation in 'select' clause not working but working in 'where' clause

[Fayaz Aziz]

ql io script :
var script = 'u = require("./myUDF.js");\
create table myoffers on select get from "https://api.sqoot.com/v1/offers?affiliate_token={^token}&location={mylocation}&order={^orders}&page={^page}&per_page={^psize}"\
using headers accept = "/" \
using defaults token = "xxxxx", orders = "score", page = "1", psize = "3" \
resultset "offers" ' \ 

select offer.title as mytitle, u.getGoogleID(10) as loc from myoffers where mylocation = 'sanfransico'

my udf function :

var util = require('util');

exports.getGoogleID = function (id) {
return this.next(null, id+10);

}

WHAT I AM MISSING HERE ?... my result is
{ title: 'The best of Heering at 50%.', loc: undefined } // the loc value should be 20
SOMEHOW I AM UNABLE TO ENTER IN MY UDF FUNCTION...... what is the possible reason for that ?

Does this udf functionality implementing in 'select' clause as it was working in my where clause ??

[shimonchayim]

Right now udf only works in where only. Functionally if you plan to use udf in select for data transformation you can also do that through the udf in where. That is the main reason we haven't given udf in select higher priority.

[Fayaz Aziz]

hmm but my udf function is depend on the parameter that is provided in select clause like

select name, id , myfunction(id) from table ABC
how can i use this in where clause, herei need to mapped the id from the current table and in my udf function i need to call another ql.io script which takes the id as param.. so by using it in select clause it will map one by one.

AND

Can you please also tell me the main difference b/w udfs and monkey patches and some use cases of both (when to use what).. i need to perform some execution of ql.io script in my monkey patch file.. it is right way to do ?? Knowing that the udf is currently not enough mature.

[shimonchayim]

Pardon for answering your question in the reverse order Monkey patches are part of table definition (more about them here: http://ql.io/docs/monkey-patch)

On your first question:

Let us consider sample udf:

exports.mergeResult = function() {
var id = this.row.id;
var name = this.row.name;
var result = do_something(id, name);
this.row = result;
return this.next(null, this.row);
}

You can modify the row returned to your desire. So Udf in "where" does filtering as well as transformation.

I wouldn't advise you to call ql.io scripts from udf or monkeypatch. These scripting hooks are for executing light logic and not for IO.

[Fayaz Aziz]

hmmm okay but the problem is that i need to perform io calls.. here is the flow of my work which i was trying to perform for last 2 weeks and haven't succeeded yet.

1) call sqoot api to retrieve 10 records by passing parameter : 1)location
2) for each records gets marchant_name , latitude and longitude and call google api to get the exact locations by passing params 1) merchant name 2) latitude 3) longitude
3) from the result of 2) modify the result and construct an object i.e

loc : [0:'5464654',1:'46546546'] // where 0 means latitude value and 1 means longitude value

4) select few results from 1) and replace location from that with the one we construct in 3)
5) return the dataset to any controller / function which will processed further..

here is what i have tried till now :

1) ql.io query which is joining both the tables having multiple join predicates as column - which is failed i.e for now when using join only the next condition after where clause consider as joining clause, all remaining will consider as conditional clause

2) i tried this for udf... the preferred ways was to call a udf function in select clause so that we have correct values in parameter and than we can call udf function which will do IO operation and return a location result which will be replace in the select clause by any alias (udf for select clause will be release in v0.9)

3)  i then tried to use monkey patch but since we are doing IO operations which is async calls so sqoot api will not wait for the result of google api (which is running in patch) so both run simultaneously

both 2 and 3 are not preferred to use for io calls rather its meant to use for lightweight works like filtering.. data altering..

So now since its the earlier releases of q.io keeping in mind the current capabilities of qlio how can i accomplish my task. ???

[shimonchayim]

Nothing below sounds to me that needs udf or Monkey Patches for IO.

Here is an example (from ql.io site) where we are doing 1) & 2).

3) & 4) Can be achieved through udfs.

Thats all I can say at this point. This is really not a very complex example IMHO.

[shimonchayim]

Sorry forgot to copy the example:

select e.ItemID as id, e.Title as title, e.ViewItemURLForNaturalSearch as url, g.geometry.location as latlng

  from details  as e, google.geocode as g

  where e.itemId in (select itemId from finditems where keywords = "iPad")

        and g.address = e.Location