How can I do more complicated queries with an ElementCriteriaModel?

Question

I need to join my 3rd party table with a standard ElementCriteriaModel (ECM). I'd like to start with an ordinary ECM, so my plugin will be compatible with any element type which may exist, now or in the future.

By default, an ECM generates a query which joins multiple native tables. According to Brad in this answer...

... think of ElementCriteriaModel as an abstraction on top of DbCommand that makes it easy to search for Elements.

My plugin does not have either a custom Field Type or custom Element Type (which means I can't use modifyElementsQuery when retrieving the data).

How can I expand my ECM query to take additional SQL commands into account?

Answer 1

Once you've got your ECM, pass it off to the buildElementsQuery method...

$query = craft()->elements->buildElementsQuery($criteria);

This will convert your ECM into a DbCommand object. From there, you can make further modifications to the query... like adding a join for your 3rd party table.

$query->join('myplugintable myplugintable', 'myplugintable.id = elements.id');
$query->order('myplugintable.mysortcolumn');

return $query->queryAll();