I have several SQL queries that work via command line, but would like to convert them to QueryBuilder due to issues with CDbConnection::createCommand recognizing valid variables.
The SQL is
INSERT INTO bgt.question_mappings(parentId, childId)
SELECT parentId, NID FROM bgt.question_mappings
WHERE bgt.question_mappings.childId = PID
UNION SELECT ALL NID, NID
Any idea how I can adapt this SQL to QueryBuilder?
Hi my friend
first I cannot full understand your query, please describe what do want with this query
secondly post your database schema (ER diagram)
Basically, this is the mapping part of a closure table pattern to represent a hierarchical tree in a flat SQL table. It maps the parent-child nodes to create a series of paths throughout to represent paths from node x to node z and everything in between.
You can see a good description of this in action at Bill Karwin’s slideshow starting at slide 40. He does a better job of explaining than I ever could.
Finally, that query should have :NID and :PID in it to represent data binding sites for nodeId and parentId fields.
I think the best way to do something like that is using
SQL via DAO instead of Query builder
http://www.yiiframework.com/doc/guide/1.1/en/database.query-builder
I recommend SQL DAO for very complex queries or for those one has no directed association with a Model
For data insert you can do like this using query builder
insert()
function insert($table, $columns)
The insert() method builds and executes an INSERT SQL statement. The $table parameter specifies which table to be inserted into, while $columns is an array of name-value pairs specifying the column values to be inserted. The method will quote the table name properly and will use parameter-binding for the values to be inserted.
Below is an example:
// build and execute the following SQL:
// INSERT INTO tbl_user (name, email) VALUES (:name, :email)
$command->insert(‘tbl_user’, array(
'name'=>'Tester',
'email'=>'tester@example.com',
));