OrientDB sql query involving multiple classes

[Joel Mathew]

Hi group,

I have below schema with three classes

orientdb {demo1}> select from Person

----+-----+-----+------+--------+---------

#   |@RID |name |gender|out_owns|out_likes

----+-----+-----+------+--------+---------

0   |#11:0|Matt |M     |#12:0   |#13:0

1   |#11:1|Helen|F     |#12:1   |#13:1

----+-----+-----+------+--------+---------

orientdb {demo1}> select from Car

----+-----+-------+-------

#   |@RID |model  |in_owns

----+-----+-------+-------

0   |#12:0|Ferrari|#11:0

1   |#12:1|BMW    |#11:1

----+-----+-------+-------

orientdb {demo1}> select from Movie

----+-----+--------------+--------

#   |@RID |mov_name      |in_likes

----+-----+--------------+--------

0   |#13:0|Need For Speed|#11:0

1   |#13:1|Matrix        |#11:1

----+-----+--------------+--------

Person(name,gender)

Car(model)

Movie(mov_name) 

Person > owns > Car

Person > likes > Movie

I'm looking at a query which matches values across three classes across the edges

Find Person.name where Person.gender='M' and who owns Car.model='Ferrari' and who likes Movie.mov_name='Need For Speed'

One way to achieve the same with gremlin is

g.V('@class','Car').has('model','Ferrari').in('owns').has('@class','Person').has('gender','M').as('x').out('likes').has('@class','Movie').has('mov_name','Need For Speed').select(["x"])

I'm using OrientDB 1.7-rc2-SNAPSHOT..

Please help me with some pointers towards equivalent orientdb sql.

Thanks,

Mathew

[Andrey Lomakin]

Hi,

Could you try 

select name from Person where gender = 'M' and out('owns')[0].model = "Ferrari" and out('likes')[0].mov_name = "Need For Speed"

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Best regards,
Andrey Lomakin.

Orient Technologies
the Company behind OrientDB

[Joel Mathew]

Thanks Andrey..It worked..

I was going wild with

select from Person let $c = (select from ( traverse out_owns from $parent.$current)  where model = 'Ferrari') , let $m = (select from (traverse out_likes from $parent.$current) where mov_name = "Need For Speed")  where gender='M' AND $m.size() > 0 AND $c.size() > 0 

But it didn't work

And one possible bug I encountered during my exploration with datetime in filter field -- 

select expand(in('owns')[gender='M'].out('likes')[mov_name="Need For Speed"]) as p from Car where model = 'Ferrari'     ------> Works

------>>>>>  Below doesn't work

select expand(in('owns')[birthtime = "1980-01-01 00:01:00"].out('likes')[mov_name="Need For Speed"]) as p from Car where model = 'Ferrari'

dt = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("1980-01-01 00:01:00")

select expand(in('owns')[birthtime = dt].out('likes')[mov_name="Need For Speed"]) as p from Car where model = 'Ferrari'

To replicate

create class Person extends V

create property Person.name string

create property Person.gender string

create property Person.birthtime DATETIME

create class Car extends V

create property Car.model string

create class Movie extends V

create property Movie.mov_name string

create class likes extends E

create class owns extends E

create vertex Person set name = 'Matt', gender = 'M', birthtime = "1980-01-01 00:01:00"

create vertex Person set name = 'Helen', gender = 'F' , birthtime = "1990-01-01 00:01:00"

create vertex Car set model = 'Ferrari'

create vertex Car set model = 'BMW'

create vertex Movie set mov_name = 'Need For Speed'

create vertex Movie set mov_name = 'Matrix'

create edge owns from (select from Person where name = 'Matt') to (select from Car where model = 'Ferrari')

create edge owns from (select from Person where name = 'Helen') to (select from Car where model = 'BMW')

create edge likes from (select from Person where name = 'Matt') to (select from Movie where mov_name = 'Need For Speed')

create edge likes from (select from Person where name = 'Helen') to (select from Movie where mov_name = 'Matrix')

---+-----+-----+------+-------------------+--------+---------

#   |@RID |name |gender|birthtime          |out_owns|out_likes

----+-----+-----+------+-------------------+--------+---------

0   |#11:0|Matt |M     |1980-01-01 00:01:00|#12:0   |#13:0

1   |#11:1|Helen|F     |1990-01-01 00:01:00|#12:1   |#13:1

----+-----+-----+------+-------------------+--------+---------

Just thought of reporting here..

Regards,

Joel

[Ersin Sevinc]

Hi there, m new at orient db trying to figure out something about relations , 

In this code Line => create edge owns from (select from Person where name = 'Matt') to (select from Car where model = 'Ferrari')

We have edge between record to record. Its fine for few records. But what will happen if i ve millions of data ? Should i create edge from backend for every new record?

Or can i just link the Property Fields like => create edge owns from Person.Name to Car.model ??

I want to know that is there any possible way for relation database like MSSQL , you know in ms sql , we can create PK and FK between Fields , with that way we do not need to create relations for every new record.

Here another question -> select car.model person.name from car, person Where ..............................   I think i can not use 2 different class after FROM m i right ?

27 Mart 2014 Perşembe 17:03:33 UTC+3 tarihinde Andrey Lomakin yazdı:

