Simple Select Statement In Doctrine
So I have recently found myself thrown into the deep end of Doctrine. The post assumes that you already know what an EntityManager is and that you have one already mapped your table to an entity object.
All I wanted to do was a simple “SELECT * FROM table WHERE x = y” type query. After a while I found the solution. The documentations that I found seemed to think that I was already able to use Doctrine, but the truth is I didn’t set it up! I was just told to use it!
Anyway, here is the code:
$query = “SELECT tbl FROM NameSpace\To\Table\Entity tbl WHERE tbl.column = :var”;
$queryObj = $this->entityManager->createQuery($query);
$queryObj->setParameter(“var”, $var);
$results = $queryObj->getArrayResult();
Let me go through that line by line:
$query = “SELECT tbl FROM NameSpace\To\Table\Entity tbl WHERE tbl.column = :var”;
This is a simple string of your query written in Doctrine Query Language. You will see that instead of * you put the table name that you declare after in the From clause. This is a massive bit of craziness, you are using a sort of variable before declaring it!For selecting in all columns you put the name of the table on its own, the one in the From clause which can be anything. For selecting specific columns, you do a list: tbl.column1, tbl.column2. Those columns must match the names you have in the Entity object.
Then you have the From clause. This should have the namespace to the Entity of the object that you are selecting from followed by the table name you are using in the rest of the query.
Then the WHERE. tbl.column is pretty standard. Again, the tbl is whatever you state in the FROM. Then I have :var. Wtf is this? Well think of this as a place holder that can be later replaced with some value. It is the same as if you were going to append a variable to a query string but done differently.
$queryObj = $this->entityManager->createQuery($query);
This simply turns your query into an object. You obviously call the createQuery() function from wherever you have stored your EntityManager object that you would have created at some point.
$queryObj->setParameter(“var”, $var);
OK, remember the :var in the query string earlier? This is how you replace it. Call the setParameter function from the query object and pass it the parameter name and the value that you want to replace it with. I just happen to have that value stored in some variable, $var.
$results = $queryObj->getArrayResult();
Finally, you do the call. Now, you could do getResult(). This would give you an object where the columns are protected fields and they have the values of the associated value in the table. I am not sure when this would be useful. I found getArrayResult() more useful because I was actually able to do something with it afterwards.
So there you have it. All that for a couple of hours. Would have been easier to use mysqli. I should be grateful that I didn’t have to create the Entity manager and the logistics behind that.
Although I did have to make all the Entity classes and the 700+ fields that they contain (don’t ask).