Select Query in a select clause ??

[fayaza]

How to add a select query in a select clauses... ? like the given below in ql.io

return select id as ID,name as NAME,url as ADDRESS, (select another_name from tableB where id = a.id) as NewID from tableA as a ; ??? just like we done usually in sql

[shimonchayim]

first of all qlio is a DSL which has similarities with SQL but is not SQL.

Some of what you are looking for can be achieved through udfs.

We will be documenting udf usage shortly on ql.io site. In the meanwhile here is an example

ql.io/modules/engine/test/where-join-udf-test.js

[fayaza]

yes but actually i dont want to use any kind of other script (define udf on ql io script and implement in node js) since we are currently analysis the power of ql io script... so is there any way to do... in the script only... here is my use case:

1) take offers from sqoot api where location is sansfransico 2) get the merchant_name . latitude and longitude from the 1) result set 3) call geolocation api by providing the 3 variables showing in 2) 4) get the actual latitude and longitude and replace it in the result of 1)... 5) return the result by defining column name as aliases

Is there any possibilities that i can achieve the above use case byonly using ql io script...

[shimonchayim]

Everything else should be fine but 4) may not be possible without udf at this time. Something we should think about supporting.

[fayaza]

I have applied joins but its not getting the result but for now UDF can be use i think if there is no other option. Ok so that mean in select clause i need to define the one udf that take three arg define in 2) and in that udf i need to applied a ql io script to get the actual latitude longitude and google id which with i will return back on the select clause... like this (correct me if i am wrong)

select id,name,url ,myudf(merchant-name,lat.long) , from ABC;

export.myudf = function () { return 3 values in json form........ so that it will replace with the function so will get 5 columns right ????? });

[shimonchayim]

conceptual code (variable names, etc madeup based on your scenario above, it just to show you an example, will not run as is) Script could be something like this: udfs = require("your_file.js");

result1 = select merchante_name, latitude, longitude from sqoot where location = "sanfrancisco"; result2 = select * from geolocation where details in '{result1}'; combined = { "sqoot" : "{result1}", "geo", "{result2}"}; return select * from combined where udf.mergeResult();

conceptual contents of your js file containing the udf

exports.mergeResult = function() { var sqoot = this.row.sqoot; var geo = this.row.geo; var result = do_whatever_you_want_to_merge_the_results(sqoot, geo); this.row = result; return this.next(null, this.row); }

[shimonchayim]

Note, we are in the process of documenting all this on http://ql.io

[fayaza]

ok its great...Thanks

[fayaza]

The best way for this work is to apply a join rather then using UDF (which is more likely to be use for generic functions)

so here i need a simple join help... join columns are myoffer.offer.merchant-name = geoloc.name

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 defaults token = '3fvbuh', orders = 'score', page = '1', psize = '2' resultset 'hash.offers.offer'; create table geoloc on select get from 'https://maps.googleapis.com/maps/api/place/search/{^format}?name={merchant}&location={lat},{lng}&radius={^radius}&sensor=true&key={^key}' using defaults format = 'json',radius = 1000, key='AIzaSyBcmAHtSvmRkdcv8F6HAafUmtEwrJ6c_rA' resultset 'results';

I THINK I AM GETTING SYNTAX ERROR IN DEFINING COLUMNS NAMES HAVING '-' IN IT

data = select * from myoffers as o , geoloc as g where g.name = '{o.offer.merchant-name}' and o.mylocation = 'sanfrancisco' and g.merchant = 'Maharani Resturant' and g.lat = '37.796781' and g.lng = '-122.42037';

[fayaza]

how to handle special character in select clause i have my column like 'merchant-name' , location.$t how to use this in select and where clauses..

I prev using this '{merchnat-name}' which throwing an error that columnName is undefined.

[shimonchayim]

Join may also work for your case. Special characters are not supported at this time. These exception cases are currently handled through monkey patch or udf. We can try to support this in the future. Also, I think discussion groups is the right forum to discuss this as more users are able to contribute. So I request you to ask further questions there. You are welcome to open issues on github and we will prioritize them.

[fayaza]

OK....i will continue this threat on the forum from now on..