ActiveOrient: Joins and Links
Tags: ruby, activeorient, orientdb, sql
Published: 2019-07-29 18:54:52

In the [Introduction](https://dev.to/topofocus/active-orient-introduction-overview-4knj) we created a self-referencing join.
Lets compare with `ActiveRecord`

```ruby
# (ActiveRecord) in file /model/person.rb
class Person
  has_many :children, class_name: 'Person', foreign_key: 'father_id'
  belongs_to :father, :class_name => 'Person'
end
# then 
 Person.find( name: 'Hugo').first.father 
 Person.find( name: 'Hugo').first.children
```
We omited the tricky part of setting up a database-definition, defining proper indices and perform a migration through rake-tasks and note, that data have to be inserted, too.
## Insert Data
With ActiveOrient, we just create the database-table, insert our data and are done.

```ruby
> V.create_class :person
> hugo = Person.create name: 'Hugo', 
                       father: Person.create( name: "Reimund" ),
                       children: ["Eva", "Ulli", "Uwe", "George"].map{|c| Person.create( name: c) }
=> "<Person[150:0]: children: [#146:0, 147:0, #148:0, #149:0], father : <Person[145:0]: name: Reimund>, name: Hugo>" 

> hugo.father.name   => "Reimund"
> hugo.children.name => ["Eva", "Ulli", "Uwe", "George"]
```
Note that `children` are represented by their database-id's only, this is equal to the foreign_key used by `ActiveRecord`. 

The schemaless approach reveals a quick solution and exposes the basic functionality of a comparable RDMS-Database. But there are limits. The possibility to work schemaless does not mean, that ad-hoc designs are favorable. It is, for instance, not possible, to query linked records, unless a proper schema is defined. We are still in the database-world, the world of structured data.

## Define Properties

It is a good practice, to put an index on any property, which is subject of a query. We want to create a `notunique`-Index on the `:name` property. `:children` are just a `list of links`, containing `Person` objects and
the `:father` is a `link`to a `Person`-record.  But first, lets delete anything. 
```ruby
> Person.delete all: true
> Person.create_property  :name, type: :string, index: :notunique
> Person.create_property  :children, type: :link_list, :linked_class: Person
> Person.create_property  :father, type: :link, linked_class: Person
# check
> Person.print_properties
  Detected Properties for class person
	person.children	-> LINKLIST	-> person
	person.father	-> LINK	-> person
	person.name	    -> STRING
```

To insert another record, we must avoid to create a duplicate of the `Reimund`-Person. 
```ruby
pete = Person.create name: 'Pete', 
                     father: Person.upsert( where: {name: "Reimund"} ),
                     children: ["Eva", "Mathew" ].map{|c| Person.create( name: c) }
```
Instead of creating a new record for the father, we search the database and use the existing entry. `Upsert` inserts a record if no entry is found. That guarantees that `:father` is always a valid link.

##Query the Database

Orientdb Database classes and -records are queried with a SQL dialect. `ActiveOrient` provides a method `query` to build such queries similar to `ActiveRecord`.

```ruby
> Person.query.where( "father is NOT NULL" ).execute.name
 #INFO->select from person where  father is NOT NULL 
 => ["Peter", "Hugo"] 
> Person.query.where( 'father.name' => 'Reimund' ).projection('name').execute
 #INFO->select name from person where father.name = 'Reimund' 
 => [{:name=>"Pete"}, {:name=>"Anton"}] 
```
The first query returns two `Person`-records. We display only the property `:name`. The second query specifies a projection and returns a list of hashes. 

#### Model-helper
Complex queries can be stored in the `model-file` of the class. Queries are class-methods. Thus, in `/model/person.rb`
```ruby
 class Person
  def self.grandchilds of:
    query.where('father.name' => of)
         .projection( :children )
   end
 end
```
The class method returns a `OrientQuery`. It can further modified, inspected by calling `to_s` and finally `executed`.
```ruby
> Person.grandchilds( of: 'Reimund' ).execute.to_human
# INFO->select children from person where father.name = 'Reimund' 
["{ children: #29:5#30:5#31:5#32:4 }", "{ children: #26:5#27:5 }"]
# or
Person.grandchilds( of: 'Reimund' ).execute{|y| y[:children]}.name
 => ["Eva", "Ulli", "Uwe", "George", "Eva", "Mathew"] 
```
For pre-prosessing `OrientQuery.execute` exposes any returned record to the optional block. Obviously, we are interested in `:children`. The query returns links to `Person-Objects` which are expanded by `ActiveOrient`. Thus `.name` simply addresses the `name`-attribute of the returned records.


## Conclusion
The database has basic support for joined tables. Anybody familiar with RDMS-(SQL)-Databases should be comfortable with unidirectional links and joins and their ActiveRecord-like querying. We can develop a draft sketch (schemaless) without any configuration. If necessary, constrains, properties, indices and validations are added. The power of [nested projections]( http://orientdb.com/docs/3.0.x/sql/SQL-Projections.html) goes far beyond anything in the RDMS-world. 

`ActiveOrient` combines the power and beauty of ruby with SQL for the 21st Century.

Unidirectional Links and Joins are nice and familiar. In the next part, we extend this approach to bidirectional Links and take a walk into the world of graphs.