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.
What if we wanted to find a User by matching on the email or username columns? We would of course add a findOneByUsernameOrEmail method to UserRepository:
// src/Yoda/UserBundle/Entity/UserRepository.php
// ...
class UserRepository extends EntityRepository
{
public function findOneByUsernameOrEmail()
{
// ... todo - get your query on
}
}
To make queries, you can use an SQL-like syntax called DQL, for Doctrine query language. You can even use native SQL queries if you’re doing something really complex.
But most of the time, I recommend using the awesome query builder object. To get one, call createQueryBuilder and pass it an “alias”. Now, add the where clause with our OR logic:
// src/Yoda/UserBundle/Entity/UserRepository.php
// ...
public function findOneByUsernameOrEmail($username)
{
return $this->createQueryBuilder('u')
->andWhere('u.username = :username OR u.email = :email')
->setParameter('username', $username)
->setParameter('email', $username)
->getQuery()
->getOneOrNullResult()
;
}
The query builder has every method you’d expect, like leftJoin, orderBy and groupBy. It’s really handy.
The stuff inside andWhere looks similar to SQL except that we use “placeholders” for the two variables. Fill each of these in by calling setParameter. The reason this is separated into two steps is to avoid SQL injection attacks, which are really no fun.
To finish the query, call getQuery and then getOneOrNullResult, which, as the name sounds, will return the User object if it’s found or null if it’s not found.
Note
To learn more about the Query Builder, see doctrine-project.org: The QueryBuilder.
To try this out, let’s temporarily reuse the EventController’s indexAction. Get the UserRepository by calling getRepository on the entity manager. Remember, the argument you pass to getRepository is the entity’s “shortcut name”: the bundle name followed by the entity name:
// src/Yoda/EventBundle/Controller/EventController.php
// ...
public function indexAction()
{
$em = $this->getDoctrine()->getManager();
// temporarily abuse this controller to see if this all works
$userRepo = $em->getRepository('UserBundle:User');
// ...
}
Now that we have the UserRepository, let’s try our new method and dump the result:
public function indexAction()
{
// ...
$userRepo = $em->getRepository('UserBundle:User');
var_dump($userRepo->findOneByUsernameOrEmail('user'));die;
// ...
}
When we refresh, we see the user. If we try the email instead, we get the same result:
var_dump($userRepo->findOneByUsernameOrEmail('user@user.com'));die;
Cool! Now let’s get rid of these debug lines - I’m trying to get a working project going here people!
But this is a really common pattern we’ll see more of: use the repository in a controller to fetch objects from the database. If you need a special query, just add a new method to your repository and use it.
Hey Junaid,
Actually, where() and andWhere() are mostly same but with a subtle difference as you can guess from their names: where() will always override all the where block of your query so if you will have some logic in WHERE statement of your query and unintentionally call where() - you'll lost all our previous WHERE statements. That's why Ryan uses andWhere() to avoid this dumb misprints. It's even more useful later when you'll modify the query to add more complex logic to it and just when you'll do some refactoring. And that's it :) Doctrine is smart enough to convert andWhere() into simple where() if it contains the first WHERE statement
P.s. about wrapping your query with try-catch - only makes sense if you expects some exceptions which should be ok in some cases, otherwise you don't need it - you'll probably want to see a big error to know that's something is not good with your query ;)
Cheers!
Hey victor
Ah! My bad. I had forgotten @return User|null in the PHPDoc due to which i had to wrap it in a try catch block but now that i put it there, i dont need a try catch block anymore. I can also use andWhere() now. Great. Think the error was somehow related to missing try-catch block or PHPDoc Comment or something else. Anyways It is working now. I just need clarification on one point though. You say that if i only use a bunch of where clauses, the previous ones are gonna be overridden, can i use where and andWhere together or multiple andWhere clauses if i have multiple conditions and where would i need a simple where clause. Thanks a lot.
Cheers!
Hey Junaid Farooq
If you specify a where clause and then another one, as Victor said, the first one will be overridden, so you have to use "andWhere" to concatenate your where clauses, but if need both clauses AND & OR, it's better to write them all in a single "where", so you don't get problems of order evaluation, like this query:
SELECT * FROM table
WHERE name = 'diego AND (role = 'admin' OR role = 'super_admin') AND active = 1
Cheers!
Hey MolloKhan
Thanks for replying. You mean in the context of Doctrine this would be like:
`
return $this->createQueryBuilder('e')
->where('e.name = :name AND (e.role = :role OR e.role = :anotherRole) AND e.active = 1')
->setParameter('name', $name)
->setParameter('role', $role)
->setParameter('anotherRole', $anotherRole)
->getQuery()
->execute();
`
Please clarify.
in minute 0.44 when you use the cat alias you get the list of the
fields. I'm not getting those.:/ Any additional plugin or something
else that I have to install??? I'm using PHPStorm + Symfony and
Annotation plugin. I get everything else of autocomplete except of
those.
Hi there!
I'm not actually sure *which* plugin provides this, but I would strongly assume that it's coming from either the Symfony plugin or PHP Annotations plugin - I don't really have anything else installed. I just double-checked and I *do* still get this type of auto-completion on the latest version of PhpStorm and both plugins (I even tried it in a totally different project / entity).
If you figure out what the problem is, let me know! Sometimes, for subtle reasons, the plugin won't *always* work perfectly.
Cheers!
Thanks. I got it. If somebody has the same problem the things is you need to type the "Single Quotation mark" or "apostrophe" around the alias to acces the fields. If you dont PHPstorm assumes you are trying to acces a variable instead of the repository entity.
I am working on a project where the database being used is PostgreSQL, while the following query seems to work perfectly file on MySQL but I am struggling to get it to work on PostgreSQL.
namespace AppBundle\Repository;
use \Doctrine\ORM\EntityRepository;
class UserRepository extends EntityRepository
{
public function findByRole($role)
{
$qb = $this->_em->createQueryBuilder();
$qb->select('u')
->from($this->_entityName, 'u')
->where('u.roles LIKE :roles')
->setParameter('roles', '%"'.$role.'"%');
return $qb->getQuery()->getResult();
}
}
The error I get is as following
An exception occurred while executing 'SELECT p0_.id AS id_0, p0_.username AS username_1, p0_.password AS password_2, p0_.is_active AS is_active_3, p0_.roles AS roles_4, p0_.name AS name_5, p0_.street AS street_6, p0_.city AS city_7, p0_.state AS state_8, p0_.zip_code AS zip_code_9, p0_.phone_number AS phone_number_10, p0_.dob AS dob_11, p0_.company_name AS company_name_12, p0_.company_slug AS company_slug_13, p0_.company_logo AS company_logo_14, p0_.company_details AS company_details_15, p0_.stripe_customer_id AS stripe_customer_id_16, p0_.created_at AS created_at_17, p0_.updated_at AS updated_at_18 FROM px_user p0_ WHERE p0_.roles LIKE ?' with params ["%\"ROLE_EMPLOYER\"%"]:
SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: json ~~ unknown LINE 1: ...at AS updated_at_18 FROM px_user p0_ WHERE p0_.roles LIKE $1 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
I will really appreciate if I can get some understanding on why this is failing?
Hey Shairyar,
What type of `u.roles` column is exactly in PostgreSQL? Could you double check it's really a string field?
Cheers!
Hi,
The field type is json_array and the role in database is saved as ["ROLE_USER","ROLE_EMPLOYER"]
Hey Shairyar!
I'm not completely sure, but I believe the issue is that Postgres (being a pretty smart database) has a special JSON field type - see details here: http://stackoverflow.com/qu.... So, with MySQL, this is just saved in a string field where the contents are JSON (and the LIKE query works). But Postgresql I believe puts it into an actual, native JSON field. This is actually good, as I believe they more natively support searching on the contents of that field, but I'm not exactly sure how that query would look. The StackOverflow has a few links that might contain some things to try out.
Let us know if this helps point you in the right direction!
Cheers!
In the start of the video, you were creating a queryBuilder with the "cat" alias, how it's possible to make alias of tables ?
Hey Hakim,
Actually, it's a core SQL feature, you can use `AS` keyword to make alias for table or field, but also you can omit it. Check the wiki page about SQL aliases: https://en.wikipedia.org/wi...
Cheers!
Yes i khnow, but my question is about when or where in the project we setup the alias of catecory :(
Ah I see.. So this alias is arbitrary and it's set for the first time when you call "createQueryBuilder()" if you use Doctrine query builder, e.g. "$this->createQueryBuilder('cat')", where `cat` is the alias of table for Category entity (because you're calling this method from CategoryRepository). If you use DQL, you'll need to specify alias right after the table name like "SELECT cat FROM AppBundle\Entity\Category AS cat". Does it answer your question?
Cheers!
it make sens... so correct me if im wrong, so we can call is $this->createQueryBuilder('gory') if we want, and work with it ?
Yes! Then `gory` will be an alias of that table, in which entity repository you call this "createQueryBuilder('gory')" method, i.e. if you call "$this->createQueryBuilder('gory')" inside the CategoryRepository, then `gory` will be an alias of category table ;)
Cheers!
// composer.json
{
"require": {
"php": ">=5.3.3",
"symfony/symfony": "~2.4", // v2.4.2
"doctrine/orm": "~2.2,>=2.2.3", // v2.4.2
"doctrine/doctrine-bundle": "~1.2", // v1.2.0
"twig/extensions": "~1.0", // v1.0.1
"symfony/assetic-bundle": "~2.3", // v2.3.0
"symfony/swiftmailer-bundle": "~2.3", // v2.3.5
"symfony/monolog-bundle": "~2.4", // v2.5.0
"sensio/distribution-bundle": "~2.3", // v2.3.4
"sensio/framework-extra-bundle": "~3.0", // v3.0.0
"sensio/generator-bundle": "~2.3", // v2.3.4
"incenteev/composer-parameter-handler": "~2.0", // v2.1.0
"doctrine/doctrine-fixtures-bundle": "~2.2.0", // v2.2.0
"ircmaxell/password-compat": "~1.0.3", // 1.0.3
"phpunit/phpunit": "~4.1" // 4.1.0
}
}
Hello,
For me the following clause worked as is there in the symfony docs:
public function findOneByUsernameOrEmail($username)
..........
->where('u.username = :username OR u.email = :email')
...........
instead of:
..........
->andWhere('u.username = :username OR u.email = :email')
.......
as you are using here. The reason for this maybe, it is expecting another clause before it if i put `andWhere()` and hence it doesnt work as there isnt any other clause. Besides I had to wrap the query builder in a try catch block which you clearly dont do. Can you please the reason behind this. Thanks in advance.