Skip to content

OrientQuery

Jump to bottom
topofocus edited this page Dec 10, 2020 · 31 revisions

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

Changes 2.2 -> 3.0 Changelog

Overview

Data Management

Query the Database

Public API

Misc

Clone this wiki locally