rql - Rediff Query Language

Rationale

rql is a way to query the same Sociali data you can access through the other API functions, but with a SQL-style interface. In fact, many of the normal API calls are simple wrappers for rql queries. All of the usual privacy checks are still applied. A typical query looks something like this:

SELECT name, pic FROM user WHERE uid=211031 OR uid=4801660

So, with all that said, why would you use rql? The key advantages of using rql over our more traditional API methods are as follows:

Condensed XML reduces bandwidth and parsing costs. Instead of getting all of the information available about a large set of items, you can get just the fields you want for only the set of items matching a specific condition. You can request the specific set of information by adding constraints to the WHERE clause and only listing certain fields in the SELECT clause.
More complex requests can reduce the number of requests necessary. Often the data that you are trying to get depends upon the results of a previous method call. For example, with the traditional API, to get the names of a person's friends, you first call friends.get and then pass the result directly back in to users.getInfo. Now you can just execute one rql query that uses a subquery to get the set of friends - thus reducing an extra trip back and forth, and all of the latency associated with it.
Provides a single consistent, unified interface for all of your data. Instead of having to learn numerous different methods that each have their own idiosyncrasies, you can make all of your requests with one function that has a consistent return type. Additionally, if you do need to call any of the traditional methods, the return XML is very similar, so the switching cost is negligible.
It's fun! Check out the examples available at Sociali.rql.query and then try playing around with it in the test console - you can do some cool stuff with it!

The query language

Hopefully, you're now convinced that rql may be useful to you. How do you use it? If you already know SQL, it should be pretty straightforward. Queries are of the form SELECT [fields] FROM [table] WHERE [conditions] (you can also optionally add on ORDER BY and LIMIT clauses that work like they do in MySQL). Unlike SQL, the FROM clause in rql can contain only a single table. In the SELECT or WHERE clauses you can use the IN keyword to do subqueries (as in the examples in Rediff..rql.query), but the subqueries cannot reference variables in the outer query's scope.

Another key restriction is that your query must be indexable. You cannot, for example, just specify WHERE 1 as your entire WHERE clause - in general your query must be limited to working on a specific, enumerable set of ID's (the table below shows which columns are indexable). If you do not satisfy this requirement you will get back an error code 604.

rql introduces a way of dealing with columns which are themselves structures or arrays (for example, the "education_history" column of the "user" table, which contains an array of education_info structures, or the "current_location" column of the user table which is a location structure). You can reference the structures as a whole, or you can filter them down to only a single field within the structure using dots. For example, you can do SELECT education_history, current_location to get the full structures, or you can do SELECT education_history.name, current_location.zip to get individual fields in those structures. This is particularly useful in WHERE clauses, for example, WHERE "Stanford" IN education_history.name OR current_location.zip = 07079 (note that the last example is not indexable and would also need an additional constraint about the uid).

The easiest way to learn rql is to experiment with it - we encourage you to try out your own queries in the test console. If you are unsure about what is happening in a WHERE clause, keep in mind that any legal expression in that clause is also legal in the SELECT clause, so you can add things into that clause to get some insight into how our query evaluator is working.

The data

Use the following table as a reference for constructing rql queries. The first column corresponds to strings that can be used in the FROM clause, while the second column corresponds to columns in that table that can be referenced in the SELECT and WHERE clauses. The third column lists API functions that work on similar data; their documentation pages contain additional information about the contents of the column and example rql queries. In order to make your query indexable, the WHERE should contain an = or IN clause for one of the columns marked with a *.

Table	Columns	More info
user	uid, first_name, last_name, name, pic_small, pic_big, pic_square, pic, affiliations, profile_update_time, timezone, religion, birthday, sex, hometown_location, meeting_sex, meeting_for, relationship_status, significant_other_id, political, current_location, activities, interests, is_app_user, music, tv, movies, books, quotes, about_me, hs_info, education_history, work_history, notes_count, wall_count, status, has_added_app	Sociali.users.getInfo
friend	uid1, uid2 (these are only indexable if both are specified or for the logged in user's user id)	Sociali.friends.get, Sociali.friends.areFriends
group	gid*, name, nid, pic_small, pic_big, pic, description, group_type, group_subtype, recent_news, creator, update_time, office, website, venue	Sociali.groups.get
group_member	uid, gid, positions	Sociali.groups.getMembers, Sociali.groups.get
event	eid*, name, tagline, nid, pic_small, pic_big, pic, host, description, event_type, event_subtype, start_time, end_time, creator, update_time, location, venue	Sociali.events.get
event_member	uid, eid, rsvp_status	Sociali.events.getMembers, Sociali.events.get
photo	pid, aid, owner, src_small, src_big, src, link, caption, created	Sociali.photos.get
album	aid, cover_pid, owner*, name, created, modified, description, location, size	Sociali.photos.getAlbums
photo_tag	pid, subject, text, xcoord, ycoord, created	Sociali.photos.getTags, Sociali.photos.get

Functions and Operators

In addition to the identifiers listed in the table above, rql also supports some basic math and string manipulation. You can call these functions and use these operators anywhere that you could list one of the fields in the table above. You can use boolean comparison operators like =, >=, <, etc., parenthesis for order of operations, and the arithmetic operators +, -, *, and /. The AND, OR, and NOT keywords are also supported. Finally, you can also call the following functions:

Function	Description
now()	Returns the current time.
rand()	Generates a random number.
strlen(string)	Returns the length of the string.
concat(string, ...)	Concatenates the given strings (can take any number of strings).
substr(string, start, length)	Gets a substring of the string.
strpos(haystack, needle)	Returns the position of needle in haystack, or -1 if it is not found.
lower(string)	Converts the string to lower case.
upper(string)	Converts the string to upper case.

Here's an example query that uses some of these:

SELECT concat(first_name, substr(last_name, 0, 1), " is from ",
                upper(hometown_location.city), ", yo"), status
  FROM user
  WHERE uid IN (SELECT uid2 FROM friend WHERE uid1 = [uid]
                ORDER BY rand() LIMIT 100)
        AND strlen(hometown_location.city) > 0
  ORDER BY status.time DESC LIMIT 10 OFFSET 5