OrientQuery

OrientSupport::OrientQueryis a simple but effective tool to create valid queries, not only for ActiveOrient. Its primary intention is to provide a mechanism to include ruby-objects into custom queries and delegate the representation to OrientQuery.

In particular, Model Classes are converted to database-classnames. ActiveOrient::Model-Objects are represented through their rrid. Strings are correctly escaped.

The standard case: provide query items as arguments and compile using 'to_s' and send to the database via execute

>  Q= OrientSupport::OrientQuery
>  Q.new( from: TestQuery, where:{ a: 2 , c: 'ufz' }).to_s
# is identical to
>  Q.new.from( TestQuery)
        .where( a:2, c: 'ufz')
        .to_s
 => "select from test_query where a = 2 and c = 'ufz' " 

note: We provide the ActiveOrient-Class to from. Its translated to the database_class.

NULL-Values

NULL-Values are supported via property: nil (set a value) and property: [nil] (query NULL), further information: Model-CRUD

Classes and Object-Instances can be used directy

> Q.new.from( TestQuery.first)
 => "select  from #33:0 "

Update

If kind(:update) is specified, an update statement is produced

> q =  Q.new( from: TheList, where: {name: 'Maria Hellweg' } ) 
   => "select from the_list where name = 'Maria Hellweg' "
> q.kind(:update).set( user_id: 345) 
 => "update the_list set user_id = 345 where name = 'Maria Hellweg'

Interactive mode: Start with a minimum and develop on the fly

> q =  Q.new
> q.from TestQuery           #=> "select  from test_query  "
> q.where  a: 2              #=> { add a condition }
> q.order  name: :asc        #=> { order the result set }                                       }
> q.projection  "eval( 'amount * 120 / 100 - discount' )"=> 'finalPrice'  # calculate something
> q.to_s    
  => "select eval( 'amount * 120 / 100 - discount' ) as finalPrice from test_query where a = 2 order by name asc"

note: The projection is placed between select and from

Incremental Development of Subqueries

Subqueries are a powerful but sometimes confusing matter.

OrientQueryaccepts itself as argument to :from

>  q =  Q.new from: TestQuery, where:{ a: 2 , c: 'ufz' }
>  Q.new( from: q , kind: 'traverse', projection: :day).to_s
 => "traverse day from ( select from test_query where a = 2 and c = 'ufz' ) "

note: The directive kind: 'traverse' changes the default select keyword

Further sub-queries can be introduced as »let-block«.

  q = Q.new .from( 'model_query')
      .let(  "$city = adress.city" )
      .where(  "$city.country.name = 'Italy' OR $city.country.name = 'France'" )
      .to_s
  # => select from model_query let $city = adress.city where $city.country.name = 'Italy' OR $city.country.name = 'France'
 # or
  q =  Q.new{ 'or' } .from( ModelQuery )
                     .let( city: 'address.city')  # or: "$city" => 'address.city
                     .where( '$city.country.name' => 'Italy' )
                     .where( '$city.country.name => 'France' )

or

  q =  Q.new
  q.let a: Q.new( from: '#5:0' ) 
  q.let b: Q.new( from: '#5:1' ) 
  q.let  '$c= UNIONALL($a,$b) '
  q.expand( '$c')
  q.to_s  # => select expand( $c ) let $a = ( select from #5:0 ), $b = ( select from #5:1 ), $c= UNIONALL($a,$b)

or

  last_12_open_interest_records = Q.new from: OpenInterest, 
					order: { fetch_date: :desc } , limit: 12
  bunch_of_contracts = Q.new from: last_12_open_interest_records, 
			      projection: 'expand( contracts )'
  distinct_contracts = Q.new from: bunch_of_contracts, 
			      projection: 'expand( distinct(@rid) )'

  distinct_contracts.to_s
   => "select expand( distinct(@rid) ) from ( select expand( contracts ) from ( select  from open_interest order by fetch_date desc limit 12 ) ) "

Nodes

Vertices are connected through Edges. We call such connected Vertices »Nodes«. OrientQuery supports queries following Edges. In terms of the query, a Node can be addressed with an inE(_edge_class_}.out or an outE({edge_class}.in expression in the projectionpart of the query.

This is exactly what OrientQuery.nodes supports.

OrientQuery.nodes ( :in -or- :out , via: {Edge-Class}, where: {a condition}, expand: {true -or- false} )

> start_node = Date.new(2018,4,25).to_tg    # select item in OrientDB-TimeGraph
> traverse_query = Q.new kind: 'traverse', from: start_node, while: '$depth <= 3'
> traverse_query.nodes :out, via: TG::GRID_OF, expand: false
>  => "traverse  outE('tg_grid_of').in  from #82:8927  while $depth <= 3 " 

> the_query = Q.new from: traverse_query, where: '$depth >=1
   => select  from  ( traverse  outE('tg_grid_of').in  from #82:8927  while $depth <= 3  )  where $depth >=1 

Expand enables the access of the contents of subqueries instead of their rid. Its commonly used when accessing nodes and therefor the default of nodes

> q= Q.new from: TestQuery
>  q.nodes
 => "select  expand (  outE().in  ) from test_query " 
> q.nodes where: {type: 7}
 => "select  expand (  outE().in[ type = 7 ]  ) from test_query " 
> q.nodes :in, where: {type: 7}
 => "select  expand (  inE().out[ type = 7 ]  ) from test_query  " 
> q.nodes :out, via: TEST_EDGE, where: {type: 7}
 => "select  expand (  outE('test_edge').in[ type = 7 ]  ) from test_query " 

note: expand : true (the default) overwrites other projections.

List of supported parameters

• projection, from, let, where, order, group_by, unwind, skip are primary query elements
• distinct, expand and nodes are included in `projection'
• any other parameter is added to misc
• targetis an alias to from and used by update
• update accepts setand remove