what i want is to add the WHERE clause to an existing query.
The query builder add the where clause but is executing the query without the where clause.
$sql = 'select name from service ';
$q = conn()->createCommand($sql);
$q->setWhere(array('IN','id',array(40,2,24)));
$rows = $q->queryAll();
// RETURNS THE CORRECT WHERE CLAUSE AS ADDED
echo $q->getWhere();
// BUT THIS RETURNS 'select name from service', NO WHERE CLAUSE.
echo $q->getText(); =>
$q = Yii::app()->db->createCommand()
->select('name')
->from('service')
->where(array('IN','id',array(40,2,24)));
echo $q->text;
Yeah Antonio, right! but i dont want to build the whole query with the query builder but have to add a particular clause (like WHERE) to an existing query.
See, i have created a class ModelQueries where i have defined all my sql queries in an array & i use it like this to get the query:
ModelQueries::getQuery($modelName, $actionId);
So in this case i have to add the IN condition to the returned query:
$q = ModelQueries::getQuery('Service', 'serviceNames);
$q = conn()->createCommand($q);
$q->where(array('IN','id',array(40,2,24)));
echo $q->getText();
BUT its returning the query without the where clause.
This is the structure of the array:
static $q = array(
'Service' => array(
'showRunningTasks' => array(
'sql' => 'SELECT p.id,s.id as SID, p.name, p.code, s.name as SName, p.firm_id, p.estimated_cost,p.iscompleted
FROM service s, project p where s.id=1 AND s.id=p.service_id',),
'serviceNames' => array(
'sql'=>'select name from service ',),
),
'Client' =>array(
'viewJSON'=>array(
'sql'=>'select *,
(SELECT name from client_categories where id=c.category_id) AS category,
(SELECT name from client_grades where id=c.grade_id) AS grade,
),
(SELECT name from client_groups where id=c.group_id) AS group
from clients c WHERE c.id=:id LIMIT 1',
),
);
You cannot mix the two…
Check the documentation for the Query Builder… - http://www.yiiframework.com/doc/guide/1.1/en/database.query-builder#preparing-query-builder
To start the query builder you call createCommand()… without any parameter…
$q = Yii::app()->db->createCommand()
->select('name')
->from('service')
->where(array('IN','id',array(40,2,24)));
Yeah its fine, but one thing is still bothering me is once i have created the query in the query builder style
i cannot change any of the clauses like:
somewhere if have to change my SELECT clause to include the column ‘id’ also i would do:
$q->select('id, name');
But it do not update the query to include the updated clauses.
I think there should be something like:
$q->updateQuery(); OR $q->reBuildQuery();
if we change any part/clause of the query.
what do you think?
You need to call $q->setText(’’) to clean up the query previously built.
Hey qiang!
but you see if i clear the query i have to write the whole query again. Then i would’ve written the new query at the first place, no need clear & use the same object (if used once in that code) .
[b]Thats what my point is:
why not to change the existing part (clause) of the query without clearing it whole, just update that part and rebuild the query reconsidering that part!!![/b]
I do not know if it is purposely done but I would like to know why isn’t the query builder built when the constructor has a $sql text passed to it. Wouldn’t be a much nicer feature to be able to break down the parameterized SQL string and build the query internally so when a user does:
$q = Yii::app()->db->createCommand('select XX from tbl_XX');
$q->where(array('IN','id',array(40,2,24)));
echo $q->text;
It actually returns the whole query instead of just ‘select XX from tbl_XX’.
And about your question Anupam:
Why dont you build your queries this way?
$q = array(
'Service' => array(
'showRunningTasks' => array(
'select' => 'p.id,s.id as SID, p.name, p.code, s.name as SName, p.firm_id, p.estimated_cost,p.iscompleted',
'from' => 'service s, project p',
'where'=> 's.id=1 AND s.id=p.service_id')
),
);
Now, when you call your getQuery, you can use the returned value like this:
$q = ModelQueries::getQuery('Service', 'showRunningTasks');
$cmd = conn()->createCommand();
// this way
foreach($q as $key->$val)
$cmd->$key($val);
// or more readable
$cmd->select($q['select'])
->from($q['from'])
->where($q['where']);
echo $cmd->text;
What do you think?
No, setText(’’) only cleans up the SQL previously built. It doesn’t clean up each individual part.
Exactly Antonio! This was the thing i was trying to do initially.
Yes, this is the only solution i am left with for now.
rather i think we can make it even shorter
$q = ModelQueries::getQuery('Service', 'showRunningTasks');
$cmd = conn()->createCommand();
$cmd->buildQuery($q);
echo $cmd->text;
But many times i have to supply the WHERE clause or specifically parameters, which includes both Compare and IN condition. For that i have to create 1 more parameter in the getQuery():
ModelQueries::getQuery('Service', 'showRunningTasks', $condition);
and add the conditions in the $q array and then call
$cmd->buildQuery($q);
Right qiang! now i can do like:
$q = Yii::app()->db->createCommand()
->select('name')
->from('service')
->where(array('IN','id',array(40,2,24)));
$q->setText('');
$q->select('id, name');
$q->where('id=40');
echo $q->getText();
Now it returns the updated select and where clause. As intended.
But this way its still not:
$sql = 'select name from service ';
$q = conn()->createCommand($sql);
$q->where('id=40');
$rows = $q->queryAll();
This will throw CDbException as the individual parts were not set earlier.
The solution will be to save the query inside the array in parts like Antonio suggested.
Right, you cannot mix plain sql with query builder because it is very difficult to parse a SQL and then modify a part of it.
Driven by curiosity I found an algorithm on Perl that uses regular expressions to tokenize a SQL statement. Through the loop of its sections i have been able to extract the main parts of a sane simple query. Maybe it is a good starting point to build a better SQL parser.
I post it here for your consideration and review. If you think is good enough to be posted as an extension I will do.
Would be nice to know what do you think about it.
Cheers
Example of Usage (with a really silly SQL to test)
Yii::import('ext.ESQLParser');
$t = new ESQLParser();
// returns a reference to the object itself
$parser = $t->parse('select * from clients c WHERE c.id=:id IN (SELECT MANOLO FROM PEPE) group by name having pepe=1 LIMIT 10');
echo 'select '.$parser->select.'<br>';
echo 'from '.$parser->from.'<br>';
echo 'where '.$parser->where.'<br>';
echo 'limit '.$parser->limit.'<br>';
echo 'group '.$parser->group.'<br>';
echo 'having '.$parser->having.'<br>';
Good job. However, the parser has limitation as it is regex-based which will fail in certain scenarios. Also it seems to be MySQL specific.
Thanks qiang,
It is actually made to brainstorm a bit about it and yes, it is certainly created for mySQL as it is the the database i mainly work but could it that be changed? I do not know how useful it could be or if it could be modified to fulfill those scenarios where the regex will fail (any resource to see those possible scenarios? i check it just for fun).
This is why I uploaded this test class to this forum post… I really do not know how useful it could be…
Thanks again
Nice dig Antonio!
Well this can be used as a utility to parse Saved queries, check clauses against some conditions, alter it & execute. or can convert existing saved queries in a query builder format.
Although query builder performs great and returns specific parts of the query still this could be a nice parsing utility.
cheers 
Thanks Anupam,
Nevertheless, qiang is very right about regex functions. Instead of that, would be better to create appropriate library to do that and I assume wouldn’t just take a simple class to do so.
I guess a proper lexer class should be a better to tokenize a string and then build the correspondent classes per DB type. Was fun anyway.