Eager loading relations in Plugins Element Type

Question

I am creating an ecommerce plugin in Craft, and trying to take advantage of the ElementType. My goal is to list the orders, and also the items ordered in a single column. Is it possible to eager load Element Type's relations for the ElementIndex view?

I have two records: OrderRecord and ItemRecord, with relations defined like this:

// OrderRecord (the ElementType)
    public function defineRelations(){
      return array(
                'element' => array(static::BELONGS_TO, 'ElementRecord', 'id', 'required' => true, 'onDelete' => static::CASCADE),
                'items'   => array(static::HAS_MANY, 'Shopping_ItemRecord', 'orderId', 'onDelete' => static::CASCADE))
    }

// ItemRecord
public function defineRelations(){
  return array(
    'order' => array(static::BELONGS_TO, 'Shopping_OrderRecord', 'required' => true, 'onDelete' => static::CASCADE)
  )
}

Loading the records from a service using OrderRecord::model()->with('items')->find() works really well, but i am struggling with translating this to the ElementType (if it's even possible, i don't know). I tried to using the OrderElementType::modifyElementsQuery() function to add the item columns, but there are a few problems:

1. The query is not allowing me to specify a different order-by, resulting in MySQL ambigous column error. The query is only containing "order by ´id´" without any alias name, which makes this query fail: select order.customerName, items.*

2. Joining in the items table, and grouping by anything else than the default ´elements´.´id´ results in duplicate rows in the view. This is my biggest concern about this question, how can i translate several rows from the result to a single OrderModel?

My current solution to this is to query the items from the OrderModel::populateModel($values), but this feels very slow and inefficient, i would like to avoid calling 50 extra queries every time the getMoreElements() is called

Thanks

Answer 1

Ok, so i managed to solve my problem without actually fetching any extra records, but instead using mysql group_concat function for the fields i need to display. Posting this here for anyone else that might need it.

In OrderElementType:

public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria){

    $query
      ->addSelect('group_concat(items.productName separator \';\') as productnames')
      ->join('orders orders', 'orders.id = elements.id')
      ->leftJoin('items items', 'orders.id = items.orderId'
      ->group('elements.id');
}

public function populateElementModel($row){
  $model = OrderModel::populateModel($row);
  $products = explode(';', $row['productnames']);
  foreach($products as $product){
    $item = new ItemModel();
    $item->productName = $product;
    if(!$model->items){
      $model->setAttribute('items', array());
    }
    $model->setAttribute('items', array_merge($model->items, array($item)));

  }
  return $model;
}