Hi syshex which version of ODB do you have ?
2014-12-18 18:32 GMT+01:00 syshex <[email protected]>: > > 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. > -- --- 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.
