Help formulating a query

Hello, I’m a dgraph and graphql newbie and I’m having trouble figuring out a query. I have nodes that have this relationship:

User -(has_ip)-> IP Address -(located_in)-> Location

A user can have multiple IPs, and an IP can only have one Location.

What is the best way to query for users that are linked to more than one location?

I can query for users that have multiple IPs but those IPs might all be mapped to one location, in which case, I don’t care about those results.

Thanks in advance!

Here is an approach using the “groupby” features in Dgraph.

  1. Schema
<hasIP>: [uid] @count @reverse .
<located_in>: uid @reverse .
<name>: string @index(term, trigram) @lang .
  1. Data
    user with overlapping location (user4 and user2 has different ips, but pointing to the same location)
{
  set{
    
    _:user1 <name> "user4" .
    _:user1 <hasIP> _:u1i1 .
    _:u1i1 <name> "ip4" .
    _:u1i1 <located_in> _:loc1 .
    _:loc1 <name> "paris" .

    

    _:user1 <hasIP> _:u1i2 .
    _:u1i2 <name> "ip5" .
    _:u1i2 <located_in> _:loc2 .
    _:loc2 <name> "hongkong" .


    

    _:user1 <hasIP> _:u1i3 .
    _:u1i3 <name> "ip6" .
    _:u1i3 <located_in> _:loc1 .
    
    
  }
}
{
  set{
    
    _:user1 <name> "user2" .
    _:user1 <hasIP> _:u1i1 .
    _:u1i1 <name> "ip1" .
    _:u1i1 <located_in> _:loc1 .
    _:loc1 <name> "london" .

    

    _:user1 <hasIP> _:u1i2 .
    _:u1i2 <name> "ip2" .
    _:u1i2 <located_in> _:loc1 .


    

    _:user1 <hasIP> _:u1i3 .
    _:u1i3 <name> "ip3" .
    _:u1i3 <located_in> _:loc3 .
    _:loc3 <name> "paris" .
    
  }
}

user with distinct locations (should not show up in query result)

{
  set{
    
    _:user1 <name> "user3" .
    _:user1 <hasIP> _:u1i1 .
    _:u1i1 <name> "ip1" .
    _:u1i1 <located_in> _:loc1 .
    _:loc1 <name> "london" .

    

    _:user1 <hasIP> _:u1i2 .
    _:u1i2 <name> "ip2" .
    _:u1i2 <located_in> _:loc2 .
    _:loc2 <name> "hongkong" .


    

    _:user1 <hasIP> _:u1i3 .
    _:u1i3 <name> "ip3" .
    _:u1i3 <located_in> _:loc3 .
    _:loc3 <name> "paris" .
    
  }
}
  • Query using groupby on location node and count of locations >1
{
  withip as var(func : has(hasIP)){
    hasIP @groupby(located_in) {
      located as count(uid)
    }
  }
  
  getLocations(func: uid(located)) @filter(gt(val(located),1))  {
    name
		countlocation: val(located)
    ~located_in {      
      ~hasIP @filter(uid(withip)){
        userName:name
       }
    }
  }  
}

Result is as follows. user2 and user4 show up but not user3

{
  "data": {
    "getLocations": [
      {
        "name": "london",
        "countlocation": 2,
        "~located_in": [
          {
            "~hasIP": [
              {
                "userName": "user2"
              }
            ]
          },
          {
            "~hasIP": [
              {
                "userName": "user2"
              }
            ]
          }
        ]
      },
      {
        "name": "paris",
        "countlocation": 2,
        "~located_in": [
          {
            "~hasIP": [
              {
                "userName": "user4"
              }
            ]
          },
          {
            "~hasIP": [
              {
                "userName": "user4"
              }
            ]
          }
        ]
      }
    ]
  }
3 Likes

Nice, thank you! this query doesn’t quite produce what i was expecting. I would only like to know the results when a user is seen in more than 1 location (not IP) - say, Paris and Hong Kong. so the user can only have one IP in Paris and one in Hong Kong and that would show as a result.

I think the key is in the @groupby. The result looks like this:

"@groupby": [
  {
    "located_in": "0x24",
    "count": 2
  },
  {
    "located_in": "0x22",
    "count": 2
  }
]

If I could somehow be able to count the number of groups or “buckets” (i.e. the number of items in the list), that should be able to get me the results I need.

Thanks again!

Hi @pirxthepilot,

Please try this query. This will provide the location, count as well as associated username and location. Hope this is closer to what you are looking for.

  getLocations(func: uid(located)) @filter(gt(val(located),1))  @normalize{
    locationUID: uid    
		countlocation: val(located)
    user:~located_in (first: 1){
      uid
      located_in{
        locationName: name
      }
      ~hasIP @filter(uid(withip)) {        
        userName:name
       }
    }
  }  

Result looks like this on my local environment.

"getLocations": [
      {
        "countlocation": 2,
        "locationName": "london",
        "locationUID": "0xfffd8d67d84f1b23",
        "uid": "0xfffd8d67d84f1b24",
        "userName": "user2"
      },
      {
        "countlocation": 2,
        "locationName": "paris",
        "locationUID": "0xfffd8d67d84f1b32",
        "uid": "0xfffd8d67d84f1b31",
        "userName": "user4"
      }
    ]

Thanks and regards.
Anand.