Visitar URL original
Implement Join in OrientDb. · Issue #7888 · orientechnologies/orientdb · GitHub
Skip to content

Implement Join in OrientDb. #7888

New issue
New issue
Closed
Closed
Implement Join in OrientDb.#7888
Assignees
luigidellaquila
Labels
question

Description

@thanveeraqtharah
thanveeraqtharah
opened on Nov 16, 2017

OrientDB Version: <2.1.9>

Java Version: <1.8>

OS: <Windows 10>

Expected behavior

I wana Join two documents based on their respective filednames , where both fileds have the same value.
For exampple consider I have two classes/document by name SOURCE_TABLE and DESTINATION_TABLE respectively. Each table(class/document) has their own fields . Consider for example SOURCE_TABLE has field by name 'col5' and DESTINATION_TABLE has field name 'col3'. Now I wana retreive the records form the both the tables where SOURCE_TABLE.col5's value == DESTINATION_TABLE.col3's value . Since there is no join in orientdb unlike Sql, Can you please help me how would I implement Join as per my requirement as I expalined in the above example.

Thank you

Actual behavior

Steps to reproduce

Activity

  1. luigidellaquila
    self-assigned this
    on Nov 16, 2017
  2. luigidellaquila
    added
    question
    on Nov 16, 2017

  3. luigidellaquila commented on Nov 16, 2017

    @luigidellaquila
    luigidellaquila
    on Nov 16, 2017
    Member

    Hi @thanveeraqtharah

    doing joins on random fields is not actually what a graph database does, I doubt we will ever implement it...

    You can simulate it as follows:

    MATCH
{class:SOURCE_TABLE, as:a, where:(...)},
{class:DESTINATION_TABLE, as:b, where:($matched.a.col5 = $currentMatch.col3)}
RETURN a, b

    but please consider that it will be extremely inefficient and memory consuming.

    What I suggest here is to review your domain and replace this join with an edge relationship

    Thanks

    Luigi

  4. luigidellaquila
    closed this as completedon Nov 16, 2017

  5. thanveeraqtharah commented on Nov 16, 2017

    @thanveeraqtharah
    thanveeraqtharah
    on Nov 16, 2017
    Author

    Thank you fpr your reply.. :).
    I got the below error when tried :

    Cannot find a command executor for the command request: sql.MATCH {class:temp_source_records, as:a }, {class:temp_dest_records, as:b, where:($matched.a.col5 = $currentMatch.col3)}RETURN a, b

  6. luigidellaquila commented on Nov 16, 2017

    @luigidellaquila
    luigidellaquila
    on Nov 16, 2017
    Member

    Oh, I just noticed that you are using v 2.1.9... it's not supported anymore and it doesn't have support for MATCH statements.

    My suggestion is to upgrade to v 2.2.30 as soon as you can, that's much more stable, it has better performance and it's fully supported

    Thanks

    Luigi

  7. thanveeraqtharah commented on Nov 16, 2017

    @thanveeraqtharah
    thanveeraqtharah
    on Nov 16, 2017
    Author

    ok but if I use that 2.2.30. i get the below Exception , which was not thrown while using 2.1.9 :

    Exception in thread "main" java.lang.NoClassDefFoundError: com/orientechnologies/orient/core/security/OSecuritySystem
    at java.lang.ClassLoader.defineClass1(Native Method)
    at java.lang.ClassLoader.defineClass(ClassLoader.java:763)
    at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142)
    at java.net.URLClassLoader.defineClass(URLClassLoader.java:467)
    at java.net.URLClassLoader.access$100(URLClassLoader.java:73)
    at java.net.URLClassLoader$1.run(URLClassLoader.java:368)
    at java.net.URLClassLoader$1.run(URLClassLoader.java:362)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:361)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    at com.orientechnologies.orient.server.OServerMain.create(OServerMain.java:28)
    at com.superizzi.prismapp.db.DatabaseHelper.startServer(DatabaseHelper.java:16)
    at com.superizzi.prismapp.PrismAppApplication.main(PrismAppApplication.java:14)
    Caused by: java.lang.ClassNotFoundException: com.orientechnologies.orient.core.security.OSecuritySystem
    at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    ... 15 more

  8. luigidellaquila commented on Nov 16, 2017

    @luigidellaquila
    luigidellaquila
    on Nov 16, 2017
    Member

    Hi @thanveeraqtharah

    Please check your classpath and make sure you have all the versions of OrientDB modules aligned to 2.2.30, this error could be due to duplicate Jars

    Thanks

    Luigi

  9. thanveeraqtharah commented on Nov 17, 2017

    @thanveeraqtharah
    thanveeraqtharah
    on Nov 17, 2017
    Author

    MATCH {class:SOURCE_TABLE, as:a }, {class:DESTINATION_TABLE, as:b, where:($matched.a.col5 = $currentMatch.col3)}RETURN a, b

    the above query returns only the record of SOURCE_TABLE , it doesnt return the record matching in the DESTINATION_TABLE .

  10. luigidellaquila commented on Nov 17, 2017

    @luigidellaquila
    luigidellaquila
    on Nov 17, 2017
    Member

    Hi @thanveeraqtharah

    I just did the following:

    CREATE CLASS SOURCE_TABLE
