SymfonyCasts stands united with the people of Ukraine
If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.
All this Doctrine entity object stuff and DQL queries are really great. But if you ever feel overwhelmed with all of this or need write a really complex query, you can always fall back to using raw SQL. Seriously, this is huge. When Doctrine scares you, you are totally free to run away and use plain SQL. I won't judge you - just get that feature finished and launch it already. If a tool isn't helping you, don't use it.
Put on your DBA hat and let's write some SQL!
Open up FortuneCookieRepository. This is where we built a query that selected
a sum, an average and the category name.
The most important object in Doctrine is.... the entity manager! But that's
just a puppet for the real guy in charge: the connection! Grab it by getting
the entity manager and calling getConnection(). Let's var_dump() this:
| ... lines 1 - 14 | |
| public function countNumberPrintedForCategory(Category $category) | |
| { | |
| $conn = $this->getEntityManager() | |
| ->getConnection(); | |
| var_dump($conn);die; | |
| ... lines 21 - 28 | |
| } | |
| ... lines 30 - 32 |
Head to the browser and click into one of the category pages. There's our
beautiful var_dump(). Hey! Look at the class name:
Doctrine\DBAL\ConnectionFun fact! The Doctrine library is actually multiple libraries put together. The two parts we care about are the ORM and the DBAL. The ORM is what does all the magic mapping of data onto objects. The DBAL - or database abstraction layer - can be used completely independent of the ORM. It's basically a wrapper around PDO. Said in a less boring way, it's a library for executing database queries.
So this DBAL Connection objects is our key to running raw database queries. Google for "Doctrine DBAL Query" so we can follow its docs. Find the Data Retrieval And Manipulaton section. Scroll down a little to a good example:
$sql = "SELECT * FROM articles WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bindValue(1, $id);
$stmt->execute();This DBAL library is a really light wrapper around PHP's PDO. So if you've used that before, you'll like this. But if not, it's like 3 steps, so stick with me.
Back in FortuneCookieRepository, let's write some simple SQL to test with:
SELECT * FROM fortune_cookie;When you use the DBAL, there are no entities and it doesn't know about
any of our Doctrine annotations. Yep, we're talking to the raw tables and
columns. So I used fortune_cookies because that's the name of the actual
table in the database.
Next, we'll use the SQL to get a statement. So:
$stmt = $conn->prepare($sql);And then we can execute() that, which runs the query but doesn't give you
the result. To get that, call $stmt->fetchAll() and var_dump() that:
| ... lines 1 - 14 | |
| public function countNumberPrintedForCategory(Category $category) | |
| { | |
| $conn = $this->getEntityManager() | |
| ->getConnection(); | |
| $sql = 'SELECT * FROM fortune_cookie'; | |
| $stmt = $conn->prepare($sql); | |
| $stmt->execute(); | |
| var_dump($stmt->fetchAll());die; | |
| ... lines 24 - 31 | |
| } | |
| ... lines 33 - 35 |
Try it! And there it is: exactly what you'd expect with no effort at all. It's literally the results - in array format - from the raw SQL query. Doctrine isn't trying to hide this feature from you - just grab the Connection object and you're dangerous.
The query we made with the query builder is a bit more complex. Could we replacle that with raw SQL? Sure! Well there's not really a good reason to do this, since it's built and working. But let's prove we can do it!
Let's grab the "select" part of the query and stick that in our query. I
hate long lines, so let's use multiple. Piece by piece, add the other query
parts. The FROM is fortune_cookie fc. Add the INNER JOIN to category
ON cat.id = fc.category_id. And since we're in DBAL land, we don't have
any of our annotation mapping configuration, so we have to tell it exactly
how to join - it's just raw SQL. And for the same reason, we're using the
real column names, like category_id.
Add a single WHERE of fc.category_id = :category. That's some good-old-fashioned
boring SQL. I love it! The only thing we still need to do is fill in the
:category placeholder. Even though we're using the DBAL, we still don't
concatenate strings in our queries, unless you love SQL attacks or prefer
to live dangerously. Are you feeling lucky, punk?
Ahem. To give :category a value, just pass an array to execute() and
pass it a category key assigned to the id. Ok, done! Let's dump this!
| ... lines 1 - 14 | |
| public function countNumberPrintedForCategory(Category $category) | |
| { | |
| $conn = $this->getEntityManager() | |
| ->getConnection(); | |
| $sql = ' | |
| SELECT SUM(fc.numberPrinted) as fortunesPrinted, AVG(fc.numberPrinted) as fortunesAverage, cat.name | |
| FROM fortune_cookie fc | |
| INNER JOIN category cat ON cat.id = fc.category_id | |
| WHERE fc.category_id = :category | |
| '; | |
| $stmt = $conn->prepare($sql); | |
| $stmt->execute(array('category' => $category->getId())); | |
| var_dump($stmt->fetchAll());die; | |
| ... lines 29 - 36 | |
| } | |
| ... lines 38 - 40 |
Boom! That's exactly what I was hoping for.
Since our SQL gives us just one row, it'd be awesome to get just its
columns, instead of an array with one result. Just use fetch()!
| ... lines 1 - 14 | |
| public function countNumberPrintedForCategory(Category $category) | |
| { | |
| $conn = $this->getEntityManager() | |
| ->getConnection(); | |
| $sql = ' | |
| SELECT SUM(fc.numberPrinted) as fortunesPrinted, AVG(fc.numberPrinted) as fortunesAverage, cat.name | |
| FROM fortune_cookie fc | |
| INNER JOIN category cat ON cat.id = fc.category_id | |
| WHERE fc.category_id = :category | |
| '; | |
| $stmt = $conn->prepare($sql); | |
| $stmt->execute(array('category' => $category->getId())); | |
| var_dump($stmt->fetch());die; | |
| ... lines 29 - 36 | |
| } | |
| ... lines 38 - 40 |
And now, this is exactly what our query builder gave us before. So get rid
of the die() statement and return the fetch() line:
| ... lines 1 - 14 | |
| public function countNumberPrintedForCategory(Category $category) | |
| { | |
| $conn = $this->getEntityManager() | |
| ->getConnection(); | |
| $sql = ' | |
| SELECT SUM(fc.numberPrinted) as fortunesPrinted, AVG(fc.numberPrinted) as fortunesAverage, cat.name | |
| FROM fortune_cookie fc | |
| INNER JOIN category cat ON cat.id = fc.category_id | |
| WHERE fc.category_id = :category | |
| '; | |
| $stmt = $conn->prepare($sql); | |
| $stmt->execute(array('category' => $category->getId())); | |
| return $stmt->fetch(); | |
| ... lines 30 - 37 | |
| } | |
| ... lines 39 - 41 |
Just let the old code sit down there. Refresh! And we're prefectly back to normal. Man, that was kinda easy. So if Doctrine ever looks hard or you're still learning it, totally use SQL. It's no big deal.
One slightly confusing thing is that if you google for "doctrine raw sql",
you'll find a different solution - something called NativeQuery.
It sort of looks the same, just with some different function names. But there's
this ResultSetMapping thing. Huh. This NativeQuery thing allows you to
run a raw SQL query and then map that back to an object. That's pretty neat
I guess. But for me, if I'm writing some custom SQL, I'm fine just getting
back an array of data. I can deal with that. The ResultSetMapping confuses
me, and probably isn't worth the effort. But it's there if you want to geek
out on it.
Hey Alexo,
If your migration failed - there're two ways: you can fix the failed query and execute it again to finish the migration. Or your can rollback the previous queries to return DB to the original state before your deploy. But depends on your deploy script, you may also need to revert some code changes as well to avoid collision when new code changes talk to the old (not sync-ed) DB.
And yes, if you migration failed in the middle - you probably do want to rollback any queries manually. Even if you write the correct queries in "down()" method of your migrations - you just can't execute the whole migration down as it failed partially, so on down() it will fail anyway as some queries in up() were not executed. If you have correct queries in down() - you can copy/paste some instead of writing them from scratch but yes, it's totally OK to do the rollbacks manually.
Anyway, try to double check the queries in your migration before executing them on production. If you have a beta server where you can test things - do it first to avoid WTF moments on production, because fixing DB is not easy sometimes.
I hope this helps!
Cheers!
Is this outdated? Because it should be
$this->getDoctrine()->getConnection() instead of what it is in the guide.
Hey Galgaldas,
Haha, I suppose it's not :) Well, at least I don't see any info that sas it's deprecated in the source code :)
https://github.com/doctrine...
I think you just can get the DB connection in a few ways.
Cheers!
Hi fellas and kudos once again for the amazing work!
I've stumbled into an interesting problem and I was wondering if there is currently any (pretty) solution to this...
Our domain is an (atypical) auction's website where inside an auction there are multiple items available and each user can bid for one or many items, one or multiple times. So this translates to a bid table like this (AuctionBid entity)
`
+---------+-----------------+--------+-------------------+
| user_id | auction_item_id | price | date_bidded |
+---------+-----------------+--------+-------------------+
| 13 | 1 | 5555 | 11/03/19 01:41 PM |
+---------+-----------------+--------+-------------------+
| 12 | 1 | 33333 | 11/03/19 02:17 PM |
+---------+-----------------+--------+-------------------+
| 12 | 1 | 5600 | 11/03/19 02:21 PM |
+---------+-----------------+--------+-------------------+
| 14 | 1 | 44444 | 11/03/19 02:44 PM |
+---------+-----------------+--------+-------------------+
| 13 | 1 | 5600 | 11/03/19 02:21 PM |
+---------+-----------------+--------+-------------------+
| 15 | 1 | 5600 | 11/03/19 02:21 PM |
+---------+-----------------+--------+-------------------+
| 16 | 1 | 5600 | 11/03/19 02:21 PM |
+---------+-----------------+--------+-------------------+
| 16 | 2 | 6000 | 11/03/19 02:21 PM |
+---------+-----------------+--------+-------------------+
| 14 | 3 | 300 | 11/03/19 02:21 PM |
+---------+-----------------+--------+-------------------+
| 15 | 3 | 150 | 11/03/19 02:21 PM |
+---------+-----------------+--------+-------------------+
`
where user_id and auction_item_id relations to the respective entities.
The problem:
I would like to get the max bids for each user for each item and when it happened..
To the best of my knowledge this is solved via a SELECT subquery (see below) but this is not possible either in QueryBuilder or in DQL. Raw SQL would not hydrate properly my AuctionBid entity (which is a neat thing to present the results in the frontend). Anything I'm missing? Thanks a ton!
`
SELECT a.user_id,
a.auction_item_id,
maxprice,
a.date_bidded
FROM (SELECT user_id,
auction_item_id,
date_bidded,
Max(price) AS maxprice
FROM auction_bid b
GROUP BY user_id,
auction_item_id) a
INNER JOIN auction_bid c
ON c.user_id = a.user_id
AND c.auction_item_id = a.auction_item_id
AND c.price = a.maxprice
ORDER BY a.auction_item_id,
maxprice DESC,
date_bidded ASC
`
Hey christosp
That's an interesting project :)
Usually, when I got problems building a query via DQL I write it "raw" first (full SQL). Then, when I get it working as I need it I find for ways to convert it into DQL but if it's taking me too long then I just execute the raw SQL but I only fetch for ids, so then I can execute another query and I get the objects hydrated. How does it sound?
Cheers!
Hi MolloKhan
Hmm, that's a clever approach, makes perfect sense and it would work fine!
Luckily I was able to get the desired result by using NativeQuery and ResultSetMapping (after a lot of trial and error since these options sometimes are confusing), mapping the results from the raw sql query into the object.
But your solution would be a nice approach when I'll face a similar case ;)
Cheers!
Hi there
I'm trying to query tags with german Umlaute. In the web, I found a lot hints, but nothing helped me so far.
In my database, there are both tags saved as Müller and Muller and I need both different versions.
When I query for "Müller" I get the results Muller and Müller. Is there a way how I get only Müller, when I query for Müller?
I work with Symfony 3 and my database setting is like this:
doctrine:
dbal:
default_connection: default
connections:
default:
driver: pdo_mysql
host: '%database_host%'
port: '%database_port%'
dbname: '%database_name%'
user: '%database_user%'
password: '%database_password%'
charset: UTF8
Thanks a lot!
Cheers
Michael
Hey Michael K.
That's an interesting case, sadly I don't know the answer but I found this post that may help you out. https://dba.stackexchange.c...
Cheers!
Hey Chris,
Thank you for reporting it! I fixed both broken links in https://github.com/knpunive...
Cheers!
Hi there
I want to eliminate duplication for importing CSV, and Replace Into Mysql raw query kinda help that.
Is there way I can write Replace into Doctrine? If not, what is the steps to write raw query for Replace into? passing field values into Repository function and append to raw query? Not so sure, need help :)
Hey jian su
Doctrine by default doesn't have a replace function, but you can create your own DQL functions, actually this guy already done it: http://stackoverflow.com/a/...
You can read more information about creating your own DQL functions here: http://docs.doctrine-projec...
And for using them in symfony: http://symfony.com/doc/curr...
Another option is to write raw sql by using the "NativeQuery" or the "Connection" in Doctrine, and you know what, we have a excellent video that can help you getting started https://knpuniversity.com/s...
Have a nice day!
Hi Ryan! Me again. Really this site for me has become an absolutely essential tool for migrating from raw php to symfony, I've been devouring all tutorials! Not long ago I decided to move to Postgres, I fall in love with the jsonb data type and many other advanced things that I miss in MySql, so, I'm following this tutorial using Postgres 9.4. In this screen cast and in a previous one I ran into an PDO error, here goes as:
An exception occurred while executing '
SELECT SUM(fc.numberPrinted) as fortunesPrinted, AVG(fc.numberPrinted) as fortunesAverage, cat.name
FROM fortune_cookie fc
INNER JOIN category cat ON cat.id = fc.category_id
WHERE fc.category_id = :category
' with params [3]:
But, if I do something like this (I did as the error suggested), the error goes away:
SELECT SUM(fc.numberPrinted) as fortunesPrinted, AVG(fc.numberPrinted) as fortunesAverage, cat.name
FROM fortune_cookie fc
INNER JOIN category cat ON cat.id = fc.category_id
WHERE fc.category_id = 3
group by cat.name
May it be a difference with DB engine or something on Doctrine. I got something very similar on the screen cast before this one, so I changed it to:
return $this->createQueryBuilder('fc')
->andWhere('fc.category = :category')
->setParameter('category', $category)
->innerJoin('fc.category', 'cat')
->select('SUM(fc.numberPrinted) as fortunesPrinted, AVG(fc.numberPrinted) as fortunesAverage, cat.name')
->addGroupBy('cat.name')
->getQuery()
->getOneOrNullResult();
Cheers!
Hey Daniel!
So very nice of you to day - I'm thrilled you're coming from raw php to Symfony - these are skill that you will not regret :).
I am not a Postgres expert (actually, I completely think it's superior to MySQL, but still have not used it extensively), but my guess is that yes, this is probably do to a difference in the databases themselves. In the case of the query builder, usually Doctrine abstracts things away and makes queries that work on all database systems. But, as you can see, it *is* generating valid PgSQL code, but yes, it looks like you must need a GROUP BY in order to do the sum and/or average.
So, I wouldn't worry about it too much - I think you came to the right conclusion and are understanding things correctly.
Cheers!
Thanks Ryan. I am seriously interested on taking the on premise training. Is it in the US or only in France?
We do them in the US too, both in site and publicly from time to time (I give the US trainings). Send me a message - ryan@knplabs.com if you're interested :)
Hi.
I need STR_TO_DATE() function in my query but of course it doesnt work in Symfony without extra job.
I found DoctrineExtensions - https://github.com/beberlei... but i have no idea how to install it in my Symfony3 project.
Does somebody work with this extensions and know how to install it in Symfony3?
...or some other way to use STR_TO_DATE().
Hey Szymon,
Good question! Actually, I was able to find an open issue in this repo, see: https://github.com/beberlei... . So, it looks simple, you need to install this package and then register custom DQL functions you need, see docs for more information: http://symfony.com/doc/curr...
Cheers!
Hey Victor,
I have opened this issue :).
I know it looks simple and i did everything what is in the documentation but it doesn't work for me :( that's why i'm asking about how to work with this bundle. I thought it is instalation problem.
I have solved my problem - instead of STR_TO_DATE function inside query i prepared the values i need and save it in variables which i used in query.
But it would be great to have possibility to use all the functions that this bundle offer.
Hey Szymon,
Ah, ok, good thinking :) Hm, this definitely should work. Please, try this config:
doctrine:
orm:
dql:
datetime_functions:
strtodate: DoctrineExtensions\Query\Mysql\StrToDate
string_functions:
str_to_date: DoctrineExtensions\Query\Mysql\StrToDate
Probably you need to declare it twice in this way, see full config example here: https://github.com/beberlei/DoctrineExtensions/blob/master/config/mysql.yml
Cheers!
Hi Ryan,
I have this query (which is working fine).
$sql = 'select * from ps_message p1 INNER JOIN (SELECT MAX(p2.id) as max_id FROM ps_message p2 GROUP BY p2.thread_id) as max_table on p1.id = max_table.max_id';
I want to convert it for my Symfony2 project.
I have tried building the DQL query using createQueryBuilder() and Native Query with ResultSetMapping()/ResultSetMappingBuilder(), but no success yet.
P.s. With ResultSetMapping() i am unable to get the relation fields working. I have tried tinkering with addJoinedEntityResult(), etc..:
$rsm = new ResultSetMapping();
$rsm->addEntityResult('BaseBundle:PsMessage', 'p1');
$rsm->addFieldResult('p1', 'id', 'id');
$rsm->addFieldResult('p1', 'message_text', 'messageText');
$rsm->addFieldResult('p1', 'message_type', 'messageType');
$rsm->addFieldResult('p1', 'receiver_message_status', 'receiverMessageStatus');
$rsm->addFieldResult('p1', 'sender_message_status', 'senderMessageStatus');
$rsm->addFieldResult('p1', 'created_date', 'createdDate');
$rsm->addFieldResult('p1', 'modified_date', 'modifiedDate');
$rsm->addFieldResult('t1', 'thread_id', 'thread_id'); //Error
$rsm->addFieldResult('u', 'sender_id', 'sender'); //Error
$rsm->addFieldResult('t', 'thread_id', 'thread'); //Error
$rsm->addFieldResult('p2', 'thread_id', 'thread'); //Error
$sql = 'select * from ps_message p1 INNER JOIN (SELECT MAX(p2.id) as max_id FROM ps_message p2 GROUP BY p2.thread_id) as max_table on p1.id = max_table.max_id';
$query = $em->createNativeQuery($sql, $rsm);
Could you kindly guide me?
Hi Zuhayer Tahir!
I can... kind of help you :). First, unfortunately, I never use the ResultSetMapping, so I'm not sure exactly what the problem is here. The reason I don't use it is that I think it's almost never necessary: I make 99% of my queries using the normal, query builder or DQL. And for that last 1% of really complex queries, I use SQL, but don't map it to objects (this is ok!).
In this case, it looks like you're working with a bit of a complex, possibly legacy database structure. Even though I can't help, I would either continue to try with the ResultSetMapping, or try to simplify the database structure itself. It looks like you want to SELECT all messages joined to the original message from the same thread... in a more perfect world (if I understand things correctly), I would give each ps_message a foreign key to the original message in its thread (e.g. original_thread_message_id). Then, you could use a simple join instead of needing the MAX stuff.
Sorry I can't be more help! Good luck!
The project is old And I cannot alter its major structure.
The basic query is as follows:
select * from ps_message p1 GROUP BY p1.thread_id
currently it shows the msg 'first' my requirement is that it show the msg 'last'.
id msg date
1 first 2017-02-11 19:43:16
2 b 2017-02-11 19:43:16
3 c 2017-02-11 19:43:16
4 d 2017-02-11 19:43:16
5 e 2017-02-11 19:43:16
6 Last 2017-02-11 19:43:16
The ResultSetMapping() and Max() parts are used to achieve this result. I hope this simplifies it :)
Hey Zuhayer Tahir!
Hmm, can you tell me one more thing? Can you describe exactly what you're trying to accomplish (I mean, what the page looks like that you're trying to build)? Are you building a page where you show the most recent message for all threads? Or are you building a page where you are showing all messages for a single thread, in a certain order? I might be able to help if I have the end-goal in mind :).
Cheers!
It is 'Show the most recent message for all threads' most def.
This is a screen shot for the API I am working on.
https://ibin.co/3DqKwT4bRVh...
There is a getMessageListingAction() which gets all the chats that the user currently has.
Currently I am using two queries to get the results:
$messages_max = $em->createQueryBuilder()
->select('max(m.id) as id')
->from('BaseBundle:PsMessage', 'm')
->groupBy('m.thread')
->getQuery()
->getResult();
$qb = $em->createQueryBuilder()
->select('message')
->from('BaseBundle:PsMessage', 'message')
->where('message.receiver = :id')
->orWhere('message.sender = :id')
->andWhere('message.id in (:maxIds)')
->orderBy('message.modifiedDate', 'desc')
->setParameter('id', $userId)
->setParameter('maxIds', $messages_max);
But as I mentioned in my first comment this single query gets me the required result but I am unable to translate this into DQL:
$sql = 'select * from ps_message p1 INNER JOIN (SELECT MAX(p2.id) as max_id FROM ps_message p2 GROUP BY p2.thread_id) as max_table on p1.id = max_table.max_id';
Any guidance will be appreciated.
Hi Zuhayer Tahir!
Here's what I do in these situations :). I use 2 queries, just like you have. In most cases, the small performance hit for doing this (if there is one - sometimes complex queries can be even slower) is minor enough that it's not an issue. And then IF it is an issue and I decide I need a more complex query, then I might choose to use a raw SQL query so that I can make it as complex as possible. So again, in this case, I would use the 2 queries just like you did and be happy. If there were some performance problems, then I would use the raw SQL query. The downside of the raw SQL query is that you no longer get back objects... which is not awful if you need to do this in just one or two places in your code, but it is obviously not ideal :). What I might do then is, instead of SELECT * FROM... in your SQL query, I would SELECT id FROM... and then do a simple, normal query through Doctrine for just the PsMessage entities that match these ids. It's a nice way to get most of the performance benefit of a raw SQL query in a complex situation, but still get back objects.
And ultimately, is it possible to turn your query into DQL? Probably! But, it's complex enough that I wouldn't even try. I do this same thing sometimes in other places in my code (e.g. Symfony's form component, in some cases): when things get too complex, I choose a simpler solution (even if it's not as elegant).
I hope this helps!
// composer.json
{
"require": {
"php": ">=5.3.3, <7.3.0",
"symfony/symfony": "2.6.*", // v2.6.13
"doctrine/orm": "~2.2,>=2.2.3", // v2.4.8
"doctrine/doctrine-bundle": "~1.2", // 1.6.4
"twig/extensions": "~1.0", // v1.5.4
"symfony/assetic-bundle": "~2.3", // v2.8.2
"symfony/swiftmailer-bundle": "~2.3", // v2.3.12
"symfony/monolog-bundle": "~2.4", // v2.12.1
"sensio/distribution-bundle": "~3.0.12", // v3.0.36
"sensio/framework-extra-bundle": "~3.0", // v3.0.29
"incenteev/composer-parameter-handler": "~2.0", // v2.1.3
"hautelook/alice-bundle": "0.2.*" // 0.2
},
"require-dev": {
"sensio/generator-bundle": "~2.3" // v2.5.3
}
}
HI, I know this is not new, but would you do rollbacks with raw query? If you have two queries, and second one fails - hot to rollback previous query?