mca blog

when the JOIN keyword was introduced to SDS back in october 1008, i found it hard to come up w/ clear uses for the feature. not that i didn't need a JOIN when building queries for my SDS data stores; i did. the reason i struggled is that SDS' version of JOIN is not quite like T-SQL's JOIN.

the SDS JOIN does not support projection (basically the ability to select fields for display from either table/collection in the JOIN). instead, you simply indicate which collection you wish SDS servers to return.

at first, i was kinda stumped. but this week, while working on an SDS-backed project, i finally came up w/ a clear (and proper) use of SDS' JOIN keyword.

here's my example:

as users add messages to the system, log each message (e.Kind=="message"). then, parse the message into words (e.Kind=="word") and store that word w/ a pointer to the "message" record. finally, as each word is stored, check to see if it is unique. if yes, add the word to the index (e.Kind=="index").

now we have three collections: 1) messages, 20 words, and 3) indexes. so i could creat a list of unique words by listing the members of the index collection. then i wanted to be able to use any member of the index collection to find all the messages that contained the word in the selected index entity. yeah, a JOIN would do it:

/* SDS JOIN query example*/ 
/* where {@index}== any word in the index */
from words in entities
where words["text"]=="{@index}" 
from messages in entities
where message.Id==words["message-id"] 
orderby message["fulltext"] ascending
select message

for example (starting with a clean/empty data collection):

1. add "The Red Shoes" (M1) to the messages collection
2. this results in the following items added to the words collection (with links to "The Red Shoes" message): the, red, shoes
3. which results in the following items added to the index collection: the, red, shoes
4. add "Little Red Riding Hood" (M2) to the messages collection
5. this results in the following items added to the words collection (with links to "Little Red Riding Hood" message): little, red, riding, hood
6. which results in the following items added to the index collection: little, riding, hood (notice that red was already in the index and was not added)
7. add "Little Women" (M3) to the messages collection
8. this results in the following items added to the words collection (with links to "Little Women" message): little, women
9. which results in the following items added to the index collection: women (notice that little was already in the index and was not added)

now running the JOIN query using red as the index word returns records M1 and M2. using little returns M2 and M3. and so forth.

all happily working nicely using the SDS JOIN keyword.