Answers.Body: <p>I ended up doing it this way:</p>
  
  <pre><code>$records = Plugin_CollectionSectionRecord::model()
          -&gt;findAllByAttributes(['collectionId' =&gt; $collectionId]);
  }
  
  $ids = [];
  foreach($records as $value) {
      $ids[] = $value-&gt;sectionId;
  }
  
  $criteria = craft()-&gt;elements-&gt;getCriteria(ElementType::Entry);
  $criteria-&gt;elementType = 'Entry';
  $criteria-&gt;sectionId = $ids;
  $result = $criteria-&gt;all();
  return $result;
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-04-27T17:22:33.230Z
Answers.LastActivityDate: 2016-04-27T17:22:33.230Z
Answers.UserDisplayName: sboucher
Body: <p>I'm creating a plugin and I need help to query relational data.</p>
  
  <p>Here's the table schema I have:</p>
  
  <pre><code>collection
  +----+-------------+---------------------+---------------------+-----+
  | id | label       | dateCreated         | dateUpdated         | uid |
  +----+-------------+---------------------+---------------------+-----+
  |  1 | Static page | 2016-03-11 16:19:07 | 2016-03-11 16:19:07 | 0   |
  +----+-------------+---------------------+---------------------+-----+
  
  collection_section
  +----+---------------+-----------+---------------------+---------------------+-----+
  | id | collectionId  | sectionId | dateCreated         | dateUpdated         | uid |
  +----+---------------+-----------+---------------------+---------------------+-----+
  |  1 |             1 |        16 | 2016-03-11 16:19:07 | 2016-03-11 16:19:07 | 0   |
  |  2 |             1 |         9 | 2016-03-11 16:19:07 | 2016-03-11 16:19:07 | 0   |
  |  3 |             1 |        14 | 2016-03-11 16:19:07 | 2016-03-11 16:19:07 | 0   |
  |  4 |             1 |        10 | 2016-03-11 16:19:07 | 2016-03-11 16:19:07 | 0   |
  +----+---------------+-----------+---------------------+---------------------+-----+
  
  craft_content
  +------------+
  | title      |
  +------------+
  | mytitle    |
  +------------+
  </code></pre>
  
  <p>I've created 2 records:</p>
  
  <pre><code>// collectionRecord
  public function defineRelations()
  {
      return array(
          'collectionSection' =&gt; array(
              static::HAS_MANY,
              'collectionSectionRecord',
              'sectionId'
          )
      );
  }
  
  // collectionSectionRecord
  public function defineRelations()
  {
      return array(
          'collection' =&gt; array(
              static::BELONGS_TO,
              'collectionRecord',
              'required' =&gt; true,
              'onDelete' =&gt; static::CASCADE
          ),
          'section'  =&gt; array(
              static::BELONGS_TO,
              'SectionRecord',
              'required' =&gt; true,
              'onDelete' =&gt; static::CASCADE
          ),
      );
  }
  </code></pre>
  
  <p>The section ids are only singles and I need to get a listing of all the sections with their content related to the collectionId.</p>
  
  <p>I tried this:</p>
  
  <pre><code>$query = craft()-&gt;db-&gt;createCommand()
      -&gt;select('s.*')
      -&gt;from('sections s')
      -&gt;join('collection_section cs', 'cs.sectionId = s.id')
      -&gt;where(array('cs.collectionId' =&gt; 1))
      -&gt;queryAll();
  return SectionModel::populateModels($query);
  </code></pre>
  
  <p>I actually get the sections from the craft_sections table but I need the related content (title row) "mytitle" in the craft_content table.
  Is there an easy way to do that in Craft?</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-04-26T00:26:05.610Z
Comments.Text: So, do you want this done through ActiveRecord or through querybuilder? To do the latter the relationships for AR don't matter - you're building the SQL query manually through querybuilder.
Comments.UserDisplayName: Brad Bell
Tags: <relations>
Title: Querying relational data in plugin
ThreadText: # Querying relational data in plugin
  
  (Asked by: username_0 on 2016-04-25)
  
  I'm creating a plugin and I need help to query relational data.
  
  Here's the table schema I have:
  
  ```
  collection
  +----+-------------+---------------------+---------------------+-----+
  | id | label       | dateCreated         | dateUpdated         | uid |
  +----+-------------+---------------------+---------------------+-----+
  |  1 | Static page | 2016-03-11 16:19:07 | 2016-03-11 16:19:07 | 0   |
  +----+-------------+---------------------+---------------------+-----+
  
  collection_section
  +----+---------------+-----------+---------------------+---------------------+-----+
  | id | collectionId  | sectionId | dateCreated         | dateUpdated         | uid |
  +----+---------------+-----------+---------------------+---------------------+-----+
  |  1 |             1 |        16 | 2016-03-11 16:19:07 | 2016-03-11 16:19:07 | 0   |
  |  2 |             1 |         9 | 2016-03-11 16:19:07 | 2016-03-11 16:19:07 | 0   |
  |  3 |             1 |        14 | 2016-03-11 16:19:07 | 2016-03-11 16:19:07 | 0   |
  |  4 |             1 |        10 | 2016-03-11 16:19:07 | 2016-03-11 16:19:07 | 0   |
  +----+---------------+-----------+---------------------+---------------------+-----+
  
  craft_content
  +------------+
  | title      |
  +------------+
  | mytitle    |
  +------------+
  
  ```
  
  I've created 2 records:
  
  ```
  // collectionRecord
  public function defineRelations()
  {
      return array(
          'collectionSection' => array(
              static::HAS_MANY,
              'collectionSectionRecord',
              'sectionId'
          )
      );
  }
  
  // collectionSectionRecord
  public function defineRelations()
  {
      return array(
          'collection' => array(
              static::BELONGS_TO,
              'collectionRecord',
              'required' => true,
              'onDelete' => static::CASCADE
          ),
          'section'  => array(
              static::BELONGS_TO,
              'SectionRecord',
              'required' => true,
              'onDelete' => static::CASCADE
          ),
      );
  }
  
  ```
  
  The section ids are only singles and I need to get a listing of all the sections with their content related to the collectionId.
  
  I tried this:
  
  ```
  $query = craft()->db->createCommand()
      ->select('s.*')
      ->from('sections s')
      ->join('collection_section cs', 'cs.sectionId = s.id')
      ->where(array('cs.collectionId' => 1))
      ->queryAll();
  return SectionModel::populateModels($query);
  
  ```
  
  I actually get the sections from the craft\_sections table but I need the related content (title row) "mytitle" in the craft\_content table.
  Is there an easy way to do that in Craft?
  
  **Tags:** relations
  
  > **Comments:**
  > **username_1**: So, do you want this done through ActiveRecord or through querybuilder? To do the latter the relationships for AR don't matter - you're building the SQL query manually through querybuilder.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2016-04-27. Score: 3
  
  I ended up doing it this way:
  
  ```
  $records = Plugin_CollectionSectionRecord::model()
          ->findAllByAttributes(['collectionId' => $collectionId]);
  }
  
  $ids = [];
  foreach($records as $value) {
      $ids[] = $value->sectionId;
  }
  
  $criteria = craft()->elements->getCriteria(ElementType::Entry);
  $criteria->elementType = 'Entry';
  $criteria->sectionId = $ids;
  $result = $criteria->all();
  return $result;
  
  ```
  
  
  ---
  
  