I have a table with 3 columns EmployeeNum, JobTitleCode and cost. I have made an entity for it with basic CRUD functions.
EmployeeNum and JobTitleCode are both Foreign Keys and when displaying the entity I want to be able to query another table in the database to extract EmployeeName from EmployeeNum and JobTitle from JobTitleCode.
In Symfony 1.4 I could just do Doctrine_Core::getTable('Employees').find('EmployeeNum') by defining 'Employees' in the schema but I couldn't find any similar method in Symfony 2.4.
From the API for getDoctrine() I didn't find any similar methods I could use and if I build a query as such:
createQuery('SELECT a FROM hs_hr_employee a WHERE a.emp_number=1')
I get the error below
[Semantical Error] line 0, col 14 near 'hs_hr_employee': Error: Class 'hs_hr_employee' is not defined
So how do you go about accessing data from other tables in the db?
Doctrine always tries to map your results to entities by default.
You can use a prepared statement without the need to provide a result-mapping:
get the connection:
$connection = $em->getConnection();
preparing a statement and executing it:
$statement = $connection->prepare(
'SELECT a FROM hs_hr_employee a WHERE a.emp_number = :emp'
);
$statement->bindValue('emp', $emp);
$statement->execute();
// for SELECT queries
$result = $statement->fetchAll('EAGER'); // note: !== $connection->fetchAll()!
// for INSERT, UPDATE, DELETE queries
$affected_rows = $statement->rowCount();
This is pretty expressive. See the examples below for shorter variants.
Alternative:
Use a Native SQL query with a result-mapping.
Advanced example shows how to rename columns in the result-array.
// create a result-mapping
$rsm = new ResultSetMapping;
$rsm->addScalarResult('n', 'nickname');
$rsm->addScalarResult('f', 'muchachos');
$query = $em->createNativeQuery(
'
SELECT
users.name as n
COUNT(user.friends) as f
WHERE
users.name = :username_parameter
FROM
user_table_name users
',
$rsm
);
$query->setParameter('username_parameter', $username);
$result = $query->getSingleResult(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);
// example: $username = 'nifr'
// array => [
// 'nickname' => 'nifr'
// 'muchachos' => 3919410
// ]
Shorter versions: (letting doctrine prepare the statement + directly fetch result)
// for SELECT queries
$result = $connection->executeQuery(
'SELECT a FROM hs_hr_employee a WHERE a.emp_number = ?'
array($emp)
);
//for INSERT, UPDATE, DELETE queries
$affected_rows = $connection->executeUpdate(
'DELETE FROM hs_hr_employee a WHERE a.emp_number = ?',
array($emp)
);
// fetch all into array ( <numeric-index> => <associative-array-entry> )
$connection->fetchAll(
'SELECT a FROM hs_hr_employee a WHERE a.emp_number = ?',
array($emp)
);
// fetch only first-row of result-set as associative array
$connection->fetchAssoc(
'SELECT a FROM hs_hr_employee a WHERE a.emp_number = ?',
array($emp)
);
// fetch only first-row of result-set as array with numeric indexes
$connection->fetchArray(
'SELECT a FROM hs_hr_employee a WHERE a.emp_number = ?',
array($emp)
);
Even shorter variants (for of INSERT, UPDATE or DELETE queries)
// DELETE FROM user WHERE id = ? (1)
$conn->delete('user', array('id' => 1));
// INSERT INTO user (username) VALUES (?) (nifr)
$conn->insert('user', array('username' => 'nifr'));
// UPDATE user (username) VALUES (?) WHERE id = ? (nifr, 1)
$conn->update('user', array('username' => 'nifr'), array('id' => 1));
Useful links: