# AQL: Querying TinySocial AsterixDB

## Query 0-A - Exact-Match Lookup

        use dataverse TinySocial;

        for $user in dataset FacebookUsers
        where $user.id = 8
        return $user;

            var expression0a = new FLWOGRExpression()
                .ForClause("$user", new AExpression("dataset FacebookUsers"))
                .WhereClause(new AExpression("$user.id = 8"))
                .ReturnClause("$user");

## Query 0-B - Range Scan

        use dataverse TinySocial;

        for $user in dataset FacebookUsers
        where $user.id >= 2 and $user.id <= 4
        return $user;

            var expression0b = new FLWOGRExpression()
                .ForClause("$user", new AExpression("dataset FacebookUsers"))
                .WhereClause().and(new AExpression("$user.id >= 2"), new AExpression("$user.id <= 4"))
                .ReturnClause("$user");

## Query 1 - Other Query Filters

        use dataverse TinySocial;

        for $user in dataset FacebookUsers
        where $user.user-since >= datetime('2010-07-22T00:00:00')
        and $user.user-since <= datetime('2012-07-29T23:59:59')
        return $user;

            var expression1 = new FLWOGRExpression()
                .ForClause("$user", new AExpression("dataset FacebookUsers"))
                .WhereClause().and(
                    new AExpression("$user.user-since >= datetime('2010-07-22T00:00:00')"),
                    new AExpression("$user.user-since <= datetime('2012-07-29T23:59:59')")
                )
                .ReturnClause("$user");

## Query 2-A - Equijoin

        use dataverse TinySocial;

        for $user in dataset FacebookUsers
        for $message in dataset FacebookMessages
        where $message.author-id = $user.id
        return {
            "uname": $user.name,
            "message": $message.message
        };

            var expression2a = new FLWOGRExpression()
                .ForClause ("$user", new AExpression("dataset FacebookUsers"))
                .ForClause ("$message", new AExpression("dataset FacebookMessages"))
                .WhereClause(new AExpression("$message.author-id = $user.id"))
                .ReturnClause({
                    "uname" : "$user.name",
                    "message" : "$message.message"
                });

## Query 2-B - Index join

        use dataverse TinySocial;

        for $user in dataset FacebookUsers
        for $message in dataset FacebookMessages
        where $message.author-id /*+ indexnl */  = $user.id
        return {
            "uname": $user.name,
            "message": $message.message
        };

            var expression2b = new FLWOGRExpression()
                .ForClause ("$user", new AExpression("dataset FacebookUsers"))
                .ForClause ("$message", new AExpression("dataset FacebookMessages"))
                .WhereClause(new AExpression("$message.author-id /*+ indexnl */  = $user.id"))
                .ReturnClause({
                        "uname" : "$user.name",
                        "message" : "$message.message"
                });

## Query 3 - Nested Outer Join

        use dataverse TinySocial;

        for $user in dataset FacebookUsers
        return {
            "uname": $user.name,
            "messages": for $message in dataset FacebookMessages
                        where $message.author-id = $user.id
                        return $message.message
        };

            var expression3messages = new FLWOGRExpression()
                .ForClause("$message", new AExpression("dataset FacebookMessages"))
                .WhereClause(new AExpression("$message.author-id = $user.id"))
                .ReturnClause("$message.message");

            var expression3 = new FLWOGRExpression()
                .ForClause ("$user", new AExpression("dataset FacebookUsers"))
                .ReturnClause({
                    "uname": "$user.name",
                    "messages" : expression3messages
                });

## Query 4 - Theta Join

        use dataverse TinySocial;

        for $t in dataset TweetMessages
        return {
            "message": $t.message-text,
            "nearby-messages": for $t2 in dataset TweetMessages
                               where spatial-distance($t.sender-location, $t2.sender-location) <= 1
                               return { "msgtxt":$t2.message-text}
        };

            var expression4messages = new FLWOGRExpression()
                .ForClause( "$t2", new AExpression("dataset TweetMessages"))
                .WhereClause( new AExpression("spatial-distance($t.sender-location, $t2.sender-location) <= 1"))
                .ReturnClause({ "msgtxt" : "$t2.message-text" });

            var expression4 = new FLWOGRExpression()
                .ForClause( "$t", new AExpression("dataset TweetMessages"))
                .ReturnClause({
                    "message" : "$t.message-text",
                    "nearby-messages" : expression4messages
                });

## Query 5 - Fuzzy Join

        use dataverse TinySocial;

        set simfunction "edit-distance";
        set simthreshold "3";

        for $fbu in dataset FacebookUsers
        return {
            "id": $fbu.id,
            "name": $fbu.name,
            "similar-users": for $t in dataset TweetMessages
                             let $tu := $t.user
                             where $tu.name ~= $fbu.name
                            return {
                                "twitter-screenname": $tu.screen-name,
                                "twitter-name": $tu.name
                            }
        };

            var similarUsersExpression = new FLWOGRExpression()
                .ForClause("$t", new AExpression("dataset TweetMessages"))
                .LetClause ("$tu", new AExpression("$t.user"))
                .WhereClause(new AExpression("$tu.name ~= $fbu.name"))
                .ReturnClause({
                    "twitter-screenname": "$tu.screen-name",
                    "twitter-name": "$tu.name"
                });

            var expression5 = new FLWOGRExpression()
                .ForClause ("$fbu", new AExpression("dataset FacebookUsers"))
                .ReturnClause(
                    {
                        "id" : "$fbu.id",
                        "name" : "$fbu.name",
                        "similar-users" : similarUsersExpression
                    }
                );

