How to create a query to group alternate language pages?

Question

I'm trying to create a sitemap for a site with multiple locales. An example of the format is described here, but as a quick summary: I need to output locales of related translated pages aside one another.

<url>
  <loc>http://www.example.com/english/</loc>
  <xhtml:link
     rel="alternate"
     hreflang="de"
     href="http://www.example.com/deutsch/"
     />
  <xhtml:link 
     rel="alternate"
     hreflang="de-ch"
     href="http://www.example.com/schweiz-deutsch/"
     />
  <xhtml:link 
     rel="alternate"
     hreflang="en"
     href="http://www.example.com/english/"
     />
</url>

To output a list of all entries like this I believe I need to query: All entries, from all locales, grouped by elementId.

I've tried to do this using $criteria->find() but I seem to run into two problems.

First, there appears to be no way to query all locales. The best way I've found is to loop through all the locales. I'm wondering if I'm overlooking something. Right now I have:

foreach (craft()->i18n->getSiteLocales() as $locale)
{
  ...
}

A potential problem with doing it this way is that I won't be able to group the results of all locales by elementId during the query.

Second, once I have all the entries, I need to make sure they are grouped by elementId. This does not happen by default. The entry for each locale just gets the next available ID when it's created. It seems the way to do this would be to use $criteria->order = 'elementId'; however, it doesn't appear Craft supports ordering a result set by elementId.

The ideal query would look something like this:

$criteria            = craft()->elements->getCriteria(ElementType::Entry);
$criteria->limit     = null;
$criteria->status    = 'live';
$criteria->sectionId = $sectionId;

// Problem #1: I want to return all locales like you can return all entries using null
$criteria->locale    = null; 

// Problem #2: I want to group my results by Element ID
$criteria->order     = 'elementId';

$entries = $criteria->find();

Any ideas on what the best approach would be here? Is there any crafty way of doing this or will I have to resort to using a custom query?

Answer 1

This query cannot be created in the "ideal" way described in the question using craft()->elements->getCriteria. It required several steps to build the results needed. The final solution was a bit specific to our needs. Here is some pseudocode that represents a solution:

$urls = array();

// Fetch all enabled locales
foreach (craft()->i18n->getSiteLocales() as $locale)
{
    // Confirm there are elements in a specific locale and get them
    ...
    $criteria->locale = $locale->id;
    $elements = $criteria->find();

    foreach ($elements as $element)
    {
        // Add each location indexed by its id
        $urls[$element->id][] = array(...);
    }
}

// Process what we created above and adjust it to fit our needs
$urls = $this->processUrlsIntoTheRightFormat($urls);