Re: [orientdb] Help with SQL query
Sorry, the first query is
TRAVERSE in('scopedEdge') FROM (
SELECT expand(in('scopeEdge'))
FROM V WHERE file IS NOT NULL
)
2017-06-19 15:26 GMT+02:00 Luigi Dell'Aquila :
> Hi Erik,
>
> I'm afraid I don't completely understand your model:
> - how are the edges directed? From parent to child or from child to parent?
> - are File and Folder two different classes that extend V?
> - what does the "file" property represent? Is it a string or what?
>
>
> If I got it right, you have many alternatives to do this, one is as
> follows:
>
> TRAVERSE in('scopedEdge') FROM (
>
> SELECT expand(in('scopeEdge')
>
> ) FROM V WHERE file IS NOT NULL
>
>
> or
>
>
> SELECT FROM V
>
> WHERE out("scopedEdge").size() > 0
>
>
> or
>
> MATCH
>
> {class:File, where:(file is not null)} <-scopedEdge- {} <-scopedEdge-
> {as:folder, while:(true)}
>
> RETURN $elements
>
>
> I hope it helps
>
> Thanks
>
> Luigi
>
>
> 2017-06-19 15:15 GMT+02:00 Erik T :
>
>> I have a tree with 5 vertices
>>
>> Folder (a)
>> /\
>> (b) File Folder (c)
>> / \
>> (d) File Folder (e)
>>
>> I would like to retrieve the folders that contain a file. So I want to
>> get back (a) and (c) from my query. The edges are lightweight edges and are
>> labeled 'scopeEdge'. I placed a property on the files in my tree and
>> labeled them 'file'. These queries don't work
>>
>> select out from V where file is not null --> gives back temporary vertices
>>
>> select from V where out('scopeEdge').file is not null --> gives back (a),
>> (b), (c), (d), and (e)
>>
>> I can get 1 query to work but its really complicated. It makes me think
>> I'm doing something wrong. Is there something easier than this?
>>
>> SELECT FROM (TRAVERSE in('scopedEdge') FROM (SELECT FROM #RID# WHERE file IS
>> NOT NULL)) WHERE $depth > 0 --> gives back (a) and (c)
>>
>>
>>
>> --
>>
>> ---
>> 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 orient-database+unsubscr...@googlegroups.com.
>> 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 orient-database+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: [orientdb] Help with SQL query
Hi Erik,
I'm afraid I don't completely understand your model:
- how are the edges directed? From parent to child or from child to parent?
- are File and Folder two different classes that extend V?
- what does the "file" property represent? Is it a string or what?
If I got it right, you have many alternatives to do this, one is as follows:
TRAVERSE in('scopedEdge') FROM (
SELECT expand(in('scopeEdge')
) FROM V WHERE file IS NOT NULL
or
SELECT FROM V
WHERE out("scopedEdge").size() > 0
or
MATCH
{class:File, where:(file is not null)} <-scopedEdge- {} <-scopedEdge-
{as:folder, while:(true)}
RETURN $elements
I hope it helps
Thanks
Luigi
2017-06-19 15:15 GMT+02:00 Erik T :
> I have a tree with 5 vertices
>
> Folder (a)
> /\
> (b) File Folder (c)
> / \
> (d) File Folder (e)
>
> I would like to retrieve the folders that contain a file. So I want to get
> back (a) and (c) from my query. The edges are lightweight edges and are
> labeled 'scopeEdge'. I placed a property on the files in my tree and
> labeled them 'file'. These queries don't work
>
> select out from V where file is not null --> gives back temporary vertices
>
> select from V where out('scopeEdge').file is not null --> gives back (a),
> (b), (c), (d), and (e)
>
> I can get 1 query to work but its really complicated. It makes me think
> I'm doing something wrong. Is there something easier than this?
>
> SELECT FROM (TRAVERSE in('scopedEdge') FROM (SELECT FROM #RID# WHERE file IS
> NOT NULL)) WHERE $depth > 0 --> gives back (a) and (c)
>
>
>
> --
>
> ---
> 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 orient-database+unsubscr...@googlegroups.com.
> 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 orient-database+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: [orientdb] Help with SQL query with Count or relationships
Hi syshex
which version of ODB do you have ?
2014-12-18 18:32 GMT+01:00 syshex sys...@gmail.com:
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 1
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 1
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 1
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 1
)
group by description
order by hiredTimes DESC
limit 1
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 1
)
limit 1
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 orient-database+unsubscr...@googlegroups.com.
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 orient-database+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: [orientdb] Help with SQL query with Count or relationships
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 sys...@gmail.com javascript::
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 1
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 1
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 1
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 1
)
group by description
order by hiredTimes DESC
limit 1
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 1
)
limit 1
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 orient-databa...@googlegroups.com 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 orient-database+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: [orientdb] Help with SQL query with Count or relationships
Could you retry with 2.0-rc1. Should fix the behaviour of your latest
query 4/5
Il 18/dic/2014 21:55 syshex sys...@gmail.com 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 sys...@gmail.com:
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 1
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 1
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 1
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 1
)
group by description
order by hiredTimes DESC
limit 1
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 1
)
limit 1
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 orient-databa...@googlegroups.com.
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 orient-database+unsubscr...@googlegroups.com.
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 orient-database+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: [orientdb] Help with SQL query with Count or relationships
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 sys...@gmail.com 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 sys...@gmail.com:
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 1
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 1
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 1
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 1
)
group by description
order by hiredTimes DESC
limit 1
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 1
)
limit 1
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 orient-databa...@googlegroups.com.
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 orient-databa...@googlegroups.com 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 orient-database+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
