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

<p>In OrderElementType:</p>

<pre><code>public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria){

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

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

  }
  return $model;
}
</code></pre>

CC BY-SA 3.0
2015-05-06T10:38:07.187Z
2015-05-06T11:33:49.217Z
2015-05-06T11:33:49.217Z
fuling
<p>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. <strong>Is it possible to eager load Element Type's relations for the ElementIndex view?</strong></p>

<p>I have two records: OrderRecord and ItemRecord, with relations defined like this:</p>

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

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

<p>Loading the records from a service using <code>OrderRecord::model()-&gt;with('items')-&gt;find()</code> 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 <code>OrderElementType::modifyElementsQuery()</code> function to add the item columns, but there are a few problems:</p>

<ol>
<li><p>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: <code>select order.customerName, items.*</code></p></li>
<li><p>Joining in the items table, and grouping by anything else than the default <code>´elements´.´id´</code> 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?</p></li>
</ol>

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

<p>Thanks</p>

<plugin-development><database><elementtype>
Eager loading relations in Plugins Element Type
# Eager loading relations in Plugins Element Type

(Asked by: username_0 on 2015-05-02)

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:

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.*
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

**Tags:** plugin-development, database, elementtype


---

## 1 answers

### Answer by username_0 on 2015-05-06. Score: 1 | Accepted answer

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


---

