Querying¶

Querying your Drowsy based API can be done via GET requests and supports a wide range of filtering, nested embedding, and pagination options.

The examples below will walk you through some query basics and advanced features using the API described in Quickstart. If you want to follow along, run the example chinook_api app and try these same GET requests yourself.

Filtering by Unique Identifier¶

Access individual resources using their primary key value (or setting a custom field to use as an ID on the ModelResource object):

GET /api/albums/2 HTTP/1.1

HTTP/1.1 200 OK

{
    "album_id": 2,
    "artist": "/albums/2/artist",
    "self": "/albums/2",
    "title": "Balls to the Wall",
    "tracks": "/albums/2/tracks"
}

Collection Filtering¶

By default, any field or nested resource field that isn’t load_only can be queried. This can be turned on or off on a field by field basis if desired.

Query for things that are >, >=, =<, <, != by appending -gt, -gte, -lt, -lte, -ne respectively to the parameter name.

GET /api/albums?album_id-lte=10&album_id-gt=8 HTTP/1.1

HTTP/1.1 200 OK

[
    {
        "album_id": 9,
        "artist":  "/albums/9/artist",
        "self": "/albums/9",
        "title": "Plays Metallica By Four Cellos",
        "tracks": "/albums/9/tracks"
    },
    {
        "album_id": 10,
        "artist":  "/albums/10/artist",
        "self": "/albums/10",
        "title": "Audioslave",
        "tracks": "/albums/10/tracks"
    }
]

Query text fields for partial matches using -like.

GET /api/albums?title-like=salute HTTP/1.1

HTTP/1.1 200 OK

[
    {
        "album_id": 1,
        "artist": {
            "self": "/artists/1"
        },
        "self": "/albums/1",
        "title": "For Those About To Rock We Salute You",
        "tracks": "/albums/1/tracks"
    }
]

Advanced Filtering¶

Query using complex MQLAlchemy style filters:

GET /api/tracks?query={"$and":[{"unit_price":{"$lte":1}},{"album.album_id":2}]} HTTP/1.1

HTTP/1.1 200 OK

[
    {
        "album": {
            "self": "/albums/2"
        },
        "bytes": 5510424,
        "composer": null,
        "genre": {
            "self": "/genres/1"
        },
        "media_type": {
            "self": "/mediaTypes/2"
        },
        "milliseconds": 342562,
        "name": "Balls to the Wall",
        "playlists": "/tracks/2/playlists",
        "self": "/tracks/2",
        "track_id": 2,
        "unit_price": 0.99
    }
]

Embedding Relationships and Fields¶

Embed full relationships or fields of relationships by specifying embeds as a query string parameter:

GET /api/albums/2?embeds=artist,tracks.name&limit=1 HTTP/1.1

HTTP/1.1 200 OK

{
    "album_id": 2,
    "artist": {
        "artistId": 2,
        "name": "Accept",
        "self": "/artists/2"
    },
    "self": "/albums/2",
    "title": "Balls to the Wall",
    "tracks": [
        {
            "name": "Balls to the Wall"
        }
    ]
}

Choose fields you want returned explicitly:

GET /api/albums/2?fields=title,album_id HTTP/1.1

HTTP/1.1 200 OK

{
    "album_id": 2,
    "title": "Balls to the Wall"
}

Offset, Limit, and Pagination¶

Use limit for any end point:

GET /api/albums?limit=2 HTTP/1.1

HTTP/1.1 200 OK

[
    {
        "album_id": 1,
        "artist": "/albums/1/artist",
        "self": "/albums/1",
        "title": "For Those About To Rock We Salute You",
        "tracks": "/albums/1/tracks"
    },
    {
        "album_id": 2,
        "artist": "/albums/2/artist",
        "self": "/albums/2",
        "title": "Balls to the Wall",
        "tracks": "/albums/2/tracks"
    }
]

Use offset for any end point:

GET /api/albums?limit=1&offset=1 HTTP/1.1

HTTP/1.1 200 OK

