How to make a query using related data from three classes

27 views
Skip to first unread message

TomekK

unread,
Jun 3, 2019, 4:11:04 PM6/3/19
to orient-...@googlegroups.com
How can I get the same effect in OrientDB query as with JOIN SQL in relational databases?

I have classes in OrientDB
CREATE Class Continent;
CREATE Class Country;
CREATE Class City;

//Hong Kong
INSERT INTO  City set name = 'Tsuen Wan';
INSERT INTO  City set name = 'Sha Tin';
INSERT INTO  City set name = 'Tuen Mun New Town';

//Denmark
INSERT INTO  City set name = 'Copenhagen';0
INSERT INTO  City set name = 'Aarhus';
INSERT INTO  City set name = 'Odense';

//France
INSERT INTO  City set name = 'Paris';
INSERT INTO  City set name = 'Marseille';
INSERT INTO  City set name = 'Lyon';

INSERT INTO  Country set name = 'Hong Kong', cities=(SELECT FROM City WHERE name in ['Tsuen Wan', 'Sha Tin', 'Tuen Mun New Town']);
INSERT INTO  Country set name = 'Denmark', cities=(SELECT FROM City WHERE name in ['Copenhagen', 'Aarhus', 'Odense']);
INSERT INTO  Country set name = 'France', cities=(SELECT FROM City WHERE name in ['Paris', 'Marseille', 'Lyon']);

INSERT INTO  Continent set name='Asia', countries=(SELECT FROM Country WHERE name = 'Hong Kong');
INSERT INTO  Continent set name='Europe', countries=(SELECT FROM Country WHERE name in ['Denmark', 'France']);

which is the equivalent of the SQL tables
CREATE TABLE Continent (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);


CREATE TABLE Country (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    continent_id INT,
    foreign key (continent_id) references Continent(id)
);

CREATE TABLE City (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    country_id INT,
    foreign key (country_id) references Country(id)
);

How to make a query using data from these three classes, which will return
CITY  COUNTRY  CONTINENT  
Copenhagen Denmark Europe
Aarhus Denmark Europe
Odense Denmark Europe
Paris France Europe
Marseille France Europe
Lyon France Europe
Tsuen Wan Hong Kong Asia
Sha Tin Hong Kong Asia
Tuen Mun New Town Hong Kong Asia

Something like in SQL
SELECT city.name AS City, country.name AS Country, continent.name AS Continent
FROM City city, Country country, Continent continent
WHERE
city.country_id=country.id and continent.id=country.continent_id;


Reply all
Reply to author
Forward
0 new messages