Query question for graph using in/out/expand in where clause

61 views
Skip to first unread message

Ankur Pandey

unread,
Aug 30, 2015, 1:39:12 PM8/30/15
to OrientDB
I have created a database with two vertices in it. Stores, Products. Stores have a GeoSpatial Index. There is an Edge 'hasInventory' from store to products. Now If I have to find a SKU in near by stores how should I proceed. 
I tried like: 
SELECT expand(in('hasInventory').count(*)) FROM Products WHERE sku = '8907103076739'

-- Second Query
Select in.@rid,in.name,out.@rid,out.name,out.lat,out.lon,quantity from hasInventory where in.sku = '8907103076739'

-- Third Query
Select in.@rid,in.name,out.@rid,out.name,out.lat,out.lon,quantity from hasInventory where in.sku = '8907103076739' and [out.lat,out.lon,$spatial] NEAR [19.195524,72.834810,{"maxDistance" : 10}]




I want to understand in such cases when I have to traverse to other vertices in query and apply "where" clause on the result of other vertices how it can be done. 

alessand...@gmail.com

unread,
Aug 31, 2015, 4:45:41 AM8/31/15
to OrientDB
Hi,
can you send me your schema ?

Regards,
Alessandro

Ankur Pandey

unread,
Sep 2, 2015, 5:36:54 AM9/2/15
to OrientDB
I am not sure what all information is needed to you. However, I am providing relevant Information here. Hope that is sufficient for you:

I have two vertices Stores and Products both extends V.

Stores Looks like as follows:

Products Look like: 

Now there is another class hasInventory that Extends E: , from Store to Products. (i.e. A store hasInventory of a product). This Edge have a property "QTY"
Idea is to Find product in all near by stores having qty > 0. in Stores lat, lon have a spatial index. 

I was able to make it work somehow. But the query is taking too long, 16 sec

Select @rid,name,address,lat,lon,$distance from Stores where @rid IN (Select out.@rid from hasInventory where in.sku = '8907103076739' and quantity > 0) and [lat,lon,$spatial] NEAR [19.195524,72.834810,{"maxDistance" : 2000}]



alessand...@gmail.com

unread,
Sep 2, 2015, 11:54:54 AM9/2/15
to OrientDB
Hi,
try to create this function with the parameters lat,lon,maxDistance,sku

var g=orient.getGraph();
var b=g.command("sql","select from Stores where [lat,lon,$spatial] NEAR ["+lat+","+lon+",{'maxDistance':"+maxDistance+"}]");
var vector = [] ;
for(i=0;i<b.length;i++){
  var id=b[i].getId().toString();
  var c=g.command("sql","select expand(out('hasInventory')) from "+id);
    for(j=0;j<c.length;j++){
      if(c[j].getProperty("sku")==sku){
          var d=g.command("sql","select expand(outE('hasInventory')) from "+id);
          for(z=0;z<d.length;z++){
              if(d[i].getProperty("quantity")>0){
                vector.push(b[i]);
                break;
              }
            }
          break;
      }
    }
}
return vector;

and from broswe try this
SELECT expand(myFunction(19.195524,72.834810,2000,"8907103076739")) from  (SELECT myFunction(19.195524,72.834810,2000,"8907103076739"))

let me know if the query goes faster.

Best regards,
Alessandro
Reply all
Reply to author
Forward
0 new messages