Projections and joins

[Fabio Rinnone]

Hi,

I have a doubt about using of projections in OrientDB.

My case is the following: suppose we have to classes named respectively
demo and drug. First class contains patient demographic and
administrative information and the second one contains drug and biologic
information. Classe demo and drug have a 1-to-many relationship, in fact
for every one item in demo class, we can have one or more records in
drug class.

Well, suppose event_dt is an attribute of demo class and drugname is an
attribute of drug class: in a relational database (for instance MySQL) I
can define a query as follow (suppose primaryid is a common attribute
used as foreign key):

select demo.event_dt, drug.drugname from demo, drug where demo.primaryid
= drug.primaryid;

In OrientDB I created a class namerd drugToDemo as edge that have the
scope to link demo and drug class records.

According to tutorial[0], because the relationship is 1-to-many I can do
the following query:

select out_drugToDemo.event_dt, drugname from drug limit 5

and this result:

+----+--------------+---------+
|# |out_drugToDemo|drugname |
+----+--------------+---------+
|0 |[20000118] |AVONEX |
|1 |[20000118] |AVONEX |
|2 |[20000118] |ZOLOFT |
|3 |[20000118] |LIPITOR |
|4 |[20000118] |SYNTHROID|
+----+--------------+---------+

I think the first column obtained is populated by arrays of single
elements, right? In fact if I do the following query:

select out_drugToDemo.event_dt[0] as event_dt, drugname from drug

I obtained this result, similar to result that I can obtain in MySQL:

+----+--------+---------+
|# |event_dt|drugname |
+----+--------+---------+
|0 |20000118|AVONEX |
|1 |20000118|AVONEX |
|2 |20000118|ZOLOFT |
|3 |20000118|LIPITOR |
|4 |20000118|SYNTHROID|
+----+--------+---------+

I can do this query because I'm sure that for every record of drug class
I can have exactly on record of demo class.

Now, for instance I try to do the following query:

select event_dt, in_drugToDemo.drugname from demo

Because I can have for every item in demo class, none, one, or more
record of drug class I obtained a result similar to the following:

+----+--------+----------------------------------+
|# |event_dt|in_drugToDemo |
+----+--------+----------------------------------+
|0 |20000118|[AVONEX,ZOLOFT,LIPITOR,SYNTHROID] |
|1 |20021015|[AVONEX,ACETAMINOPHEN.] |
|2 |20040204|[GLIVEC,CYLOCIDE,LASIX,FOY] |
|3 |200011 |[TACROLIMUS.,AZATHIOPRINE.] |
|4 |200303 |[AVONEX,RITALIN,WELLBUTRIN,RXANAX]|
+----+--------+----------------------------------+

Now, my question is: how I can expand arrays obtained by navigating into
in-edge drugToDemo and list all results as single items?

For instance, if I try the following:

select event_dt, expand(in_drugToDemo.drugname) from demo;

I obtain the following result:

+----+----------+
|# |value |
+----+----------+
|0 |AVONEX |
|1 |ZOLOFT |
|2 |LIPITOR |
|3 |SYNTHROID |
|4 |AVONEX |
+----+----------+

but event_dt is now showed as result.

Another question: if I want to project all attributes of classes what do
I do? In other words, how I can do a query as following in OrientDB?

select demo.*, drug.* from demo, drug where demo.primaryid = drug.primaryid

Thanks for patience, I hope I was clearly in explaination of my issue.

Kind regards.

[0] http://orientdb.com/docs/last/Tutorial-Relationships.html

--
Fabio Rinnone
Skype: fabiorinnone
Web: http://www.fabiorinnone.eu

[Fabio Rinnone]

Il 28/12/2016 11:39, Fabio Rinnone ha scritto:
> Another question: if I want to project all attributes of classes what do
> I do? In other words, how I can do a query as following in OrientDB?
>
> select demo.*, drug.* from demo, drug where demo.primaryid = drug.primaryid

Please, can someone help me to translate this query in OrientDB? I think
it's a very simple question: for this moment my only solution is to
project all attributes explicitly but with this approach I think query
is too long.

This is my actual solution:

select
primaryid,
caseid,
caseversion,
i_f_code,
$drug.drug_seq,
$drug.role_cod,
$drug.drugname,
$drug.prod_ai
from demo
let $drug = in('drugToDemo')
unwind drug_seq, role_cod, drugname, prod_ai

Relationship beetween demo and drug is the following:

drug --> (drugToDemo) --> demo

Relationship type is 1-to-n.

In my example I suppose that number of attributes of demo and drug class
are respectively 4 but in my real case they are more than 4 and the real
query is more long than this.

Thanks in advance.