Joomla：使用JDatabaseQuery来表示插入..选择查询？

Question

So I need to run a long(ish) query to insert a new row into a table, based on values of another row elsewhere in the database. This is running in Joomla 3.1.5

Typically, you can use MySql's INSERT .. SELECT syntax to easily do this, but I'm looking for a way to keep close to Joomla's query builder, example:

<?php

// ...

// Base Tables & Columns.

$my_table = '#__my_table';

$columns = array('column_one', 'column_two');

// Set up the database and query.

$db = JFactory::getDBO();

$query = $db->getQuery(true);

// Escape / quote the table name.
$my_table = $db->quoteName($my_table);

// Escape all columns.
$cols = array_map(array($db, 'quoteName'), $cols);

$query
    ->insert($my_table)
    ->columns($columns)
    // E.g. ->select( ... )->from( ... ) ...

$db->setQuery($query);

$result = $db->query();

// ...

?>

Of course, the example comment won't work, but I was wondering if there was a way which would allow me to perform something similar (without needing to run a separate query elsewhere).

Naturally, if there's no way to perform this type of query, I can just drop to using a raw query string.

Answer 1

The docblocks of many JDatabaseQuery methods include the following statement

 * Note that you must not mix insert, update, delete and select method calls when building a query.

This is because ... to over simplify but as an example how would we know which list of columns (or which where clause etc) to put where when we are building the query.

But there are ways that you can get around this limitation by building your query in a slightly more complex way (which is fair since it's a more complex query). So for example

$db = JFactory::getDBO();

$queryselect = $db->getQuery(true);
$queryselect->select($db->quoteName(array('id','title','alias')))
    ->from($db->quoteName('#__content'));
$selectString = $queryselect->__toString();


$queryInsert = $db->getQuery(true);
$queryInsert->columns($db->quoteName(array('id','title','alias')))
->insert($db->quotename('#__newtable'));

$db->setQuery($queryInsert . $selectString);
$db->execute();