1

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')");
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 parameter available to the ElementCriteriaModel. 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.

| improve this answer | |
  • would you know hot to pull entries using a sub category (level 2) slug? – BillyMedia Nov 10 '17 at 14:16
  • 1
    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! – Tony DeStefano Feb 28 '19 at 18:57

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.