Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-06T10:38:07.187Z
Answers.LastActivityDate: 2015-05-06T11:33:49.217Z
Answers.LastEditDate: 2015-05-06T11:33:49.217Z
Answers.UserDisplayName: fuling
Body: <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>
  
Tags: <plugin-development><database><elementtype>
Title: Eager loading relations in Plugins Element Type
ThreadText: # 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:
  
  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
  
  **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;
  }
  
  ```
  
  
  ---
  
  