2

This has to be something that is easy, but I've been beating my head on my desk for a a few hours with this. Most solutions I have come across are way to complex - when all I need to do is query a section for all entries that appear in two categories.

I'm pulling in the slugs via url,

ex: domain.com/investigators?t=primary&a=pregnancy-newborn-medicine

Then querying like so:

{% set typeQuery = craft.app.request.getParam('t') %}
{% set areaQuery = craft.app.request.getParam('a') %}

{% set cat1 = craft.categories.group('investigatorTypes').slug(typeQuery).one() %}
{% set cat2 = craft.categories.group('areaOfResearch').slug(areaQuery).one() %}

{% set researchers = craft.entries()
    .section('investigatorBios')
    .relatedTo(['and', 
        {element: cat1},
        {element: cat2}
    ])
    .orderBy('bioLastName asc')
    .limit(12)
%}  

What am I doing wrong?
Slugs match the respective categories in the groups. There are only two types in the investigatorTypes I can query for - one returns all of them, and the other returns none.

Am I overlooking something obvious?

Thanks in advance, friends.

  • Your code is working for me. I didn't use multiple category groups though, doubt that's the problem. Have you tried outputting {{ cat1.title }}{{ cat2.title }} to confirm that you're correctly grabbing the categories? – Simon Kuran Jul 3 at 1:34
  • Yep. It's correctly grabbing the categories. It seems to be confused when it is filtering by the "investigatorTypes" as opposed to the "areaOfResearch". – gjhead Jul 5 at 20:14
2

First, I might over complicate things. Maybe there is a simple solution by using: https://docs.craftcms.com/v3/relations.html#passing-multiple-relation-criteria

But my project has a complex query built in. So I did a custom db query class. And here is one of them might working for you.

If you know how to create a twig function in craft 3 in modules, the following code will make sense to you. If you don't know how to do it. Here is the link how to do it https://docs.craftcms.com/v3/extend/extending-twig.html#register-a-twig-extension.

    /**
     * This function is a twig extension

     * @param $IdArray      array           the array of element ids used for related to 
     *                                      you can join as many ids you want to.
     *                                      e.g. [
     *                                              [
     *                                                "or", ["1234","23123"]
     *                                              ],
     *                                              [
     *                                                "and", ["234","1232","12332"]
     *                                              ],
     *                                              ...
     *                                            ]
     * @return object                       craft element query object
     */
    public function elementMultiRelatedTo($elementQuery, $IdArray=[]){
        if($IdArray!=[]&&isset($IdArray[0])&&is_array($IdArray[0])){
            foreach ($IdArray as $key=>$value){
                if(isset($value[0])&&is_string($value[0])&&isset($value[1])&&is_array($value[1])&&$value[1]!=[]){
                    if($value[0]=="or"){
                        $elementQuery = $elementQuery->orWhere($this->multipleRelatedToQuery($value[1]));
                    }elseif($value[0] == "and"){
                        $elementQuery = $elementQuery->andWhere($this->multipleRelatedToQuery($value[1]));
                    }
                }
            }
        }

        return $elementQuery;
    }

    /**
     *
     * @param $id
     * @return \yii\db\Expression
     */
    public function multipleRelatedToQuery($id){
        $sourceString = "";
        $targetString = "";
        if($id==[""]||$id==""||$id==[]){
            return new \yii\db\Expression("1");
        }
        if(is_array($id)){
            foreach($id as $key => $v){
                if($key == 0){
                    $sourceString = "`sources2`.`sourceId` = '$v'";
                    $targetString = "`targets2`.`targetId` = '$v'";
                }else{
                    $sourceString = $sourceString . " OR `sources2`.`sourceId` = '$v'";
                    $targetString = $targetString . " OR `targets2`.`targetId` = '$v'";
                }

            }
        }else{
            $sourceString = "`sources2`.`sourceId` = '$id'";
            $targetString = "`targets2`.`targetId` = '$id'";
        }

        return new \yii\db\Expression("
        (
            `elements`.`id` IN 
            (
                SELECT 
                    `sources2`.`targetId`
                FROM
                    `craft_relations` `sources2`
                WHERE
                    $sourceString
            )
            OR `elements`.`id` IN 
            (
                SELECT 
                    `targets2`.`sourceId`
                FROM
                    `craft_relations` `targets2`
                WHERE
                    $targetString
            )
        )");
    }

Then in twig

{% set typeQuery = craft.app.request.getParam('t') %}
{% set areaQuery = craft.app.request.getParam('a') %}

{% set cat1 = craft.categories.group('investigatorTypes').slug(typeQuery).one().id %}
{% set cat2 = craft.categories.group('areaOfResearch').slug(areaQuery).one().id %}

{% set researchers = craft.entries()
    .section('investigatorBios')
    .orderBy('bioLastName asc')
    .limit(12)
%}

{# here cat1 and cat2 is not array, make sure put them in [] #}
{# if you want more than one id, you can try ".ids()" instead of ".one().id" #}

{% set researchers = elementMultiRelatedTo(resaerchers, 
[
  [
     "and",
     [cat1]
  ],
  [
     "and",
     [cat2]
  ]
]) %}
{% researchersArray = researchers.all() %}
1

After a ton of headbanging (and not the fun heavy-metal kind) I ended up following the answer in this post, and it seems to be working!

Craft 3 Multi Category and Field Search

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.