Joining Tables

OrientDB represents relationships between database classes with LINKS instead of (RDMS) JOINs. For this reason, the classic JOIN syntax is not supported in SQL-Queries. OrientDB uses the "dot (.) notation" to navigate LINKS.

This is supported by ActiveOrient in a convenient way.

Suppose, you want to store general information about a Company in a Class/Table called »the_base«. Then you want to keep records of available trading-instruments in a »asset« Class/Table, which should have children called »stock«, »option« or »bond«. Thus

V.create_class :the_base, :asset
Asset.create_class :stock, :option, :bond 

The Asset-Class needs a property »base« which should carry an index. Its inherited to the child-classes.

Asset.create:property :base, type: :link, index: :uniq

Anything is initialized in a single statement

Stock.create symbol: 'AAPL', base: TheBase.upsert( where: {name: 'Apple'} )
Bond.create maturity: Date.new(2025,5,31), cupon: 2.5 , base:  TheBase.upsert( where: {name: 'IBM'} )

Associated records are fetched the usual way

google_assets =  Asset.where "base.name='Google'"
=> [#<Stock:0x000000035b8698 @metadata={"type"=>"d", "class"=>"stock", "version"=>3, "fieldTypes"=>"price=c,currency=x,base=x", "cluster"=>59, "record"=>336}, @attributes={"name"=>"GOOGLE INC-CL A", "ib_con_id"=>30351181, "price"=>555.19, "base"=>"#45:22"}>, 
#<Option:0x000000035a5598 @metadata={"type"=>"d", "class"=>"option", "version"=>3, "fieldTypes"=>"price=c,expire=t,currency=x,base=x", "cluster"=>65, "record"=>756}, @attributes={"name"=>"GOOGLE INC-CL A", "ib_con_id"=>nil, "price"=>5.2, "expire"=>"2014-08-29 00:00:00", "symbol"=>"GOOGL", "exchange"=>"SMART", "currency"=>"#42:0", "basiswert"=>"#45:22"} ] 
google_assets.asset.name 
=> ["Google", "Google"]
google_assets.name
=> ["GOOGLE INC-CL A", "GOOGLE INC-CL A"]

Arrays, embedded Lists and Linkmaps

Linkmaps are the OrientDB equivalent to joined tables Formally, they are ordinary arrays.

They can be created schema-less.

V.create_class :industry, :property
property_record=  Property.create  con_id: 12346, property: []
industries =  ['Construction','HealthCare','Bevarage']
property_record.property.append *industries.map{ |i| Industry.new( label: i ) }
## append expects a comma separated list of items, thus the » * «

Property.last
 => #<Property:0x00000001d1a938 @metadata={"type"=>"d", (...)},  
    @attributes={"con_id"=>12346, "property"=>["#34:0", "#35:0", "#36:0"],
    "created_at"=>"2017-02-01T06:28:17.332 01:00", "updated_at"=>"2017-02-01T06:28:17.344 01:00"}> 

Note: In schema-less mode, the elements of the joined table are first created as ordinary document, which is then linked to the property. However, if the property industries is predefined as :list, the records are stored as embeddedlist.

Stored data in this array is accessible via

p =  Property.last
p.property.label
 => ["Construction", "HealthCare", "Bevarage"]
p.property[2].label
 => "Bevarage" 
p.property.label[2]
  => "Bevarage" 
p.property[2].label[2]
 => "v" 

p.property.remove_at 2
p.property.label
  => ["Construction", "HealthCare"] 

p.property[2] = Industry.where(label:'Bevarage').first
p.property.label
 => ["Construction", "HealthCare", "Bevarage"] 

The Elements of the Array can be treated like common ruby Arrays. Manipulations are performed in ruby-space. Simply call

p.update; p.reload!

to transfer the changes into the database. This approach can be extended to linked records as well

p.property[2].label = 'zu'
p.property[2].update
p.property.label
 => ["Construction", "HealthCare", "zu"]
Industry.last.label
 => "zu"