[Luigi Dell'Aquila]

Hi Ersin,

There is no SQL statement to do this, but you can use Teleporter (included in OrientDB Studio) to do a full import from SQL to a pure graph.

About multiple classes as target in a query, you cannot do it that way, what you probably need is MATCH https://orientdb.com/docs/3.0.x/sql/SQL-Match.html

Thanks

Luigi

To unsubscribe from this group and stop receiving emails from it, send an email to orient-database+unsubscribe@googlegroups.com.

[Ersin Sevinc]

Thanks alot Luigi. You guys are really helping all developers. But i' ve an extra question for you :/

We have some problems about OrientObject is not Json serializable Error.

Let me explain our code in simple way. We have Customers,Admins(Company),Product,Personel  classes and CRUD for all of them in back end (Python).

HERE IS MY VERTEX

createPersonel = configDb.client.command("create vertex PersonelInformation set Adress ='" + adress
                                         + "',""CitizenId='" + citizenId + "',CompanyCode='" + companyCode
                                         + "',""Email='" + email + "',Name='" + name + "', Surname='" + surname
                                         + "',Phone=" + phone)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

HERE IS MY EDGE

createRelation = configDb.client.command("create edge PersonelRelation from " + personelRid + " to " + companyRid + "' ")
-------------------------------------------------------------------------------

SELECT QUERY FOR ALL INFORMATIONS IN CLASS

all = []
data = configDb.client.command("select * from PersonelInformation")
for i in range(0, len(data)):
    all.append(data[i].oRecordData)
return json.dumps(all)

-------------------------------------------------------------------------------------------------------------------------

Everything is fine except select query, I can not see the relationship. When there is a relation in some records , after execute the query i get this error .

[2018-03-25 13:24:49,575] ERROR in app: Exception on /personelInfo/getAll [GET]
Traceback (most recent call last):
  File "D:\Outputs\pyCharm\rms\venv\lib\site-packages\flask\app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "D:\Outputs\pyCharm\rms\venv\lib\site-packages\flask\app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "D:\Outputs\pyCharm\rms\venv\lib\site-packages\flask\app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "D:\Outputs\pyCharm\rms\venv\lib\site-packages\flask\_compat.py", line 33, in reraise
    raise value
  File "D:\Outputs\pyCharm\rms\venv\lib\site-packages\flask\app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "D:\Outputs\pyCharm\rms\venv\lib\site-packages\flask\app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "D:/Outputs/pyCharm/rms\Middleware\Decator.py", line 16, in decorated_function
    return f(*args, **kwargs)
  File "D:/Outputs/pyCharm/rms\Resources\PersonelinformationResource.py", line 105, in GetAll
    return re
  File "D:\Outputs\pyCharm\rms\venv\lib\site-packages\flask\json.py", line 123, in dumps
    rv = _json.dumps(obj, **kwargs)
  File "D:\Program Files\Python36\Lib\json\_init_.py", line 238, in dumps
    **kw).encode(obj)
  File "D:\Program Files\Python36\Lib\json\encoder.py", line 199, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File "D:\Program Files\Python36\Lib\json\encoder.py", line 257, in iterencode
    return _iterencode(o, 0)
  File "D:\Outputs\pyCharm\rms\venv\lib\site-packages\flask\json.py", line 80, in default
    return _json.JSONEncoder.default(self, o)
  File "D:\Program Files\Python36\Lib\json\encoder.py", line 180, in default
    o._class.name_)
TypeError: Object of type 'OrientBinaryObject' is not JSON serializable
127.0.0.1 - - [25/Mar/2018 13:24:49] "GET /personelInfo/getAll HTTP/1.1" 500 -

---------------------------------------------------------------------------------------------------------------------------------------------------------------
IN THE OTHER HAND, When i delete records with relations , My Select query works fine. I can see all records without relation.
But my main goal is getting companyCode when i query on PersonelInformation vertex.


Also i tried This.

-select adress from user where = out('PersonelRelation')[0].name = "orient"

------------------------------------------------------------------------------------------------------------------------------------

OUT comes like this.
 b64:'AQAAAAEAggAAAAAAAAAA'

IN SHORT my main problem is when i connect the Company And Personel Classes, i want to see Company class informations over personelInformation Query. But i dont want to restrict my query like , select from where rid = 37
i just want to get company information  wihtout giving any company information in Personel Query. Just want to get informations about Company in Back end and want to read them. Its not important after a query like = select from personel where CompanyCode "abc"


Really Thanks alot from now
Looking for your answer


Regards,
Ersin




5 Mart 2018 Pazartesi 12:21:40 UTC+3 tarihinde Luigi Dell'Aquila yazdı:

[Luigi Dell'Aquila]

Hi Ersin,

The problem seems to be in the python driver, did you try to run the queries in Studio and see if they work fine?

If you are using OrientDB v 3.0 RC you can write a query as follows:

MATCH

   {class:Company, as:company} <-PersonnelRelation- {as:person}

RETURN company:{*}, person:{*}

This is kind of an inner join based on PersonnelRelation. You can also add WHERE conditions to the pattern to filter the result, see http://orientdb.com/docs/3.0.x/sql/SQL-Match.html

Thanks

Luigi

To unsubscribe from this group and stop receiving emails from it, send an email to orient-database+unsubscribe@googlegroups.com.

[Ersin Sevinc]

Yeahh!! it worked.  Really thanks about that.

"databasename": {

        "mapping": {

            "fromTable": "source table",

            "fromColumns": ["source field"],

            "toTable": "destination table",

            "toColumns": ["destination table"],

            "direction": "direct"

        }

         

}

Also i found this on the internet about mapping or creating relation , but i couldn't find anything about it in documentation. M i missing something? Or what is the topic name of this code line? Or simply is this code line true? 

Regards 

Ersin

28 Mart 2018 Çarşamba 10:33:45 UTC+3 tarihinde Luigi Dell'Aquila yazdı: