Answers.Body: <p>Craft has a pretty solid engine for relational queries, so unless what you want to do is totally outside the box, there's a good chance you can accomplish what you want by using the <a href="https://craftcms.com/docs/relations"><code>relatedTo</code></a> parameter available to the <a href="https://craftcms.com/docs/plugins/working-with-elements">ElementCriteriaModel</a>. <em>Note that the <code>relatedTo</code> entry in the docs pertain to templates and not plugin development per se, but aside from the syntax the concept and approach will be identical in PHP.</em></p>
  
  <p>Here are some examples, since its not obvious to me exactly what you hope to achieve:</p>
  
  <p>To pull entries related to a certain category:</p>
  
  <pre><code>$criteria = craft()-&gt;elements-&gt;getCriteria(ElementType::Entry, [
      'section' =&gt; 'affiliates',
      'limit' =&gt; null,
      'relatedTo' =&gt; [
          'element' =&gt; $category, // Instance of CategoryModel, or a category ID
      ],
  ]);
  </code></pre>
  
  <p>To pull entries related to multiple categories:</p>
  
  <pre><code>...
  'relatedTo' =&gt; [
      'element' =&gt; $categories, // Array of CategoryModels or category IDs
  ],
  ...
  </code></pre>
  
  <p>To pull entries related to a category, where the relation is made in a certain Categories field:</p>
  
  <pre><code>...
  'relatedTo' =&gt; [
      'element' =&gt; $category,
      'field' =&gt; 'someCategoriesFieldHandle',
  ],
  ...
  </code></pre>
  
  <p>To pull entries related to a category, where the relation is made in a certain Categories field on the EntryModel:</p>
  
  <pre><code>...
  'relatedTo' =&gt; [
      'targetElement' =&gt; $category,
      'field' =&gt; 'someCategoriesField',
  ],
  ...
  </code></pre>
  
  <p>To pull entries related to a category, where the relation is made in a certain Entries field on the CategoryModel:</p>
  
  <pre><code>...
  'relatedTo' =&gt; [
      'sourceElement' =&gt; $category,
      'field' =&gt; 'someEntriesField',
  ],
  ...
  </code></pre>
  
  <p>To pull entries with multiple possible relations, where <em>any</em> of the relational parameters can be met:</p>
  
  <pre><code>...
  'relatedTo' =&gt; ['or', [
      'targetElement' =&gt; $categories,
      'field' =&gt; 'someCategoriesField',
  ], [
      'targetElement' =&gt; $someOtherCategories,
      'field' =&gt; 'someOtherCategoriesField',
  ]],
  ...
  </code></pre>
  
  <p>To pull entries with multiple possible relations, where <em>all</em> of the relational parameters must be met:</p>
  
  <pre><code>...
  'relatedTo' =&gt; ['and', [
      'targetElement' =&gt; $categories,
      'field' =&gt; 'someCategoriesField',
  ], [
      'targetElement' =&gt; $someOtherCategories,
      'field' =&gt; 'someOtherCategoriesField',
  ]],
  ...
  </code></pre>
  
  <p>If you edit your question to include exactly what you want to do, I might be able to give you more specific advice or examples.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-11-10T14:16:25.543Z
Answers.Text: would you know hot to pull entries using a sub category (level 2) slug?
Answers.UserDisplayName: BillyMedia
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-02-28T18:57:35.157Z
Answers.Text: This is great and super helpful. Thanks for posting. I was having problems with data overwrites because of a bad double relatedTo() in my query. Problem is now solved!
Answers.UserDisplayName: Tony DeStefano
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-08-23T11:07:41.417Z
Answers.LastActivityDate: 2016-08-23T11:07:41.417Z
Answers.UserDisplayName: Mats Mikkel Rummelhoff
Body: <p>I want to filter data based on category, I would prefer to do this using a <code>join</code> statement, but I can't figure out how an entry is related to its category. I had the same problem with matrix field, but figured it out after hours of research, the code for this is below. A similar statement to filter based on category would be great.</p>
  
  <pre><code>$criteria = craft()-&gt;elements-&gt;getCriteria(ElementType::Entry);
  $criteria-&gt;section = 'affiliates';
  $criteria-&gt;limit = null;
  $query = craft()-&gt;elements-&gt;buildElementsQuery($criteria);
  $query-&gt;join('matrixblocks matrixblocks', 'matrixblocks.ownerId = entries.id');
  $mat_name = 'field_handle';
  $query-&gt;join('matrixcontent_'.$mat_name.' matrixcontent_'.$mat_name, 'matrixcontent_'.$mat_name.'.elementId = matrixblocks.id');
  $query-&gt;join('relations relations', 'relations.sourceId = matrixcontent_'.$mat_name.'.elementId');
  $query-&gt;join('content '.$mat_name, $mat_name.'.elementId = relations.targetId');
  $query-&gt;andWhere($mat_name.".title"." IN ('abc', 'def')");
  </code></pre>
  
