If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.
The most common thing to do in a query is to add a WHERE clause. Unfortunately, Doctrine doesn't support that. I'm kidding!
I have a search box - let's search for "Lucky Number". This isn't hooked
up yet, but it adds a query parameter ?q=lucky+number
. Let's use that to
only return categories matching that.
Back in FortuneController
, add a Request $request
argument to the controller.
Below, let's look to see if there is a q
query parameter on the URL or not.
If there is, we'll search for it, otherwise, we'll keep finding all the categories.
For the search, call a new method on the repository called search()
, and
pass in the term.
... lines 1 - 7 | |
use Symfony\Component\HttpFoundation\Request; | |
... lines 9 - 14 | |
public function homepageAction(Request $request) | |
{ | |
... lines 17 - 20 | |
$search = $request->query->get('q'); | |
if ($search) { | |
$categories = $categoryRepository->search($search); | |
} else { | |
$categories = $categoryRepository->findAllOrdered(); | |
} | |
... lines 27 - 30 | |
} | |
... lines 32 - 53 |
Back in CategoryRepository
, let's make that function:
... lines 1 - 12 | |
class CategoryRepository extends EntityRepository | |
{ | |
... lines 15 - 23 | |
public function search($term) | |
{ | |
... lines 26 - 30 | |
} | |
} |
We'll make a QueryBuilder just like before, but do the entire query in just
one statement. Start by calling createQueryBuilder()
and passing it cat
:
... lines 1 - 12 | |
class CategoryRepository extends EntityRepository | |
{ | |
... lines 15 - 23 | |
public function search($term) | |
{ | |
return $this->createQueryBuilder('cat') | |
... lines 27 - 30 | |
} | |
} |
The only thing our query needs is a WHERE clause to match the term to the
name
of the Category. Let's chain!
Use a function called andWhere()
. Don't worry - Doctrine won't add an
AND
to the query, unless it's needed. Inside, write cat.name =
. But
instead of passing the variable directly into the string like this, use a
placeholder. Type colon, then make up a term. On the next line, use setParameter
to tell Doctrine what I want to fill in for that term. So type searchTerm
,
should be replaced with $term
. This avoids SQL injection attacks, so don't
muck it up! Finally, we call getQuery()
- like before - and execute()
:
... lines 1 - 23 | |
public function search($term) | |
{ | |
return $this->createQueryBuilder('cat') | |
->andWhere('cat.name = :searchTerm') | |
->setParameter('searchTerm', $term) | |
->getQuery() | |
->execute(); | |
} | |
... lines 32 - 33 |
And just like that, we should be able to go back, refresh, and there's our "Lucky Number" category match. And on the homepage, we still see everything.
But if we just search for "Lucky", we get nothing back because we're doing
an exact match. But just like with normal SQL, we know that's easy to fix.
And you already know how: just change =
to LIKE
- just like SQL!
It's just like writing SQL people! For the parameter value, surround it by percent signs to complete things. Refresh! We've got a match!
... lines 1 - 23 | |
public function search($term) | |
{ | |
return $this->createQueryBuilder('cat') | |
->andWhere('cat.name LIKE :searchTerm') | |
->setParameter('searchTerm', '%'.$term.'%') | |
->getQuery() | |
->execute(); | |
} | |
... lines 32 - 33 |
What about adding an OR WHERE to the query? The Category
entity has an
iconKey
property, which is where we get this little bug icon. For "Lucky Number",
it's set to fa-bug
from Font Awesome. Search for that. No results of course!
Let's update our query to match on the name
OR iconKey
property. If you're
guessing that there's an orWhere()
method, you're right! If you're guessing
that I'm going to use it, you're wrong!
The string inside of the andWhere
is a mini-DQL expression. So you can
add OR cat.iconKey LIKE :searchTerm
:
... lines 1 - 23 | |
public function search($term) | |
{ | |
return $this->createQueryBuilder('cat') | |
->andWhere('cat.name LIKE :searchTerm OR cat.iconKey LIKE :searchTerm') | |
->setParameter('searchTerm', '%'.$term.'%') | |
->getQuery() | |
->execute(); | |
} | |
... lines 32 - 33 |
And the searchTerm
placeholder is already being filled in:
Refresh! Another match!
So even though there is an orWhere()
function, don't use it - it can cause
WTF moments. Imagine if Category had an enabled
property, and we built
a query like this:
$this->createQueryBuilder('cat')
->andWhere('cat.name LIKE :searchTerm')
->orWhere('cat.iconKey LIKE :searchTerm')
->andWhere('cat.enabled = :enabled')
->setParameter('searchTerm', '%'.$term.'%')
->setParameter('enabled', true)
->getQuery()
->execute();
What would the SQL look like for this? Would it have the three WHERE clauses in a row, or would it correctly surround the first two with parentheses?
SELECT * FROM category WHERE
name LIKE '%lucky%' OR iconKey LIKE '%lucky%' AND enabled = 1;
SELECT * FROM category WHERE
(name LIKE '%lucky%' OR iconKey LIKE '%lucky%') AND enabled = 1;
Doctrine does the second and the query works as expected. But it's a lot
less clear to read. Instead, think of each andWhere()
as being surrounded
by its own parentheses, and put OR statements in there.
Oh, and there's also a where()
function. Don't use it either - it removes
any previous WHERE clauses on the query, which you might be doing accidentally.
In other words, always use andWhere()
, it keeps life simple.
// 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
}
}