OrientDB SQL query to emulate a many to many join

Question

Given the following schema created using the OrientDB Document API:

    OClass team = getoDocDatabase().getMetadata().getSchema().createClass(TEAM);
    team.createProperty(NAME, OType.STRING);

    OClass driver = getoDocDatabase().getMetadata().getSchema().createClass(DRIVER);
    driver.createProperty(NAME, OType.STRING);

    OClass car = getoDocDatabase().getMetadata().getSchema().createClass(CAR);
    car.createProperty(NAME, OType.STRING);

    // Relationships
    team.createProperty(CARS_HERITAGE, OType.LINKSET, car);
    car.createProperty(BUILT_BY, OType.LINK, team);
    car.createProperty(DRIVEN_BY, OType.LINKSET, driver);
    driver.createProperty(DRIVER_OF, OType.LINKSET, car);

What's the sql query to fetch all the teams that Fernando Alonso has driven for?

In relational SQL would be as easy as

SELECT team.name FROM {the join} where driver.name = 'Fernando Alonso'

Answer 1

The answer from @Alessandro is correct, however I've found some weirdnesses in the way the sql is interpreted. Let me explain myself.

I've simplified the goal, let's try to find the query to fetch the teams which have had at least a car.

This first query works, is the one suggested by Alessandro. It returns a list of documents containing one property, that is the name of the team.

select distinct(team.name) as name from Car

This second query works as well, returning the list of teams (as documents).

select expand(distinct(team)) from Car

This third query works and returns exactly the same result that the previous, so it ignores the ".name" part of the select.

select expand(distinct(team)).name from Car

This last query fails. Well it doesn't fail, but it doesn't return what I expected, it returns a list of links to the teams.

select distinct(team).name from Car

Tests running the queries: Tests.

Answer 2

I have try with this db

create class Team
CREATE PROPERTY Team.name String

create class DRIVER
CREATE PROPERTY DRIVER.name String

create class Car
CREATE PROPERTY Car.name String

CREATE PROPERTY Team.CARS_HERITAGE LINKSET Car
CREATE PROPERTY Car.BUILT_BY LINK Team
CREATE PROPERTY Car.DRIVEN_BY LINKSET DRIVER
CREATE PROPERTY DRIVER.DRIVER_OF LINKSET Car

INSERT

INSERT INTO TEAM(name) values ("Ferrari"),("Renault") // Ferrari 12:0  Renault 12:1
insert into Driver(name) values ("Fernando Alonso"),("Giancarlo Fisichella") // Alonso 13:0  Fisichella 13:1
insert into car(name,BUILT_BY,DRIVEN_BY) values ("car ferrari",#12:0,[#13:0,#13:1])
insert into car(name,BUILT_BY,DRIVEN_BY) values ("car renault",#12:1,[#13:0])

Query

select BUILT_BY.name as TeamName from car where DRIVEN_BY.name contains "Fernando Alonso"

Hope it helps.

UPDATE 1

select distinct(BUILT_BY.name) as team from car where DRIVEN_BY.name contains "Fernando Alonso"

FROM JAVA API

UPDATE 2

FROM JAVA API