[
    {
        "album_id": 2,
        "artist": "/albums/2/artist",
        "self": "/albums/2",
        "title": "Balls to the Wall",
        "tracks": "/albums/2/tracks"
    }
]

Paginate any end point (limit can be used to set page size):

GET /api/albums?page=2limit=5 HTTP/1.1

HTTP/1.1 200 OK

[
     {
        "album_id": 6,
        "artist": "/albums/6/artist",
        "self": "/albums/6",
        "title": "Jagged Little Pill",
        "tracks": "/albums/6/tracks"
    },
    {
        "album_id": 7,
        "artist":  "/albums/7/artist",
        "self": "/albums/7",
        "title": "Facelift",
        "tracks": "/albums/7/tracks"
    },
    {
        "album_id": 8,
        "artist":  "/albums/8/artist",
        "self": "/albums/8",
        "title": "Warner 25 Anos",
        "tracks": "/albums/8/tracks"
    },
    {
        "album_id": 9,
        "artist":  "/albums/9/artist",
        "self": "/albums/9",
        "title": "Plays Metallica By Four Cellos",
        "tracks": "/albums/9/tracks"
    },
    {
        "album_id": 10,
        "artist":  "/albums/10/artist",
        "self": "/albums/10",
        "title": "Audioslave",
        "tracks": "/albums/10/tracks"
    }
]

Convert Fields to camelCase¶

Schemas can easily be defined to serialize and deserialize using lower camelCase field names to be more JavaScript convention friendly.

class AlbumSchema(ModelResourceSchema):
    class Meta:
        model = Album
        converter = CamelModelResourceConverter
        include_relationships = True

GET /api/albums/2 HTTP/1.1

HTTP/1.1 200 OK

{
    "albumId": 2,
    "artist": "/albums/2/artist",
    "self": "/albums/2",
    "title": "Balls to the Wall",
    "tracks": "/albums/2/tracks"
}

Note that the album_id field here has been converted to albumId.

Nested Queries¶

One of the more powerful things Drowsy allows is to query nested relationships of objects. This can take a few different forms, the first of which involves filtering top level objects based on whether their relationships meet a particular example:

GET /api/albums?tracks.track_id=1 HTTP/1.1

HTTP/1.1 200 OK

[
    {
        "album_id": 1,
        "artist": "/albums/1/artist",
        "self": "/albums/1",
        "title": "For Those About To Rock We Salute You",
        "tracks": "/albums/1/tracks"
    }
]

Here we’re looking for all albums that contain an object in tracks that has a track_id of 1. Seeing as a track can only ever be in one album, a more realistic query much be something like /api/albums?tracks.genre.name=Rock, which would return all albums that contain a track that has a genre of Rock.

The other way you query nested resources is by filtering the results of your embeds. For example, perhaps you want to retrieve an album, embed it’s tracks, and only include the tracks with a genre of Rock:

GET /api/albums/112?tracks._subquery_.genre.name=Rock HTTP/1.1

HTTP/1.1 200 OK

{
    "self": "/albums/112",
    "artist": "/albums/112/artist",
    "tracks": [
        {
            "composer": "Steve Harris",
            "unit_price": 0.99,
            "invoice_lines": "/tracks/1393/invoice_lines",
            "media_type": "/tracks/1393/media_type",
            "self": "/tracks/1393",
            "bytes": 11737216,
            "playlists": "/tracks/1393/playlists",
            "album": "/tracks/1393/album",
            "track_id": 1393,
            "name": "The Number Of The Beast",
            "milliseconds": 293407,
            "genre": "/tracks/1393/genre"
        }
    ],
    "album_id": 112,
    "title": "The Number of The Beast"
}

This same album contains 9 other tracks (all classified as Metal), but in our result we get the lone Rock track on the album. Note the use of _subquery_ in the filter can be overridden with some other syntax if you prefer by providing an alternative to parse_subfilters().

