在Yii中使用动态过滤器和动态连接执行搜索

I'm having issues performing a query and have not found anything that suited my situation, so here is my scenario.

I'm displaying a dataTable with information and have multiple filters that I can apply.

So far, so good. The issue now is that I have to implement something like dynamic filters, meaning:

• The user presses a button that says "Add Filter"(he can press it as many times as he wants to add multiple filters).
• When he presses that button, a div element appears with 2 elements inside. A dropdown list and an input box.
• The user can select the field he wants to search on the dropdown list and writes the value he wants to be searched in the input.
• He presses "Search" and the query is performed.

Lets say that in the dropwdown list the user chooses the option "Company" and in the search field he writes "foo bar".

In the controller, I detect that the option "Company" was selected. The thing is, "Company" is not in the main table I'm searching, it is for example, 2 relationships away. As it is also possible to export data from that table, I'm doing LEFT JOINS, but am having dificulty binding the params.

I've tried multiple things, I am currently:

//this initial query is always performed
$test_query = 'SELETC a.*
               FROM tableA a
               LEFT JOIN TableB b ON b.name = a.name
               ';
$whereTxt = "<some_other_where_conditions>"; 
$bindParam[':some_field'] = $_POST["field_value"] //this $_POST is here only for better understanding, I don't actualy have the untreated value here.

Now, if "company" filter was detected in the dropdownlist:

if($company_filter){ 
  $test_query .= 'LEFT JOIN relation_table1 t1 ON a.team_id = t1.id
                  LEFT JOIN relation_table2 t2 ON t1.company_id = t2.id';
  $whereTxt .= " AND retaion_table2.company_name LIKE :company_name";
  $bindParam[':company_name'] = "% ".$_POST["company_name"]." %";
}

The issue is here How do I wrap this all up?

I tried:

$list = $this->getDBConnection()->createCommand($test_query)->where($whereTxt, $bindParam)->queryAll();

Fails, the where parameter isn't even considered in the query execution

If tried other variations but the outcome is the same, the params are unbinded or the query fails.

Ideally, I would have:

        $list = $this->getDBConnection()->createCommand()
            ->select("a.*")
            ->from('tableA a')
            ->leftjoin('regular_join')
            ->leftJoin('other_regular_join')
            ->where($whereTxt, $bindParam)
            ->queryAll();

But this way, I cannot add

->leftJoin(...)

dynamically corect? Which I really have to because of the filters the user might/might not have selected.

tl;dr

using getDBConnection()->createCommand() I need to insert LeftJoins dynamically depending on what the user chooses in the search filters, binding where parameters and arguments

I am aware that what I'm trying to achieve may not be clear, so apologies if I couldn't make myself understand.

Don't know why I was so confused.

Instead of binding the where params I just appened the where to the query itself and then binded the values after, ending up with something like:

$test_query= 'SELECT a.* FROM ....';

if($company_filter){ 
  $test_query .= 'LEFT JOIN relation_table1 t1 ON a.team_id = t1.id
                  LEFT JOIN relation_table2 t2 ON t1.company_id = t2.id';
  $whereTxt .= " AND relation_table2.company_name LIKE :company_name";
  $bindParam[':company_name'] = "%".$_POST["company_name"]."%";
}

$list = $this->getDBConnection()->createCommand($test_query." WHERE ".$whereTxt)->bindValues($bindParam)->queryAll();

Also, kudos to dgtal@Yii forum