Hi everyone.
Have a schema that goes somewhat along these lines :
( Institute ) <---owner --- ( Contract ) --- hired ---> ( Company )
I'm trying to create an SQL query that shows all companies that have
contracts with a particular institute and tells me how many contracts each
company has. I've tried several different ways , but probably due to lack
of experience I'm just not being able to get it.
I've tried with traversal and other, but I basically got to this point now
:
1- With this query I can get all companies that have a contract with a
particular institute
select
EXPAND(in('owner').out('hired'))
from Institute where itemid = 47
limit 10000
It gives me the list of all companies , with an overall record count of
162. (this is important, the record count!)
2- next I try to expand that query to start grouping by and counting :
select
EXPAND(in('owner').out('hired'))
from Institute where itemid = 47
group by description
limit 10000
I was expecting to get a list of fewer records ( because some companies
have multiple contracts with this Institute ) . But what I got back was 0
Records
I though : OK , the EXPAND function is ran after the group by and
therefore the queries being grouped by at that point have no property named
"description", just the id .
3- Instead of group by, I though : let me try the order by clause to see
what happens
select
EXPAND(in('owner').out('hired'))
from Institute where itemid = 47
order by description DESC
limit 10000
I get a list of 162 records again , all ordered perfectly , showing me
several repeated records , therefore, the several times a company was hired
by that institute.
4 - Because I have a list of 162 records with this query, I though about
using it as a sub-query of another select statement, and perform the group
by and count() with that select.
select
description, count(*) as hiredTimes
from (
select
EXPAND(in('owner').out('hired'))
from Institute where itemid = 47
limit 10000
)
group by description
order by hiredTimes DESC
limit 10000
It returns me 112 records , of which none has a hiredTimes > 1, which tells
me that for this select only 112 records were evaluated.
5 - I decided to try running this previous select without the group by
clause, just to make sure
select
*
from (
select
EXPAND(in('owner').out('hired'))
from Institute where itemid = 47
limit 10000
)
limit 10000
I get a list of records, but only 112 of them .
I must be missing something. Should I by going about this in a different
way ?
Thanks much for your help.
--
---
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.
