Self join sorting

[Michael Roterman]

Hello,

Run into an AQL problem. I have two collections. One that contains users and another with user locations.

What I want to do is return users based on distance from the current location. 

The problem I have run into is that some users won't have a location document but I still want to include them.

To solve this I did a self join but I can't figure out how I would sort by distance on the result. The users without a location would come last.

FOR u IN users

RETURN MERGE( u, {"location":(

        FOR l IN NEAR(userLocations, 59.371317762912, 18.162492521009, NULL, "distance")

    FILTER u._key == l.userId

            RETURN l

)})

Thanks!

[Frank Celler]

Will the following work for you:

let u1 = (
    FOR l IN near(locations, 59, 18, null, "distance")
        FOR u IN users
            FILTER u._key == l.userId
            RETURN merge(u, {"location": l}))
let u2 = (
    FOR u IN u1
        RETURN u._key)
let u3 = (
    FOR u IN users
        FILTER ! (u._key in u2)
        RETURN u)
FOR u IN union(u1, u3)
    RETURN u

First get the users near the location sorted by distance and then add everything else.

[Michael Roterman]

Hello Frank,

Sorry for the late reply. Yes that query works perfectly.

Thanks once again for your help!

Michael

[Michael Roterman]

Hello again Frank,

I have another query problem that I am trying to solve in the most efficient manner. I basically want to implement a feature so that a user can block another user. This means that when I do a user query or a content query I will need to filter out users that have been blocked or content created by the users being blocked.

I thought of just creating a collection where I store which users have blocked by other users. I would then do a similar multi query that you have done above where I first get all the blocked user id's and then in the second query filter out any content or users created by those user id's from the first query.

Would this be a good way of solving this problem or would creating an edge collection between users be a better way to handle these relationships? As ArangoDB enables so many more ways to store data I want to make sure I am utilizing the best structure/performance Arango can offer.

Regards,

Michael

[Lucas Dohmen]

Hello Michael,

Due to the usage of NEAR you now have the distance stored in the attribute `distance` in each of your Is (/ userLocations). Therefore you can just use SORT on the `distance` attribute. But I think I'm not totally getting your AQL query:

So (59.371317762912, 18.162492521009) is a location, and for each of your users you have a number of locations. You now want to get a list of all users with their locations that are sorted by being near (59.371317762912, 18.162492521009)? If I understood your description correctly though, you want exactly one location per user – the one that is nearest to that point, right? So that would work as follows:

FOR userLocation IN NEAR(userLocations, 59.371317762912, 18.162492521009, NULL, "distance")
  FILTER u._key == userLocation.userId

  SORT userLocation.distance DESC

  LIMIT 1

  RETURN userLocation

Now you have the nearest userLocation for each of your users, right? If the user doesn't have a userLocation, it will be an empty array. If it has one or more, it will be an array with exactly one element.

Best Wishes

Lucas

[Michael Roterman]

Hello Lucas,

Thank you for your reply! The problem with my query was that I wanted to also include users that didn't have a location in userLocations. Frank solved the problem for me above :) 

If you have time you might have som valuable input on another issue I am trying to solve efficiently with ArangoDB? In my latest reply above to Frank I describe the blocking system I am building.

Regards,

Michael

[Lucas Dohmen]

Hello Michael,

Can you point me to the other issue? I can’t find it.

Best Wishes

Lucas

--
You received this message because you are subscribed to the Google Groups "ArangoDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to arangodb+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Michael Roterman]

Hello Lucas,

Here is the link: https://groups.google.com/forum/#!msg/arangodb/MKGOCxx3P00/D0O5ZFpoRHMJ

Thanks!

Michael

[Lucas Dohmen]

Hello Michael,

Sorry for the confusion. Sometimes GoogleGroups tricks me :(

It probably becomes apparent how much I love graphs, but I would again solve this with the graph capabilities of ArangoDB. If you use a directed 'block' edge between those users, you can use this in your queries. Whenever you get a list of posts, you get the user that wrote the post. Then you filter out all of the users that are neighbors via a 'block' of your current user.

Best Wishes

Lucas

[Michael Roterman]

Hello Lucas,

No worries. I am new to graphs and see a lot of possibilities. Just takes a while to get around thinking in graph terms when I am modeling my data. 

What makes this case very good for using edges I think is that I can choose which direction I want to filter as sometimes I might want to filter out both directions as it's not relevant who blocked who just the fact that a person is blocked. I guess I should call this collection something like "userRelationships" as I could in theory in the future use this for friend, follow, etc.?

How would I construct an AQL query if I wanted to filter out posts created by users blocked in any direction?

Really appreciate your help on this!!

Thanks!

Michael

[Lucas Dohmen]

Hello Michael,

I think that’s a really good idea to call it userRelationships and make blocking just one of the possibilities :)

If your graph is called ‘my_graph’, the following snippet would give you all the users you have blocked:

FOR blocked_user

IN GRAPH_NEIGHBORS(‘my_graph’, ID_OF_THE_USER, { edgeExamples: [{ blocked: true }], edgeCollectionRestriction: [ ‘userRelationships’ ] }

RETURN blocked_user

This will give you the blocked users for the user with the ID “ID_OF_THE_USER”. You can use LET to put that into a list. When you run a query for posts, you can get the user for each of the posts and then use a FILTER statement to check if the user is IN blocked_users for that user.

Find information about GRAPH_NEIGHBORS here: https://docs.arangodb.com/Aql/GraphOperations.html

Find information about FILTER here: https://docs.arangodb.com/Aql/Operations.html

Find information about IN here: https://docs.arangodb.com/Aql/Operators.html

You’re welcome :)

Best Wishes

Lucas

[Michael Roterman]

Hello Lucas,

Cool! I'll get started with this ASAP. I assume you mean NOT IN when I want to filter out content by those users? Coming from the SQL world IN queries are never that good from a performance perspective. Not sure if this is true with Arango?

As I am not familiar with the inner workings of the graph functions I can imagine that from a performance perspective the attributes in the edgeExamples part should be indexed?

Thanks once again!

Michael

[Lucas Dohmen]

Hello Michael,

Yes, I meant ‘NOT IN’ :) I think you should try it and measure it :) I’m not aware of any performance problems with IN. If you find problems, please ping us :)

Yes, I would add indexes on the attributes of the edges that you want to use in the edgeExample :)

Best Wishes

Lucas