How to view linked data in OrientDB

Question

I have two tables, described as below.

Table 1: countries

c_id, int
c_name, varchar(20) (PK)

Sample records in this table are.

c_id | c_name
1 | USA
2 | UK
3 | PAK

Table 2: immigrants

i_id, int
i_name, varchar(20)
i_country, int (FK)

Sample records in this table are.

i_id | i_name | i_country
1 | John | 1
2 | Graham | 2
3 | Ali | 3

Question 1:

I want to create two classes (tables) in OrientDB but I need to know what should be the data type of the FK field and what to insert in it. I mean what should I write in query to insert the id of the PK table. Does it need to be @rid? How?

QUESTION 2:

What is the OrientDB SQL for producing the following output.

i_id | i_name | i_country | c_id | c_name
1 | John | 1 | 1 | USA
2 | Graham | 2 | UK
3 | Ali 3 | PAK 

Answer 1

with OrientDB you can avoid the creation of FK fields and join operations by using direct Edge links between records. For example:

create class Country extends V;
create class Immigrant extends V;
create class comesFrom extends E;

create property Country.c_id integer;
create property Country.c_name String;
create property Immigrant.i_id integer;
create property Immigrant.i_name String;

insert into Country(c_id, c_name) values (1, USA);
insert into Country(c_id, c_name) values (2, UK);
insert into Country(c_id, c_name) values (3, PAK);

insert into Immigrant(i_id, i_name) values (1, John);
insert into Immigrant(i_id, i_name) values (2, Graham);
insert into Immigrant(i_id, i_name) values (3, Ali);

Now you can connect directly the record you want (I used an Edge called 'comesFrom' and subqueries to link the id fields but you could also directly use the @RID field)

create edge comesFrom from (select from Immigrant where i_id = 1) to (select from Country where c_id = 1);
create edge comesFrom from (select from Immigrant where i_id = 2) to (select from Country where c_id = 2);
create edge comesFrom from (select from Immigrant where i_id = 3) to (select from Country where c_id = 3);

Finally you can query the fields you want without join operations:

select i_id, i_name, out('comesFrom').c_id as c_id, out('comesFrom').c_name as c_name 
from Immigrant unwind c_id, c_name

----+------+----+------+----+------
#   |@CLASS|i_id|i_name|c_id|c_name
----+------+----+------+----+------
0   |null  |1   |John  |1   |USA
1   |null  |2   |Graham|2   |UK
2   |null  |3   |Ali   |3   |PAK
----+------+----+------+----+------

In Orient Studio you should obtain a graph like this: