Performing Search With Dynamic Joins

Yii 1.1 General Discussion
1

Hey all,

I’m having issues performing a query and have not found anything that suited my situation in the forum, 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. 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 "xpto"

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 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 right? Which I really have to because of the filter the user might 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 the issue might not be clear, so apologies if I couldn’t make myself understand.

Any tips are welcome.

Regards

2

Just use a plain text query.


$qtxt ="SELECT your_field FROM your_table WHERE your_other_field LIKE :your_other_field";

if($company_filter){

  $qtxt .= "LEFT JOIN and whatever";

}

$command =Yii::app()->db->createCommand($qtxt);

$command->bindValue...

$res =$command->queryAll();
3

Use my extension RelatedSearchBehavior!

http://www.yiiframework.com/extension/relatedsearchbehavior/

It is easy to define relations that are several levels away and it is easy to define search conditions on those. Check out the demo.

4

Don’t know why I was so confused.

Instead of binding the where params I just appened, as you suggested, the where to the query itself and then binded the values. Thanks :)

@le_top I will definalty check it out too!

Thank you all!