Query Builder question

Can someone explain how to dynamically build a query with query builder?

I am trying it like this but it only returns the object, no results. If I copy the text of the SQL in the object into mysql workbench then I get the results, so I know the query is good, it just seems that the way I am trying to use this isn’t right.

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

            $pendingCount->select("COUNT(*)");

            $pendingCount->from("invoice_items_$tran_id i");

            $pendingCount->join('customers c', 'c.cust_id = i.cust_id');

            $pendingCount->join('service_location s', 's.serv_id = i.serv_id');

            $pendingCount->join('states ss', 'ss.id = s.state');

            $pendingCount->where('i.invoice_id IS NULL AND i.is_voided=0');

            if(isset($_GET['sSearch']) && $_GET['sSearch'] != ""){

                for($i=0; $i<count($sColumns); $i++){

                    $pendingCount->andWhere($sColumns[$i]." LIKE '%:param_$i%'", array(":param_$i" => $_GET['sSearch']));

                }

            }

            for($i=0; $i<count($sColumns); $i++){

                if(isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != ''){

                    $pendingCount->andWhere($sColumns[$i]." LIKE '%:param2_$i%'", array(":param2_$i" => $_GET['sSearch']));

                }

            }            

            if(isset($_GET['iSortCol_0'])){

                $sOrder = "";

                for($i=0; $i<intval($_GET['iSortingCols']); $i++){

                    if($_GET['bSortable_'.intval($_GET['iSortCol_'.$i])] == "true"){

                        $sOrder .= $sColumns[intval($_GET['iSortCol_'.$i])]." ".($_GET['sSortDir_'.$i]==='asc'? "ASC" : "DESC").", ";

                    }

                }

                $sOrder = substr_replace($sOrder, "",-2);

                if($sOrder != ""){

                    $pendingCount->order($sOrder);

                }

            }

            if(isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1'){

                $pendingCount->limit(intval($_GET['iDisplayLength']), intval($_GET['iDisplayStart']));

            }           

            $pendingCount->queryAll();

text result is in the object, but results are not.

SELECT `i`.*, `c`.`cust_name`, `s`.`location_name`, `s`.`street`, `s`.`suite`, `s`.`city`, `s`.`zip`, `ss`.`stateCode`, `c`.`active` AS `cust_active`, `s`.`active` AS `serv_active`

FROM `invoice_items_9` `i`

JOIN `customers` `c` ON c.cust_id = i.cust_id

JOIN `service_location` `s` ON s.serv_id = i.serv_id

JOIN `states` `ss` ON ss.id = s.state

WHERE i.invoice_id IS NULL AND i.is_voided=0

ORDER BY `c`.`cust_name` ASC LIMIT 100

Okay, I solved my own problem.

Where I was doing

$pendingItems->queryAll()

and then I try to access as $pendingItems, like with a model.

I needed to do this as an extra step to get the results.

$results = $pendingItems->queryAll()

So it works. I think. I have not had a chance to test the multiple andWhere yet, but the documentation says this is appended to the query so I hope it works as expected.

Okay, my andWhere needs some work. I see how to do that properly I think

What I need is to get a mixed AND/OR like this

WHERE i.invoice IS NULL AND i.is_voided=0 AND(i.name LIKE "%test%" OR i.col2 LIKE "%test%")

If I know the columns I want to search, can I just write the andWhere as a string like this?

$pendingItems->andWhere("i.name LIKE :param OR i.col2 LIKE :param OR i.col3 LIKE :param",array(':param'=>"%".$param."%")