Entry Query relatedTo Two Categories

Question

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.

Answer 1

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() %}

Answer 2

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