Tags: <plugin-development><mysql>
Title: Filtering entries based on category?
ThreadText: # Filtering entries based on category?
  
  (Asked by: username_0 on 2016-08-21)
  
  I want to filter data based on category, I would prefer to do this using a `join` statement, but I can't figure out how an entry is related to its category. I had the same problem with matrix field, but figured it out after hours of research, the code for this is below. A similar statement to filter based on category would be great.
  
  ```
  $criteria = craft()->elements->getCriteria(ElementType::Entry);
  $criteria->section = 'affiliates';
  $criteria->limit = null;
  $query = craft()->elements->buildElementsQuery($criteria);
  $query->join('matrixblocks matrixblocks', 'matrixblocks.ownerId = entries.id');
  $mat_name = 'field_handle';
  $query->join('matrixcontent_'.$mat_name.' matrixcontent_'.$mat_name, 'matrixcontent_'.$mat_name.'.elementId = matrixblocks.id');
  $query->join('relations relations', 'relations.sourceId = matrixcontent_'.$mat_name.'.elementId');
  $query->join('content '.$mat_name, $mat_name.'.elementId = relations.targetId');
  $query->andWhere($mat_name.".title"." IN ('abc', 'def')");
  
  ```
  
  **Tags:** plugin-development, mysql
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2016-08-23. Score: 14
  
  Craft has a pretty solid engine for relational queries, so unless what you want to do is totally outside the box, there's a good chance you can accomplish what you want by using the [`relatedTo`](https://craftcms.com/docs/relations) parameter available to the [ElementCriteriaModel](https://craftcms.com/docs/plugins/working-with-elements). *Note that the `relatedTo` entry in the docs pertain to templates and not plugin development per se, but aside from the syntax the concept and approach will be identical in PHP.*
  
  Here are some examples, since its not obvious to me exactly what you hope to achieve:
  
  To pull entries related to a certain category:
  
  ```
  $criteria = craft()->elements->getCriteria(ElementType::Entry, [
      'section' => 'affiliates',
      'limit' => null,
      'relatedTo' => [
          'element' => $category, // Instance of CategoryModel, or a category ID
      ],
  ]);
  
  ```
  
  To pull entries related to multiple categories:
  
  ```
  ...
  'relatedTo' => [
      'element' => $categories, // Array of CategoryModels or category IDs
  ],
  ...
  
  ```
  
  To pull entries related to a category, where the relation is made in a certain Categories field:
  
  ```
  ...
  'relatedTo' => [
      'element' => $category,
      'field' => 'someCategoriesFieldHandle',
  ],
  ...
  
  ```
  
  To pull entries related to a category, where the relation is made in a certain Categories field on the EntryModel:
  
  ```
  ...
  'relatedTo' => [
      'targetElement' => $category,
      'field' => 'someCategoriesField',
  ],
  ...
  
  ```
  
  To pull entries related to a category, where the relation is made in a certain Entries field on the CategoryModel:
  
  ```
  ...
  'relatedTo' => [
      'sourceElement' => $category,
      'field' => 'someEntriesField',
  ],
  ...
  
  ```
  
  To pull entries with multiple possible relations, where *any* of the relational parameters can be met:
  
  ```
  ...
  'relatedTo' => ['or', [
      'targetElement' => $categories,
      'field' => 'someCategoriesField',
  ], [
      'targetElement' => $someOtherCategories,
      'field' => 'someOtherCategoriesField',
  ]],
  ...
  
  ```
  
  To pull entries with multiple possible relations, where *all* of the relational parameters must be met:
  
  ```
  ...
  'relatedTo' => ['and', [
      'targetElement' => $categories,
      'field' => 'someCategoriesField',
  ], [
      'targetElement' => $someOtherCategories,
      'field' => 'someOtherCategoriesField',
  ]],
  ...
  
  ```
  
  If you edit your question to include exactly what you want to do, I might be able to give you more specific advice or examples.
  
  > **Comments:**
  > **username_2**: would you know hot to pull entries using a sub category (level 2) slug?  
  > **username_3**: This is great and super helpful. Thanks for posting. I was having problems with data overwrites because of a bad double relatedTo() in my query. Problem is now solved!  
  
  ---
  
  