3

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

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;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.