Query Builder: Multiple calls to select

Archives Feature Requests
1

EDIT: This can probably be considered as a feature request.

I’m building a somewhat complex command, and I need to append to the selected fields in some cases.

I would expect the following to work:

$command->select(‘name,age’);

Then later on:

$command->select($command->getSelect().‘email,created)’); // with our without a leading , between old and new fields

However, this results in a syntax error in the SQL statement, due to the fields from the first select now being quoted twice.

I can work around this by not setting the select() until I know all the fields I need, but that’s not as elegant in my case.

This could be solved by adding a more intuitive method addSelect() or similar. I have not checked if this works for where(), but I’d guess the same is relevant there. Is this something you can consider adding?

2

I figured out a little something that lets you conditionally add to your query as it is being built, avoiding a bunch of if statements. Basically trying to do what the above feature would do. See http ://code.google.com /p/yii/issues/detail ?id=1856 (remove spaces, sorry first post). Looks like this feature wont be added.

In my case I needed a where clause with multiple and variable numbers of AND comparisons.

Set up query normally, omitting the where clause:




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

$command->select('v.id as vID,sr.id AS srID') 

		->from('whatever v')

		->join('whateverelse sr','v.id = sr.joinfield');

Use 2 arrays to track the conditions and params what will be passed to the where function of CDBCommand. Here we’re using the array form of conditions rather than a string.




$whereConditions = array('and');

$whereParams = array();

Figure out if you want to add to the where clause:




if ( $something ) { 

  $whereConditions[] = 'sr.something <= :something';

  $whereData[':something'] = $yourFieldSomething;

}

Then later on, add some more conditions:




$whereConditions[] = 'v.afield = :somevalue';

$whereData[':somevalue'] = $someValue;

Then finally assemble your where clause, and fire off the query:




$command->where($whereConditions,$whereData);

$results = $command->queryAll();

You end up with a WHERE clause like:




(v.afield=:somevalue) AND (sr.something <= :somevalue)

So this is all very particular to my case, but you can see how to use it for building portions of a query incrementally, and then setting that portion on CDBCommand once.