Hi Fabio,
SQL-like joins don't go down well in OrientDB. Please have a look
here: http://orientdb.com/docs/last/SQL-Match.html
try a step wise approach, something like this:
match {class Drug, as:D} -drugToDemo- {class:Demo, as:E} return D.propertyX
as whatever,E.propertyY as foo
then perhaps, a bit more elborate:
select whatever,foo from ( the above match) group by ... etc,etc
Tore
On Monday, February 6, 2017 at 10:51:16 PM UTC+1, Fabio Rinnone wrote:
>
> Hi there,
>
> I migrated a relational database from MySQL to OrientDB but I have an
> issue in producing a query and I hope to have an help from community.
>
> Consider two tables named drug and demo and suppose I launch the
> following query in MySQL with aggregation:
>
> select role_cod, count(de.caseid) as count
> from drug dr
> join demo de on (dr.primaryid = de.primaryid)
> where lower(drugname) like '%paracetamol%'
> group by role_cod
>
> Attribute role_cod represents role of a drug: I would to count all cases
> (the attiribute caseid represents a single case) in which paracetamol is
> reported, aggregate by role.
>
> Table drug and demo are joined by an attribute called primaryid. For
> every one row in the demo table, we can have one or more rows in the
> drug tables.
>
> In OrientDB I created to class named drug and demo linked by edge of
> class named drugToDemo.
>
> If I launch the following query in OrientDB I obtain the same correct:
>
> select role_cod, count($caseid) as count
> from drug
> let $caseid = in('drugToDemo').caseid
> where drugname.toLowerCase() like '%paracetamol%'
> group by role_cod
>
> But if I would to obtain all *distinct* cases, in MySQL I launch
> follwing query:
>
> select role_cod, count(distinct de.caseid) as count
> from drug dr
> join demo de on (dr.primaryid = de.primaryid)
> where drugname.toLowerCase() like '%paracetamol%'
> group by role_cod
>
> Now, I don't know how to represent this query in OrientDB. I tried the
> following query using the distinct() function:
>
> select role_cod, count(distinct($caseid)) as count
> from drug
> let $caseid = in('drugToDemo').caseid
> where drugname.toLowerCase() like '%paracetamol%'
> group by role_cod
>
> but the composition of count and distinct functions don't produce the
> same results of count obtained in MySQL. In this case I obtain always 1
> as count and I think it is always the only result obtainable.
>
> Any ideas?
>
> Thank you.
>
> --
> Fabio Rinnone
> Skype: fabiorinnone
> Web: http://www.fabiorinnone.eu
>
>
--
---
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
