Simon Fell > Its just code > SOQL-R

API 8.0 (aka the Winter '07 release) is live everywhere, which means everyone can now benefit from probably the biggest single API release out of Salesforce.com, there's a ton of new features and functionality in there, where to start? well, probably the most popular new feature will be SOQL-R (aka SOQL Relationships aka SOQL Joins), this allows you to navigate your data model and query other objects related to the primary one, here's a simple example, that I actually used in the latest build of SF³

Select id, firstname, lastname, account.name from contact

So, in addition to querying the id, firstname and lastname values from the contact, it'll also follow the account relationship and fetch the account's name field. You can also go the other way and use the reverse relationship, e.g.

select id, name, (select firstname, lastname from contacts) from account

This time, we query id and name from account, and for each account we query the firstname and lastname associated with the account. Unlike a SQL join, where this data would be flatted into a single table, this actually returns a real hierarchical structure. You can also filter inside those aggregate queries, e.g.

select id, name, (select firstname, lastname from contacts where lastname like 'a%') from account

You can now also order and/or limit the size of the results, e.g.

select id, name, (select firstname, lastname from contacts) from account order by name
select id, name, (select firstname, lastname from contacts) from account order by name limit 5
select id, name, (select firstname, lastname from contacts order by lastname desc) from account order by name limit 50

I've barely scratch the surface, go check out the docs for info on all the extra features for SOQL, next up I'll talk about some new tricks for the upsert call.