Along with _subquery_, you can also specify a _limit_ and/or _offset_, and optionally _sorts_ to essentially paginate the nested objects. By default, the nested objects will be sorted by their identifying data key(s), so if you want the first two tracks of an Album you can try a query like /api/albums/112?tracks._limit_=2. If you want the next two, you can use /api/albums/112?tracks._limit_=2&tracks._offset_=2.

Tying all of this together, the below is essentially the second page of Metal tracks (2 per page) on a particular album, sorted by name descending (note the - in front of the provided sort).

GET /api/albums/112?tracks._subquery_.genre.name=Metal&tracks._limit_=2&tracks._offset_=2&tracks._sorts_=-name HTTP/1.1

HTTP/1.1 200 OK

{
    "self": "/albums/112",
    "title": "The Number of The Beast",
    "tracks": [
        {
            "self": "/tracks/1391",
            "track_id": 1391,
            "media_type": "/tracks/1391/media_type",
            "bytes": 2849181,
            "genre": "/tracks/1391/genre",
            "composer": "Steve Harris",
            "name": "Invaders",
            "playlists": "/tracks/1391/playlists",
            "milliseconds": 203180,
            "invoice_lines": "/tracks/1391/invoice_lines",
            "unit_price": 0.99,
            "album": "/tracks/1391/album"
        },
        {
            "self": "/tracks/1390",
            "track_id": 1390,
            "media_type": "/tracks/1390/media_type",
            "bytes": 6006107,
            "genre": "/tracks/1390/genre",
            "composer": "Steve Harris",
            "name": "Hallowed Be Thy Name",
            "playlists": "/tracks/1390/playlists",
            "milliseconds": 428669,
            "invoice_lines": "/tracks/1390/invoice_lines",
            "unit_price": 0.99,
            "album": "/tracks/1390/album"
        }
    ],
    "artist": "/albums/112/artist",
    "album_id": 112
}

For reference, the _sorts_ parameter can also be a comma separated list if you want to provide multiple sort criteria.

Lastly, the simplest way to access nested objects is to access them as their own collection:

GET /api/albums/264/tracks HTTP/1.1

HTTP/1.1 200 OK

[
    {
        "self": "/tracks/3352",
        "track_id": 3352,
        "media_type": "/tracks/3352/media_type",
        "bytes": 5327463,
        "genre": "/tracks/3352/genre",
        "composer": "Karsh Kale/Vishal Vaid",
        "name": "Distance",
        "playlists": "/tracks/3352/playlists",
        "milliseconds": 327122,
        "invoice_lines": "/tracks/3352/invoice_lines",
        "unit_price": 0.99,
        "album": "/tracks/3352/album"
    },
    {
        "self": "/tracks/3358",
        "track_id": 3358,
        "media_type": "/tracks/3358/media_type",
        "bytes": 6034098,
        "genre": "/tracks/3358/genre",
        "composer": "Karsh Kale",
        "name": "One Step Beyond",
        "playlists": "/tracks/3358/playlists",
        "milliseconds": 366085,
        "invoice_lines": "/tracks/3358/invoice_lines",
        "unit_price": 0.99,
        "album": "/tracks/3358/album"
    }
]

You can also supply filters, sorts, pagination, or any other parameters to such a request the same way you would any other query.

Limitations¶

While Drowsy is incredibly flexible in how much it will let you do in one single query, there are a few limitations to note:

Attempting to embed (or subquery) the same relationship multiple times in the same query will result in an error. This is something intended to be worked around in the future, but given the way SQLAlchemy’s contains_eager relationship loading technique works, it’ll require a significant change to how Drowsy handles embedding.
The MQLAlchemy parser used by Drowsy is an iterative process, and has a default limit on how complex of a query it will attempt to parse (intended to prevent malicious attempts to overload a server). If you find that you’re hitting this limitation in a real world use case, let us know by filing an issue on GitHub.

More Examples¶

The included test suite, in particular the test_query_builder.py file contains more in depth examples that may be useful to look through.

Drowsy

Navigation

Related Topics