Ghost Filter Query examples

examples.md

Filter Queries - Example Use Cases

Here are a few example use cases, these use cases combine filter with other parameters to make useful API queries. The syntax for any of this may change between now, implementation, and release - they're meant as illustrative examples :)

Fetch 3 posts with tags which match 'photo' or 'video' and aren't the post with id 5.

api.posts.browse({filter: "tags:[photo, video] + id:-5", limit="3"});

GET /api/posts?filter=tags%3A%5Bphoto%2Cvideo%5D%2Bid%3A-5&limit=3

{{#get "posts" filter="tags:[photo,video]+id:-5" limit="3"}}

Would become:

gql
    .findAll('posts')
    .filter({
        tags: { $in: [ 'photo', 'video' ] },
        id: { $ne: 5 }
    })
    .limit(3);

Which would become the following SQL:

SELECT posts.* 
    FROM posts
    JOIN posts_tags ON posts_tags.post_id = posts.id
    JOIN tags ON tags.id = posts_tags.tag_id
    WHERE tags.slug IN('photo', 'video')
    AND posts.id <> 5
    LIMIT 3;

Fetch posts which have either a tag of 'photo', are marked 'featured' or have an image

api.posts.browse({filter: "tag:photo,featured:true,image"})

GET /api/posts?filter=tag%3Aphoto%2Cfeatured%3Atrue%2Cimage

{{#get "posts" filter="tag:photo,featured:true,image"}}

Would become:

gql
    .findAll('posts')
    .filter({
        $or : [
            { tag: 'photo' },
            { featured: true },
            { image: { $ne: null } }
        ]
    });

Which would become the following SQL:

SELECT posts.*
    FROM posts
    JOIN posts_tags ON posts_tags.post_id = posts.id
    JOIN tags ON tags.id = posts_tags.tag_id
    WHERE tags.slug = 'photo'
        OR posts.featured = 1
        OR posts.image NOT NULL;

Fetch all tags, ordered by post count, where the post count is at least 1

api.tags.browse({filter: "post.$count:>=1", order: "posts.$count DESC", limit: "all"})

GET /api/tags?filter=post.$count:>=1&order=posts.$count%20DESC&limit=all

{{#get "tags" filter="post.$count:>=1" order="posts.$count DESC" limit="all"}}

Would become:

gql
    .findAll('tags')
    .having({
        'posts.$count': { $gte: 1 }
    })
    .orderBy({ 'posts.$count': desc });

Which would become the following SQL:

SELECT tags.*, COUNT(DISTINCT posts.id) AS post_count
  FROM tags
  JOIN posts_tags ON tags.id = posts_tags.tag_id
  JOIN posts ON posts_tags.post_id = posts.id
  GROUP BY tags.id
  HAVING post_count > 1
  ORDER BY post_count DESC;

Fetch posts by the author 'hannah' which are marked as featured

api.posts.browse({filter: "author.name:hannah+featured:true"});

GET /api/posts?filter=author.name:hannah%2Bfeatured:true

{{#get "posts" filter="author.name:hannah+featured:true"}}

Would become:

gql
    .findAll('posts')
    .filter({
        author: { name: 'hannah' },
        featured: true
    });

Fetch the 3 most prolific users who write posts with the tag 'photo' ordered by most posts

api.users.browse({filter: "posts.tags:photo", order: "posts.$count DESC", limit: 3});

GET /api/users?filter=posts.tags:photo&order=posts.$count%20DESC&limit=3

{{#get "users" filter="posts.tags:photo" order="posts.$count DESC" limit="3"}}

Would become:

gql
    .findAll('users')
    .filter({
        posts: { tags: 'photo' }
    })
    .order({ 'posts.$count': 'DESC' })
    .limit(3)

Which would become the following SQL (with some help from bookshelf to map tasks.slug via alias):

SELECT users.*, count(DISTINCT posts.id) as post_count
  FROM users
  JOIN posts ON posts.author_id=users.id
  JOIN posts_tags ON posts.id = posts_tags.post_id
  JOIN tags ON posts_tags.tag_id = tags.id
  WHERE tags.slug = 'photo'
  GROUP BY tags.id
  LIMIT 3;

Fetch 5 posts after a given date

api.posts.browse({filter: "published_at:>'2015-07-20'", limit: 5});

GET /api/posts?filter=published_at:%3E'2015-07-20'&limit=5

{{#get "posts" filter="published_at:>'2015-07-20'" limit="5"}}

Would become:

gql
    .findAll('posts')
    .filter({
        published_at: { $gt: '2015-07-20' }
    })
    .limit(5)

Which would become the following SQL:

SELECT * FROM posts WHERE published_at > '2015-07-20' LIMIT 5;

There are a three of main differences in my gist compared to the one I forked it from.

1. The count queries are aggregate queries and need to be handled separately. I've updated the syntax from posts.count to posts.$count to reflect that. Also, the gql api to handle aggregate queries uses having() instead of where. This maps pretty closely to the underlying sql.
2. The structure of the GQL is different from what was originally suggested. In order to support the type of nesting that is supported by SQL a simple array is not sufficient. Modeling the query as an object is a more powerful approach.
3. I changed the way "not null" is represented from foo:-null to just foo. The presence of the attribute name implies that the attribute should exist, and be not null. The -null syntax would require special handling to check for the string 'null'. It would also make this query impossible { foo: { $ne: 'null' } } (checking for the actual string 'null'). The syntax I chose instead is more compact and doesn't require the same special handling.

It's worth mentioning that the $gt, $ne, etc. is modeled after how mongodb models queries.