## Query 6 - Existential Quantification

        use dataverse TinySocial;

        for $fbu in dataset FacebookUsers
        where (some $e in $fbu.employment satisfies is-null($e.end-date))
        return $fbu;

            var expression6 = new FLWOGRExpression()
                .ForClause ("$fbu", new AQLClause().set("dataset FacebookUsers"))
                .WhereClause(
                    new QuantifiedExpression (
                        "some" ,
                        {"$e" : new AExpression("$fbu.employment") },
                        new FunctionExpression("is-null", new AExpression("$e.end-date"))
                    )
                )
                .ReturnClause("$fbu");

## Query 7 - Universal Quantification

        use dataverse TinySocial;

        for $fbu in dataset FacebookUsers
        where (every $e in $fbu.employment satisfies not(is-null($e.end-date)))
        return $fbu;

            var expression7 = new FLWOGRExpression()
                .ForClause("$fbu", new AExpression("dataset FacebookUsers"))
                .WhereClause(
                    new QuantifiedExpression (
                        "every" ,
                        {"$e" : new AExpression("$fbu.employment") },
                        new FunctionExpression("not", new FunctionExpression("is-null", new AExpression("$e.end-date")))
                    )
                )
                .ReturnClause("$fbu");

## Query 8 - Simple Aggregation

        use dataverse TinySocial;

        count(for $fbu in dataset FacebookUsers return $fbu);

            var expression8 = new FunctionExpression(
                "count",
                new FLWOGRExpression()
                    .ForClause("$fbu", new AExpression("dataset FacebookUsers"))
                    .ReturnClause("$fbu")
            );

## Query 9-A - Grouping and Aggregation

        use dataverse TinySocial;

        for $t in dataset TweetMessages
        group by $uid := $t.user.screen-name with $t
        return {
            "user": $uid,
            "count": count($t)
        };

            var expression9a = new FLWOGRExpression()
                .ForClause("$t", new AExpression("dataset TweetMessages"))
                .GroupClause("$uid", new AExpression("$t.user.screen-name"), "with", "$t")
                .ReturnClause(
                    {
                        "user" : "$uid",
                        "count" : new FunctionExpression("count", new AExpression("$t"))
                    }
                );

## Query 9-B - (Hash-Based) Grouping and Aggregation

        use dataverse TinySocial;

        for $t in dataset TweetMessages
        /*+ hash*/
        group by $uid := $t.user.screen-name with $t
        return {
            "user": $uid,
            "count": count($t)
        };

            var expression9b = new FLWOGRExpression()
                .ForClause("$t", new AExpression("dataset TweetMessages"))
                .AQLClause("/*+ hash*/")
                .GroupClause("$uid", new AExpression("$t.user.screen-name"), "with", "$t")
                .ReturnClause(
                    {
                        "user" : "$uid",
                        "count" : new FunctionExpression("count", new AExpression("$t"))
                    }
                );

## Query 10 - Grouping and Limits

        use dataverse TinySocial;

        for $t in dataset TweetMessages
        group by $uid := $t.user.screen-name with $t
        let $c := count($t)
        order by $c desc
        limit 3
        return {
            "user": $uid,
            "count": $c
        };

            var expression10 = new FLWOGRExpression()
                .ForClause("$t", new AExpression("dataset TweetMessages"))
                .GroupClause("$uid", new AExpression("$t.user.screen-name"), "with", "$t")
                .LetClause("$c", new FunctionExpression("count", new AExpression("$t")))
                .OrderbyClause( new AExpression("$c"), "desc" )
                .LimitClause(new AExpression("3"))
                .ReturnClause(
                    {
                        "user" : "$uid",
                        "count" : "$c"
                    }
                );

## Query 11 - Left Outer Fuzzy Join

        use dataverse TinySocial;

        set simfunction "jaccard";
        set simthreshold "0.3";

        for $t in dataset TweetMessages
        return {
            "tweet": $t,
            "similar-tweets": for $t2 in dataset TweetMessages
            where  $t2.referred-topics ~= $t.referred-topics
            and $t2.tweetid != $t.tweetid
            return $t2.referred-topics
        };

        var expression11 = new FLWOGRExpression()
            .ForClause( "$t", new AExpression("dataset TweetMessages"))
            .ReturnClause({
                "tweet"         : new AExpression("$t"),
                "similar-tweets": new FLWOGRExpression()
                                    .ForClause( "$t2", new AExpression("dataset TweetMessages"))
                                    .WhereClause().and(
                                        new AExpression("$t2.referred-topics ~= $t.referred-topics"),
                                        new AExpression("$t2.tweetid != $t.tweetid")
                                     )
                                    .ReturnClause("$t2.referred-topics")
            });