insert into SOURCE_TABLE set col5 = 'a'
insert into SOURCE_TABLE set col5 = 'b'

CREATE CLASS DESTINATION_TABLE
insert into DESTINATION_TABLE set col3 = 'a'
insert into DESTINATION_TABLE set col3 = 'b'
insert into DESTINATION_TABLE set col3 = 'b'
insert into DESTINATION_TABLE set col3 = 'c'

MATCH 
   {class:SOURCE_TABLE, as:a }, 
   {class:DESTINATION_TABLE, as:b, where:($matched.a.col5 = $currentMatch.col3)}
RETURN a, b

    and it works as expected. Am I missing something?

    Thanks

    Luigi

  11. thanveeraqtharah commented on Nov 17, 2017

    @thanveeraqtharah
    thanveeraqtharah
    on Nov 17, 2017
    Author

    can you please post the output of the same?

    Thank you:)

  12. luigidellaquila commented on Nov 17, 2017

    @luigidellaquila
    luigidellaquila
    on Nov 17, 2017
    Member

    Here we go

    schermata 2017-11-17 alle 13 28 23

    Thanks

    Luigi

  13. thanveeraqtharah commented on Nov 17, 2017

    @thanveeraqtharah
    thanveeraqtharah
    on Nov 17, 2017
    Author

    i.e is to say you will get 5 records returned as output?

  14. luigidellaquila commented on Nov 17, 2017

    @luigidellaquila
    luigidellaquila
    on Nov 17, 2017
    Member

    Why five? I get three... It's an inner join, you will get three records, one per single couple of SOURCE_TABLE/DESTINATION_TABLE that matches the JOIN condition.

    If you need the single records expanded, you can do RETURN $elements

    Thanks

    Luigi

  15. thanveeraqtharah commented on Nov 17, 2017

    @thanveeraqtharah
    thanveeraqtharah
    on Nov 17, 2017
    Author

    oh my bad. Thanks . And I have a embedded orientdb . how can I use orient db studio and connect to the database for querying via UI? Like you have queried via OrientDb studio. I had installed Orientdb Studio, but when tried to accesss using Url : http://localhost:2480. "This site can’t be reached" error message is displayed on browser.

  16. luigidellaquila commented on Nov 17, 2017

    @luigidellaquila
    luigidellaquila
    on Nov 17, 2017
    Member

    If you have OrientDB embedded, you have to start the server from inside the application, see http://orientdb.com/docs/2.2.x/Embedded-Server.html

    But if it's just for a test, you can copy the DB folder on a stand-alone OrientDB instance and launch it

    Thanks

    Luigi

  17. thanveeraqtharah commented on Nov 17, 2017

    @thanveeraqtharah
    thanveeraqtharah
    on Nov 17, 2017
    Author

    Thank you :)

  18. thanveeraqtharah commented on Nov 17, 2017

    @thanveeraqtharah
    thanveeraqtharah
    on Nov 17, 2017
    Author

    I will get back to you, if I have problem accessing orientDb Studio. Thank you so much :) Very much appreciable for your realtime response and help 👍

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

question

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions