Execute raw SQL using Doctrine 2
I found out the answer is probably:
A NativeQuery lets you execute native
SQL, mapping the results according to
your specifications. Such a
specification that describes how an
SQL result set is mapped to a Doctrine
result is represented by a
ResultSetMapping.
Source: Native SQL.
Using Raw SQL with Doctrine
$q = Doctrine_Manager::getInstance()->getCurrentConnection();
$result = $q->execute(" -- RAW SQL HERE -- ");
See the Doctrine API documentation for different execution methods.
Execute raw SQL using Doctrine 2 with Doctrine paginator
Please try that :
$maxpage = ceil(count($paginator) / $limit);
Read the doc
Raw SQL Query from Non Default Connection
Here is how i'm doing it.
First you need to update your config to set your different connections in config/package/doctrine.yaml
:
doctrine:
dbal:
default_connection: default
connections:
default:
url: '%env(resolve:DATABASE_URL)%'
alt:
url: '%env(resolve:DATABASE_URL_ALT)%'
Then, in your controller or service you can inject the ManagerRegistry
and get the connection :
public function index(ManagerRegistry $managerRegistry)
{
$connection = $managerRegistry->getConnection("alt");
$result = $connection
->prepare("SELECT * FROM your_table_in_alt_db")
->executeQuery()
->fetchAllAssociative()
;
dump($result);
}
How to get the raw sql of doctrine
According to Logging Doctrine SQL queries in Symfony2:
You can use a Doctrine logger such as DebugStack or own implementation of SQLLogger interface.
$logger = new \Doctrine\DBAL\Logging\DebugStack();
/* @var Doctrine\DBAL\Connection $connection */
$connection->getConfiguration()->setSQLLogger($logger);
After execution of some queries you can get query strings and parameters from public property of the logger.
var_dump($logger->queries);
You second question is very different. Btw, as said in documentation:
Objects that were not already loaded from the database are replaced
with lazy load proxy instances. Non-loaded Collections are also
replaced by lazy-load instances that fetch all the contained objects
upon first access. However relying on the lazy-load mechanism leads to
many small queries executed against the database, which can
significantly affect the performance of your application. Fetch Joins
are the solution to hydrate most or all of the entities that you need
in a single SELECT query.
Doctrine 1 Migrations, How to execute raw SQL
What I do in these cases is create an empty migration class using the symfony doctrine:generate-migration
command and then fill in both up()
and down()
methods similar to this:
public function up()
{
$dbh = Doctrine_Manager::connection()->getDbh();
$query = "INSERT INTO `some_table` (`id`, `created_at`, `updated_at`)
VALUES
('1', NOW(), NOW()),
('2', NOW(), NOW()),
('3', NOW(), NOW());
";
$stmt = $dbh->prepare($query);
$stmt->execute();
}
Related Topics
Mongodb Duplicate Documents Even After Adding Unique Key
Compare Multiple Values in PHP
How to Run a PHP Script Inside a HTML File
Fatal Error: Call to a Member Function Prepare() on Null
How to Replace Text Urls and Exclude Urls in HTML Tags
PHP Expression ≪≪≪Eob
How to Re-Index the Values of an Array in PHP
Laravel 5 Failed Opening Required Bootstrap/../Vendor/Autoload.PHP
Simplexml and Print_R() - Why Is This Empty
Multidimensional Array Iteration
What Is the Syntax For Accessing PHP Object Properties
What Does MySQL_Real_Escape_String() Do That Addslashes() Doesn'T