Just gave it a quick spin, the rc1 with the db I'm doing tests with and yes , now things are making sense to me.
Query 4 works like I want. Perfect! Thanks so much for taking the time. On Thursday, December 18, 2014 8:58:15 PM UTC, Enrico Risa wrote: > > Could you retry with 2.0-rc1. Should fix the behaviour of your latest > query 4/5 > Il 18/dic/2014 21:55 "syshex" <[email protected] <javascript:>> ha scritto: > >> Forgot to mention that ! So sorry, >> Version 2.0 M3 on linux >> >> On Thursday, December 18, 2014 7:06:16 PM UTC, Enrico Risa wrote: >>> >>> 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] <javascript:>. >> 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.
