Custom Queries

Keep on Learning!

If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.

Start your All-Access Pass
Buy just this tutorial for $12.00

With a Subscription, click any sentence in the script to jump to that part of the video!

Login Subscribe

How do you write custom queries in Doctrine? Well, you're already familiar with writing SQL, and, yea, it is possible to write raw SQL queries with Doctrine. But, most of the time, you won't do this. Instead, because Doctrine is a library that works with many different database engines, Doctrine has its own SQL-like language called Doctrine query language, or DQL.

Fortunately, DQL looks almost exactly like SQL. Except, instead of table and column names in your query, you'll use class and property names. Again, Doctrine really wants you to pretend like there is no database, tables or columns behind the scenes. It wants you to pretend like you're saving and fetching objects and their properties.

Introducing: The Query Builder

Anyways, to write a custom query, you can either create a DQL string directly, or you can do what I usually do: use the query builder. The query builder is just an object-oriented builder that helps create a DQL string. Nothing fancy.

And there's a pretty good example right here: you can add where statements order by, limits and pretty much anything else:

... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.exampleField = :val')
->setParameter('val', $value)
->orderBy('a.id', 'ASC')
->setMaxResults(10)
->getQuery()
->getResult()
;
}
... lines 36 - 47
}

One nice thing is that you can do this all in any order - you could put the order by first, and the where statements after. The query builder doesn't care!

Oh, and see this andWhere()?

... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.exampleField = :val')
... lines 29 - 33
;
}
... lines 36 - 47
}

There is a normal where() method, but it's safe to use andWhere() even if this is the first WHERE clause. Again the query builder is smart enough to figure it out. I recommend andWhere(), because where() will remove any previous where clauses you may have added... which... can be a gotcha!

DQL - and so, the query builder - also uses prepared statements. If you're not familiar with them, it's a really simple idea: whenever you want to put a dynamic value into a query, instead of hacking it into the string with concatenation, put : and any placeholder name. Then, later, give that placeholder a value with ->setParameter():

... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.exampleField = :val')
->setParameter('val', $value)
... lines 30 - 33
;
}
... lines 36 - 47
}

This prevents SQL injection.

Writing our Custom Query

In our case, we won't need any arguments, and I'm going to simplify a bit. Let's say andWhere('a.publishedAt IS NOT NULL'):

... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.publishedAt IS NOT NULL')
... lines 29 - 31
;
}
... lines 34 - 45
}

You can totally see how close this is to normal SQL. You can even put OR statements inside the string, like a.publishedAt IS NULL OR a.publishedAt > NOW().

Oh, and what the heck does the a mean? Think of this as the table alias for Article in the query - just like how you can say SELECT a.* FROM article AS a.

It could be anything: if you used article instead, you'd just need to change all the references from a. to article..

Let's also add our orderBy(), with a.publishedAt, DESC:

... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.publishedAt IS NOT NULL')
->orderBy('a.publishedAt', 'DESC')
... lines 30 - 31
;
}
... lines 34 - 45
}

Oh, and this is a good example of how we're referencing the property name on the entity. The column name in the database is actually published_at, but we don't use that here.

Finally, let's remove the max result:

... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.publishedAt IS NOT NULL')
->orderBy('a.publishedAt', 'DESC')
... lines 30 - 31
;
}
... lines 34 - 45
}

Once you're done building your query, you always call getQuery() and then, to get the array of Article objects, getResult():

... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.publishedAt IS NOT NULL')
->orderBy('a.publishedAt', 'DESC')
->getQuery()
->getResult()
;
}
... lines 34 - 45
}

Below this method, there's an example of finding just one object:

... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 34
/*
public function findOneBySomeField($value): ?Article
{
return $this->createQueryBuilder('a')
->andWhere('a.exampleField = :val')
->setParameter('val', $value)
->getQuery()
->getOneOrNullResult()
;
}
*/
}

It's almost the same: build the query, call getQuery(), but then finish with getOneOrNullResult().

So, in all normal situations, you always call getQuery(), then you'll either call getResult() to return many rows of articles, or getOneOrNullResult() to return a single Article object. Got it?

Now that our new findAllPublishedOrderedByNewest() method is done, let's go use it in the controller: $repository->, and there it is!

... lines 1 - 15
class ArticleController extends AbstractController
{
... lines 18 - 30
public function homepage(EntityManagerInterface $em)
{
$repository = $em->getRepository(Article::class);
$articles = $repository->findAllPublishedOrderedByNewest();
... lines 35 - 38
}
... lines 40 - 79
}

Let's give it a try! Move over and, refresh! Perfect! The order is correct and the unpublished articles are gone.

Autowiring ArticleRepository

To make this even cooler, let me show you a trick. Instead of getting the entity manager and then calling getRepository() to get the ArticleRepository, you can take a shortcut: just type ArticleRepository $repository:

... lines 1 - 5
use App\Repository\ArticleRepository;
... lines 7 - 16
class ArticleController extends AbstractController
... lines 18 - 31
public function homepage(ArticleRepository $repository)
{
$articles = $repository->findAllPublishedOrderedByNewest();
... lines 35 - 38
}
... lines 40 - 79
}

This works for a simple reason: all of your repositories are automatically registered as services in the container. So you can autowire them like anything else. This is how I actually code when I need a repository.

And when we refresh, no surprise, it works!

Custom queries are a big topic, and we'll continue writing a few more here and there. But if you have something particularly challenging, check out our Go Pro with Doctrine Queries tutorial. That tutorial uses Symfony 3, but the query logic is exactly the same as in Symfony 4.

Next, I want to show you two more tricks: one for re-using query logic between multiple queries, and another super shortcut to fetch any entity with zero work.

Leave a comment!

  • 2020-06-01 Brandon Peterson

    Diego, yes sir using is empty and is not empty was it, thank you again!

  • 2020-05-28 Diego Aguiar

    So, what you have is an Order can have multiple Emails and an Email can be attached to multiple Orders, yes, it sounds like a ManyToMany relationship. In that kind of relationships Doctrine will handle the extra table internally, you won't even notice that it exists unless you check your Database.
    I'd expect what you tried to work but seems like the IS NOT NULL works a bit different that I thought. Here you can see a couple of solutions you can try (the is empty looks promising!) https://stackoverflow.com/q...
    Let me know if it worked :)

  • 2020-05-27 Brandon Peterson

    Diego,
    orderstoo is spelled right, I know it looks odd but it what I went with. I also checked case sensitivity as well and I'm good there, I used all lower case. The field is a ManyToMany relationship so I think they are both the owning side. With that said, would a join still be useful? I tried an inner join to orders_email_list but it says that it is undefined.

    What I am trying to achieve is that when a user fills out an order, they can choose to e-mail it by selecting check boxes populated from the EmailList table, or just save it in the database. What I would then like is to query for all orders that haven't been e-mailed. I used to do this by checking if that field in the database (orderstoo) was null or not. My old site used to implode an array of e-mail addresses and store them in the database orderstoo field, then explode them out to e-mail them. I didn't use a ManyToMany relationship (this is my first time with them). The more I think about it, what I'm trying to achieve probably won't work unless you have a suggestion?

  • 2020-05-27 Diego Aguiar

    Hey Brandon Peterson

    Is the orderstoo field correctly spelled? It's case sensitive. That's the first thing I'd look at
    Second: is that field a relationship? if so, check which entity has the owning side, if you're are working with the not-owning side, then I believe you have to also do a join

    I hope it helps. Cheers!

  • 2020-05-27 Brandon Peterson

    Hello, I'm using Symfony 5, and when I do:
    return $this->createQueryBuilder('o')->andWhere('o.orderstoo IS NOT NULL')
    ->getQuery()
    ->getResult()
    I get the following error:
    [Semantical Error] line
    0, col 42 near 'orderstoo IS': Error: Invalid PathExpression.
    StateFieldPathExpression or SingleValuedAssociationField expected.
    Is there something different I need to use for IS NOT NULL?

  • 2020-01-06 Dung Le

    Great support and guidance I got form SymfonyCasts courses. Thanks!

  • 2020-01-06 Victor Bocharsky

    Hey Dung,

    Yay, I really happy to hear it helps! And thank you for sharing your final solution, it might be useful for others ;)

    Cheers!

  • 2020-01-04 Dung Le

    Victor Bocharsky it does help, i got it working, thank you so much Victor, so much to learn and I will keep learning :)


    $qb = $this->createQueryBuilder('e')
    ->orderBy('e.id', 'ASC')
    ->select('e.id')
    ->setMaxResults(7)
    ->getQuery();
    $scalarResult = $qb->getScalarResult();

    $qb2 = $this->createQueryBuilder('e');
    $qb2->update()
    ->set('e.statuses', ':statuses')
    ->setParameter('statuses', 2)
    ->andWhere('e.id IN (:e_ids)')
    ->setParameter('e_ids', $scalarResult)
    ->getQuery()
    ->execute();
  • 2020-01-03 Victor Bocharsky

    Hey Dung,

    What exactly problem did you get writing that query? If you want to do low level queries using getConnection(), here's a screencast where we show how to do it in a Symfony project: https://symfonycasts.com/sc... - please, notice that we refer exact table and column names in MySQL DB, not class and property names.

    Otherwise, if you want to do this in a few queries instead of one - you just need to fetch all the IDs you need in one query, I suppose you know how to do it, it should be pretty simple. You can add "->select(estate.id)" to your query builder to specify you want to fetch only ID column, and at the end instead of "->getQuery()->getResult()" call "->getQuery()->getScalarResult()" to avoid object hydration.

    For the 2nd query, yes, use "IN (:ids)", e.g:
    ->andWhere('estate.id IN (:estate_ids)')

    ->setParameter('estate_ids', $estateIds);

    Where $estateIds is just a PHP array of IDs, like "[2,7,8,23]".

    I hope this helps!

    Cheers!

  • 2020-01-02 Dung Le

    Hello weaverryan ,

    I have been trying to write a different query that will not suffer from this problem as you suggested but failed. Again this is the query that works using

    Querying with SQL Method:


    $conn = $this->getEntityManager()->getConnection();
    $sql = 'update estate set status_id = 7 order by id desc limit 29';
    $stmt = $conn->prepare($sql);
    $stmt->execute();

    How can I do this with 2 queries using Query Builder Method?

    first query for all the "id"s ORDER BY est.id DESC LIMIT 29 and then make
    a second query to update only those ids - e.g. WHERE id IN (:ids).

    I totally understand your instruction (I tried nested queries) it is just I am not able to translate that into query builder syntax :) sorry for being a such a dense person, if you have an example course tutorial somewhere I will learn and work it out from there, please point me to?

    Thanks Ryan!

    Best regards,

  • 2019-11-19 Dung Le

    Excellent! Thank you for the SOF link, your short explanation, B) solution. I will write it in 2 queries. Appreciate your guidance!

  • 2019-11-18 Dung Le

    - for option 4 this is how I write it

    // Querying for Objects with DQL
    $query = $this->getEntityManager()->createQuery(
    'update estate e set p.status_id = 7 order by p.id desc limit 29'
    );
    $query->execute();

    - And for all options and queries I mentioned above are written in EstateRepository.php file -> ( https://uofc-my.sharepoint.... ) which is in Repository directory And the call made to them from JsonImport.php file -> ( https://uofc-my.sharepoint.... ) which is in Service directory, the call looks like this

    $this->entityManager->getRepository('App:Estate')->resetStatusQueryNoneBuilder();

    - The error message for option number 4 is

    In QueryException.php line 65:
    [Semantical Error] line 0, col 7 near 'estate e set': Error: Class 'estate' is not defined.
    In QueryException.php line 43:
    update estate e set p.status_id = 7 order by p.id desc limit 29

    I apologize if I cannot explain myself very well but please let me know your suggestion. I am confused if you can simplify or help me sort out?

    Thanks Ryan!

  • 2019-11-18 weaverryan

    Hey Dung Le!

    Hmm, ok, I think I know what's going on. Here is a Stack Overflow that talks about a similar problem but with LIMIT instead of ORDER BY: https://stackoverflow.com/q... - I think it's the same issue.

    In short, occasionally there will be an SQL feature you use that isn't supported by all database systems. In those cases, because Doctrine is meant to work the same for MySQL, Postgresql, Mssql, Oracle, etc - Doctrine chooses not to support it natively. I have a feeling that this is what's going on in your situation.

    So, my best advice is either to:

    A) Keep with the raw query
    B) Try to write a different query that will not suffer from this problem. You could do that actually with 2 queries: first query for all the "id"s ORDER BY a.id DESC LIMIT 29 and then make a second query to update only those ids - e.g. WHERE id IN (:ids).

    Let me know if this helps!

    Cheers!

  • 2019-11-18 Dung Le

    Hello weaverryan - thank you for your reply

    - yes the original native sql query does work.

    - this is the error I got from option 3:


    C:\xampp\htdocs\project>php bin/console import:data
    In QueryException.php line 54:
    [Syntax Error] line 0, col 46: Error: Expected end of string, got 'ORDER'
    In QueryException.php line 43:
    UPDATE App\Entity\Estate a SET a.statuses = 7 ORDER BY a.id DESC

    - I lost/forgot how I did option 4 but I will find it again and post back.

    What do you think of error in option 3 and what would be your query builder?

    Thanks very much!

  • 2019-11-18 weaverryan

    Hey Dung Le!

    Hmm, interesting question. The original SQL is a bit odd - I've never updated a value using an "ORDER BY"... but I guess that works :). When you try options (3) and (4), do you get an error? Does the query just not work? If it *does* work, you can use the web debug toolbar to see what query it generated. Also, I think you pasted (4) wrong - it matches (3) (it's just another query builder).

    Cheers!

  • 2019-11-15 Dung Le

    Good Morning Symfonycasts,

    I have tried to do it and tested but I still failed on a couple query syntax, I hope you can help me.

    1) Original native query: update estate set status_id = 7 order by id desc limit 29;

    2) I successfully translated it to "Querying with SQL" in my EstateRepository.php using this documentation https://symfony.com/doc/cur...$conn = $this->getEntityManager()->getConnection();
    $sql = 'update estate set status_id = 7 order by id desc limit 29';
    $stmt = $conn->prepare($sql);
    $stmt->execute();

    3) However I failed to write it using "Querying with the Query Builder" as in https://symfony.com/doc/cur...

    Here is my attemp: $this->createQueryBuilder('a')
    ->update()
    ->set('a.statuses', 7)
    ->orderBy('a.id', 'DESC')
    ->setMaxResults(30)
    ->getQuery()
    ->execute();

    4) And I failed to write it using "Doctrine Query Language" as in https://symfony.com/doc/cur...

    Here is my attemp:$this->createQuery('a')
    ->update()
    ->set('a.statuses', 7)
    ->orderBy('a.id', 'DESC')
    ->setMaxResults(30)
    ->getQuery()
    ->execute();

    My question is: would you please help me construct queries for types number 3) and 4) and where to I go for original documentation to learn from for this topic?

    Many thanks!

  • 2019-09-28 weaverryan

    Yo @Geoff!

    Yep, I know this error :). For some reason, it simply doesn’t see your custom method. This is basically a “method not found” exception. Why does the message sound so weird then? Because the repositories have a bit of magic functionality that the error is trying to advertise (and it’s confusing things): if you call findOneByFoo($bar) and that method doesn’t exist, it will magically query for one record where a “foo” property equals the passed value.

    Basically, the original error is a “method not found” error. Ignore its advice and try to figure out why the method doesn’t exist (typo? Calling method on wrong repository?).

    Cheers!

  • 2019-09-27 Geoff Maddock

    I swear I'd run into this before, but I can't for the life of me remember how it happened.
    I'm calling a custom repository method that I created, but I'm getting an error as if it does not exist.

    BadMethodCallException
    HTTP 500 Internal Server Error
    Undefined method 'findAllByCustomerOrInternal'. The method name must start with either findBy, findOneBy or countBy!

    Now, if I change the method to start with findBy, it throws a different error:
    Entity 'App\Model\Entity\Location' has no field 'customerOrInternal'. You can therefore not call 'findByCustomerOrInternal' on the entities' repository

    Which is true, but I'm not trying to filter on a single field, I'm trying to use a custom query builder.

    I'm using similar methods to the first method elsewhere in my codebase, but it's not throwing an error.

  • 2019-09-13 Vladimir Sadicov

    Sounds interesting! Does it throws an exception?

  • 2019-09-12 Brian Barrick

    Hi Vladimir, no this is the code from the code download section right out of the box. I just created a new project ran composer install and started with the tutorial. All of the class definitions should be exactly the same as the tutorial. I did however continue the video and passing the Article repository to the controller fixes it, but anyone else who comes along may run in to the same issue.

    createQueryBuilder('a')
    ->andWhere('a.publishedAt IS NOT NULL')
    ->orderBy('a.publishedAt', 'DESC')
    ->getQuery()
    ->getResult()
    ;
    }

  • 2019-09-12 Vladimir Sadicov

    hey Brian Barrick

    Is it related to course code or your personal? Could you please share your ArticleRepository definition code and Symfony version you are using?

    Cheers!

  • 2019-09-11 Brian Barrick

    I have the repositoryClass="App\Repository\ArticleRepository" annotation in the Article.php class file but it's wanting to default to the ObjectRepository. Any suggestions?

  • 2019-03-12 Lavin

    hi thank for the reply but ive done found the solution :) just put that query into querybuilder and viola its work

  • 2019-03-04 Diego Aguiar

    Hey Lavin

    I'm far from being an expert on translating a site but what I would do is to add a listener for detecting which language is chosen (parsing the URL) and storing it on a session key. Then I would create a doctrine filter for automatically inject the WHERE clause using the session.

    Cheers!

  • 2019-03-04 Lavin

    hi symfonycast i have a question maybe related to this topic,so i have product repository with function findOneBySlug($slug) it working great and fine but... a horible event happen so in the product they have 2 table with name language_id 1 for indonesia 2 for english and whenever the findOneBySlug code running this code just return the language_id 1,i want add some modification when in url has locale example /en/single-origin/kopi-arabica-aceh-gayo/reviews/create they will return view product name with language_id 2 or /single-origin/kopi-arabica-aceh-gayo/reviews/create will get language_id 1 here some code in findOneBySlug

    edit: sorry i forgot mention this i wanna add something like this query
    SELECT * FROM seo s Join language l on l.id = s.language_id WHERE s.slug = 'kopi-arabica-aceh-gayo' and s.language_id = 1;

    in findOneBySlug function

    public function findOneBySlug($slug)
    {
    $qb = $this->getQueryBuilderByEnabled();
    $query = $qb
    ->andWhere('seo.slug = :slug')
    ->andWhere('seo.relationTable = :relationTable')
    ->setParameters(['slug' => $slug, 'relationTable' => 'product'])
    ->setMaxResults(1)
    ->getQuery();

    return $query->getOneOrNullResult();
    }

  • 2018-01-06 Michael

    Hey Victor,

    Thanks a lot for your explanation! Then I try it with PHP.

    Have a goog day!
    Michael

  • 2018-01-05 Victor Bocharsky

    Hey Michael,

    Hm, tricky question :) I don't think it's possible, at least with SQL DBs. The problem is that if you have at least one string value in the column - SQL will apply string sort algorithm anyway, even if other values are integers. And even if we're talking about values that start with a letter, you will always have the order you don't want like: M1, M10, M11, M2, M21, etc. The only way I see, fetch data without sorting from SQL and then order them in PHP where you can implement whatever logic you need. :)

    Cheers!

  • 2018-01-05 Michael

    Hey Victor,

    You're right, the field was defined as a *string*, with *integer* it works perfect ;-)

    But I also need "students.number" as a string sorted like: 1,2, 12, K1, K2, K10, K11, M1, M12, M13, M21
    I found the hint that it should works with a CAST or a CONVERT function. But how can I integrate this to my query?

    Thank you in advance and have a great day!
    Michael

  • 2018-01-04 Victor Bocharsky

    Hey Michael,

    Happy new year to you too!

    Actually, you do it right. So, when you order this query you get results like "1, 12, 2, 21, 3, etc."? Sounds like you have an invalid type of "students.number" field. Please, make sure that "number" field is *integer*, not a *string* type like varchar, text, etc. Then MySQL will be able to order those numbers correctly :)

    Cheers!

  • 2018-01-03 Michael

    Dear KnpUniversity-Team,

    first of all: HAPPY NEW YEAR ;-)

    After searching for hours, I allow myself to ask the experts ;-)
    I just want to order a query for the KnpPaginator by numbers in natural (1, 2, 3, 12, 21,.. not 1, 12, 2, 21, 3)
    My query:

    $qb = $this->createQueryBuilder('students')
    ->where('students.event = :event')
    ->setParameter('event', $event)
    ->orderBy('students.number', 'ASC');

    Thanks a lot!
    Michael

  • 2017-11-29 Diego Aguiar

    Hey Cesar Delgado
    Is totally fine to do that, actually that's the way I like to do it, but you can even use a doctrine extension "TimeStampable" to achieve the same result (The good thing about this extension is that it can help you for date time fields that need to be updated based on changes to an entity)

    Extension Link: https://github.com/Atlantic...

    Cheers!

  • 2017-11-29 Cesar

    Hello. I have surrendered to the Doctrine powers because of this tutorial. One question, if I need to set a default date time value, do I need to create a construct function inside of the Entity? I need it to know at what time the user submit a form. I hope you can help me. I saw something in your track of Symphony 2 but I am not sure of follow it because I am using Symfony 3 obviously.

  • 2017-11-29 Cesar

    Hello, I have surrendered to the Doctrine powers because of this tutorial. One question, I need to have a default date time value to catch the moment when the user fill a form. I made a construct function in the Entity setting the variable to the current time. Is that correct or I am making a mistake? I will appreciate any tip or if you have more information about Doctrine.

  • 2017-07-26 Mateusz Sobczak

    Thanks much for your explanation! It helps me get a better grasp of the bigger picture. And I'll definitely use getRepository helper :-)

  • 2017-07-26 weaverryan

    Yo Mateusz Sobczak !

    Yes, very good question! Eloquent and Doctrine use the 2 different major "types" of ORM. Eloquent is called an "Active Record"... which basically means that when you query, you actually use the class (e.g. Genus). Doctrine is known as a data mapper, where your class (Genus) is just a simple, non-magic class that holds data (and then you do your queries through a different object). Which is better is a major area of debate: data mappers are "cleaner OO code", but take more work. Using static calls that an active record requires is usually not a great idea... but in practice, it usually works well enough.

    So that's the explanation about why there are two objects floating around :). In reality, I usually *do* shortcut things a bit - you can't get as short as Eloquent due to the difference above, but you can get closer. For example, if you create your own base controller (that extends Symfony's normal Controller) and add a protected function getEm(), then you could have this:


    $genuses = $this->getEm()->getRepository(Genus::class)->findAll();

    Or you could even add a protected function getRepository($class) and shorten to:


    $genuses = $this->getRepository(Genus::class)->findAll();

    That's probably about as short as you'll get it - not as short, but a lot closer :). I personally like the data mapper way of doing things because it makes sense to have one object that simply holds data, and another object that's really good at querying (separation of concerns).

    Cheers and welcome to Symfony :)

  • 2017-07-26 Mateusz Sobczak

    Thank you for the video series. It's fun to watch and very informative.

    This is my second day with Symfony. I've used Laravel for a while. I was just wondering if all this code for entity manager is necessary:

    $em = $this->getDoctrine()->getManager();
    $genuses = $em->getRepository('AppBundle:Genus')->findAll();

    I'm used to Laravel where you just type:

    $genuses = Genus::all();

    Is there a shortcut to get rid of the boilerplate code?

  • 2017-07-20 Diego Aguiar

    Hey Dennis!

    Can you show me the error message ?

    I believe you just need to change your "setParameter", instead of passing the categoryId, just pass the whole object, if your relationship is correctly setup, Doctrine already knows what to do.

    Have a nice day :)

  • 2017-07-20 Dennis

    Hi Ryan,

    How can I make a custom query for a pair of entities with manyToMany relation?

    I've got this now:

    return $this->createQueryBuilder('product')
    ->andWhere('product.category = :categoryID')
    ->setParameter('categoryID', $category->getId())
    ->getQuery()
    ->execute();

    But I get an semantical error.

  • 2017-06-27 Diego Aguiar

    Greeeeeeeeeeat!! Keep it going ;)

  • 2017-06-26 Yeison J. Espinoza

    Yes! finally! thanks! now i can continue :p

  • 2017-06-26 Diego Aguiar

    Hey Yeison J. Espinoza

    Finally I could find your problem, you are mixing your entities configuration, you have a YML file that defines your Player entity, but also, you have annotations on it, you have to decide which one you want to use, look's like symfony by default detects yml first, and you are not defining which is it's repository class, so you just have to add this into Players.orm.yml:


    AppBundle\Entity\Players:
    repositoryClass: AppBundle\Repository\PlayersRepository

    Cheers!

  • 2017-06-26 Yeison J. Espinoza

    I'm not getting a way to fix that problem, here is the whole code if some one can check and help
    ---> https://mega.nz/#!hUkijIQb!...

  • 2017-06-20 Diego Aguiar

    Hey Yeison,

    Let's figure it out why this is happening.
    First, try creating a new Entity and a new Repository, but name it in singular i.e. Player, then dump it and let's see if it works.
    If it does not work, I believe it's caused by Doctrine's cache, so run this, and try it again
    $ bin/console doctrine:cache:clear-metadata

    I hope this will do the trick

    Cheers!

  • 2017-06-20 Yeison J. Espinoza

    I did clear cache, and no, not using any cache engine

  • 2017-06-20 Victor Bocharsky

    Hey Yeison,

    Are you trying to do so in dev environment? Because for the prod one you probably need to clear the cache. Actually, I advise you to clear the cache for both dev and prod environments, or better manually remove everything in the cache folder with the next command:

    rm -rf var/cache/*

    Btw, do you use any bytecode cache engines for doctrine annotations, like OPCache or APCu? If so, you need manually clear that cache as well.

    Cheers!

  • 2017-06-20 Yeison J. Espinoza

    The same result, entitymanager :S

  • 2017-06-19 Diego Aguiar

    Hmmm, it looks good

    Could you try my response above ? https://knpuniversity.com/s...

  • 2017-06-19 Yeison J. Espinoza

    I'm in dev environment, next is the config.yml
    https://pastebin.com/Fd4C6km6

  • 2017-06-19 Diego Aguiar

    Hey Yeison,

    Try fetching the EntityManager from the Container instead of injecting it via the ParamConverter, something like this:


    // PlayerController
    public function playersAction() {
    $em = $this->getDoctrine()->getManager();
    $repository = $em->getRepository(Players::class);
    $players = $repository->findAllOrderedByName();
    }

    Cheers!

  • 2017-06-19 Diego Aguiar

    It shouldn't be the case, you are using all the standards, but let me check your config.yml file.
    Are you running in dev or prod environment ?

  • 2017-06-19 Yeison J. Espinoza

    I'm still waiting to get some help :S
    I remake the whole project and follow all steps and still getting EntityRepository instead of my custom Repository

    https://gyazo.com/942c78279...

  • 2017-06-17 Yeison J. Espinoza

    "i dont know why is not recognizing my PlayersRepository, i checked everything, maybe some config?"

  • 2017-06-16 Diego Aguiar

    Alright, so what you mean with "Not recognizing" I see in your dump that your Players entity class is been mapped, what happens when you execute

    $this->em->getRepository('AppBundle:Players')->findAllOrderedByName() 

    Also you don't have to inject the EntityManager into a Controller Method, you can gather it directly from the container


    $this->getDoctrine()->getManager();
  • 2017-06-16 Yeison J. Espinoza

    Symfony 3.3.* current
    Yes is the only dump in the code
    Yes already tried clearing cache

  • 2017-06-16 Diego Aguiar

    Hey Yeison J. Espinoza

    Your repository configuration looks good to me.

    Which version of Symfony are you using ?

    The dump that you printed is for this line ?

    $this->em->getRepository('AppBundle:Players');


    if so, it looks like you are getting the right repository and your code should just work, so I'm guessing that for any reason you are having troubles with the cache (I suppose you are running in "dev" environment), try executing $ bin/console cache:clear

    I hope it helps you, cheers!

  • 2017-06-16 Yeison J. Espinoza

    Hi, i'm doing my own project following you guide, i did try many ways but i'm still getting this:

    Dump: https://gyazo.com/9bf0e8594...
    PlayersController.php on line 28:
    EntityRepository {#347 ▼
    #_entityName: "AppBundle\Entity\Players"
    #_em: EntityManager {#457 …11}
    #_class: ClassMetadata {#369 ▶}
    }

    i dont know why is not recognizing my PlayersRepository, i checked everything, maybe some config?

    PlayersEntity: https://gyazo.com/c9f5917ac...
    PlayersRepository: https://gyazo.com/12e46654f...
    PlayersController: https://gyazo.com/26b36675a...

  • 2017-05-15 Victor Bocharsky

    Hey Cesar,

    The main advantage of ORM is that you operate objects and ORM lib like Doctrine do a lot of work for you. But yeah, it depends on your aims.

    Cheers!

  • 2017-05-13 Cesar Delgado

    Thanks Victor. I am using DBAL now and it's great. Maybe it's because I am not an expert but I couldn't find the advantages of the ORM.

  • 2017-05-03 Victor Bocharsky

    Hey Cesar,

    With Doctrine you get both ORM and DBAL. We explain ORM in this course, it's higher level and more fun. However, ORM is based on the more lower level like DBAL. Actually, you can think of Doctrine DBAL as about PDO, it's just a fancier wrapper for PDO objects. And we have a screencast about using it in your project: https://knpuniversity.com/s... . So if you don't want to use ORM yet, take a look at Doctrine DBAL instead of PDO or other plane PHP for connecting to your database. I think here's the most interesting article in docs for you: http://docs.doctrine-projec... .

    So what about your questions: yes, you can! But you even don't need to configure anything if you want to try Doctrine DBAL since it configured out-of-the-box. But if you still want to write your custom service for it - it's up to you, you can do it as well.

    Cheers!

  • 2017-05-02 Cesar

    Hi. I want to tell you that this track explains Symphony in a really friendly way.

    However, after this Doctrine course, I prefer to use plane PHP for connecting to my database (keep things simpler). But I wonder if I can take advantage of some Symfony features for this. For example, use the parameters.yml file or use a special service?

    Do you have any tutorials about this or any recommendations? I will appreciate it.

  • 2017-03-02 weaverryan

    Nice job Thomas! That is a gotcha of reverse engineering! As soon as Doctrine finds *one* metadata format, it just stops looking for the other formats. Good debugging!

  • 2017-03-02 Thomas

    Hi Ryan, thanks a lot for making this clear.

    Additional I found out why symfony/doctrine was checking the XML Files instead of my Entites ... the XML-Files was created caused by Reverse Engineering of existent database. Thats why doctrine is checking these files instead of using the annotations within entities.

    Now I ran into equal issues while using relationships. Just deleted the XML-Files and now all is fine ;-)

  • 2017-03-01 weaverryan

    Hey Thomas!

    Good find on your solution! You're getting "bit" by a bit of a bad error message. In this chapter (https://knpuniversity.com/s..., we talk about how, out-of-the-box, when you call $em->getRepository('AppBundle\Entity\Genus'), Doctrine returns a generic EntityRepository class. This has a few methods on it, like find(), findOneBy(), findAll(), etc. But as soon as you want to add your own custom methods to this, then you'll create your own repository class (AcmeRepository in your case). But, how does Doctrine know to use *your* class instead of its generic EntityRepository class? The answer: by adding (if you're using XML mapping) the repository-class option to your XML file. If you're using annotations (like we are), then we have that same config above the class (repositoryClass=".."). With this config, Doctrine now knows to use *your* class when you ask for the repository.

    So when you get the The method name must start with either findBy or findOneBy! error, it was because you were calling your custom method on the internal, EntityRepository class. Really, you should have seen a message more like Undefined method findAcmeStuff() on class EntityRepository. With this error, it would have been a bit more obvious that Doctrine was not using your class. But, the EntityRepository has a magic __call method, which allows you to make "magic" calls to it - e.g. you can say "findOneBySlug('foo')" - that method doesn't exist, but the __call method translates it into a findOneBy(['slug' => 'foo']). I never take advantage of this, but it's the reason for the error. You can see it right in the EntityRepository class: https://github.com/doctrine...

    tl;dr; You make the right change! And before this change, Doctrine wasn't using your custom class (so you got this error from the core of Doctrine).

    Let me know if this helps! And Cheers!

  • 2017-03-01 Thomas

    After some research I found the solution. Don't know why but I had to add

    repository-class="AcmeBundle\Repository\AcmeRepository"

    within the <entity>-Key to the Resources\doctrine xml-File. Why?

  • 2017-03-01 Thomas

    Hi Ryan, first of all thanks for the great tutorials. Its fun to learn with. Really great.

    In this one I am stuck. I am using symfony 3.2.4 and went trough this tutorial for creating customer queries. I've added Repository Class and ORM Notation but ... Symfony is telling me:

    The method name must start with either findBy or findOneBy!

    I am out, I have no clue anymore.

  • 2017-01-19 Pierre G.

    Dear Victor,

    I totally understand. Thank you very much for your detailed explanation!

    Cheers

    Peter

  • 2017-01-19 Victor Bocharsky

    Hey Peter,

    Doctrine model, i.e. Genus entity in our case, or any other entity - can't query the database, it's an architectural pattern of Doctrine. To query the database - you need to use entity repository, so you can some default methods in default repository like findAll(), findBy(), etc. *or* extend this default repository with your own, where you can define more custom methods like findAllPublishedOrderedBySize(), etc. Sometimes, for some simple queries, the default repository methods are enough, but if you want write complex queries, use JOIN, GROUP BY, etc. - you will need custom queries.

    Actually, you can write custom queries in controllers, but it's a bad practice, because you mix layers and also you can't reuse your custom queries in this case. So better write it in custom repositories, where you can easily reuse and make them more readable. Actually, that was the advantages of custom repositories.

    I think it's clearer for you now. If you have more questions - let us know.

    Cheers!

  • 2017-01-19 Pierre G.

    Dear Ryan, first of all thank you for your awesome tutorials, they help perfectly to get a grip on Symfony!

    I have more like architectural question regarding the custom GenusRepository you create:

    What, in your words, is the advantage of overwriting the default Repository over just defining findAllPublishedOrderedBySize() as a method of the Genus model?

    Thank you in advance and have a great day

    Peter

  • 2016-10-24 weaverryan

    Hey Terry!

    You got it then :). @ORM\Entity(..) ties the repository to the entity, and then @ORM\Table ties the entity to the table (and so, both put together effectively tie the repository to the table).

    And I *love* the way you said:

    > the query already knows to select from the GENUS Entity's table

    The "weird" (by design) thing about Doctrine sometimes is that it wants you to think in terms of entities and not worry about the database. So, your way of thinking about it here is perfect.

    Cheers!

  • 2016-10-24 Terry Caliendo

    Got it. I was focused on the table name. I knew the @ORM\Entity(...) line tied the two together (but forgot to explicitly state it in my question), and was ultimately verifying that the @ORM\Table(...) statement ultimately tied both the Entity and Repository to a particular table name.

    But at the Repository level, I hear from your answer that I don't need to worry about the table name, just that the Repository is tied to the Entity by the @ORM\Entity(...) statement and the Entity is in turn tied to the table by the @ORM\Table(...) statement.

    Thus, when you said "the query already knows to select from that table", I shouldn't have been thinking about a particular table but interpreted it as "the query already knows to select from the GENUS Entity's table (whatever name it may have).

    Thanks for the clarification.

  • 2016-10-24 weaverryan

    Hey Terry!

    You are thinking about the problem *perfectly* and are *so* close to being correct :). It knows because of the @ORM\Entity annotation on Genus (not the \Table annotation):

    /**
    * @ORM\Entity(repositoryClass="AppBundle\Repository\GenusRepository")
    * ...
    */
    class Genus

    We add this right after creating the repository class (https://knpuniversity.com/s.... So, the GenusRepository class can live anywhere, and we connect that repository class to our Genus entity with that annotation. If you take away that annotation, Doctrine will fallback and use the generic, core EntityRepository class (which is what it's been using up until this point).

    Let me know if that makes sense! I like that you're questioning it :).

    Cheers!

  • 2016-10-22 Terry Caliendo

    When you create the Repository and Subsequent GenusRepository folders, you say the names are not important. Then when you are in the findAllPublishedOrderedBySize method within GenusRepository and call the createQueryBuilder('genus') you say that because "we are in the GenusRepository, the query already knows to select from that table". How does it know? It can't be because of the repository name, because you said the name was arbitrary. There's nothing else in that file that would indicate the 'genus' table. Thus, I assume it knows because of the @ORM statement at the top of the Genus Entity @ORM\Table(name="genus"). Am I correct? If so (or even if not) I could use a bit more clarification.

  • 2016-07-08 Sergio Medina

    You're a legend mate!
    That worked just great!

    Never mind the other question just find that it was my bad, haven't defined the relationship on the entity =( It works all good now!

  • 2016-07-07 weaverryan

    Hi Sergio

    Is this a custom "formatter"? So you have something like this in your YAML: <account()>?

    My guess is that Alice actually "flushes" all of the data at once. What I mean is, even though Alice may have parsed your YAML file for the CoreBundle:Account entity, these have not actually be flushed to the database yet. So, there's nothing in the database to query.

    Typically, you relate entities by using the '@' symbol in YAML - http://knpuniversity.com/sc...

    Any reason why you can't do it that way?

    Cheers!

  • 2016-07-07 Sergio Medina

    Hi again Ryan!
    I've got an annoying problem =( I'm trying to use Fixtures + Alice and while doing simple things it works perfect, when I try to query an entity to populate other tables I get and empty array.

    I have followed the documentation and added:

    use Symfony\Component\DependencyInjection\ContainerAwareInterface;
    use Symfony\Component\DependencyInjection\ContainerInterface;

    I'm doing this:

    public function account()
    {
    $em = $this->container->get('doctrine')->getManager();

    /** @var \App\Bundle\CoreBundle\Entity\Repository\AccountRepository $account */
    $account = $em->getRepository('CoreBundle:Account')->find(1);

    return $account->getId();
    }

    But all I get is an empty array:
    array(0) {
    }

    Any ideas what may I be doing wrong? Thanks in advance for your help.

  • 2016-06-22 Victor Bocharsky

    You're welcome! Keep learning :)

  • 2016-06-22 Dominik

    thank you, Victor :)

  • 2016-06-21 Victor Bocharsky

    Hey, Dominik!

    Ah, it was a bad cache. I fixed it!

    Thank you for reporting it!

  • 2016-06-21 Dominik

    Hello Ryan!
    I got a problem - can't see any script on this page. Can you fix this, please?

  • 2016-06-01 Raphael Schubert

    Thank you Ryan! I`m not a good guy when talking about Database... maybe there is how to do what i`m doing in a best way.... But thanks... I`ll have a look in that tutorial... i need learn more also about Symfony Services... Create objects that will be shared across all the application... i`ll take a look in that tutos also... thanks

  • 2016-06-01 weaverryan

    Yo Raphael!

    For tough queries like this, where you don't expect to get back an object (but instead, an array), it's *ok* if you need to make straight SQL queries. So, I honestly wouldn't worry too much about this, as long as you're not doing too much of this stuff. Btw, even if you are writing SQL queries, I highly recommend putting this inside repository methods - like http://knpuniversity.com/sc....

    However, I will say that I don't fully understand your schema setup, but this query *does* look pretty complicated, even in SQL - it's a bit confusing to look at :). It's possible that your database schema could be setup better to make this query easier. But again, I only have a small part of the whole picture :).

    Oh, and you *could* probably write a query like this with DQL or with the query builder. I was going to try to translate it for you, but it's such an odd query (because the FROM is actually selecting a sub-query), that it's actually a bit hard for me too :).

    Cheers!

  • 2016-06-01 Raphael Schubert

    Hello Ryan! One more time i`m here...

    I`m in love with symfony... but i still have some doubts... i learned how to do some querys... But now i need perform some advanced querys...

    For sample... I did implemented this query:


    $em = $this->getDoctrine()->getManager();
    $query = "SELECT *, COUNT(1) AS count
    FROM ( SELECT * FROM visualizacoes WHERE idperfil = $idperfil ) a
    GROUP BY idusuario, idperfil, data";

    $stmt = $em->getConnection()->prepare($query);
    $stmt->execute();
    $stmt->fetchAll();

    How can i implement it with doctrine? because the result of this will be an list of how much times a people viewed an profile... but a profile can have lots of views from lots of users....

    i really wanna implement it in SF3

    Thanks!

  • 2016-05-18 weaverryan

    Hi Kosta!

    My pleasure - I'm so happy you this stuff is useful for you!

    Ok, about your situation: Do you have a ManyToOne relationship from Memo to User? It *sounds* like you a just storying the id of the user, and not using an actual Doctrine relationship. In the database, both options result in the same structure: your memo table will have a user_id column. But if you map this as a true ManyToOne relationship, then you *should* be able to say (in php( $memor->getCreatedBy()->getUsername(). Or, equivalently in Twig, memo.createdBy.username.

    If I'm right that you're not using a ManyToOne relationship, then check out: http://knpuniversity.com/sc.... But if you *do* have this relationship mapped correctly, you should be able to simply say {{ memo.createdBy.username }} to print out the username of the user who created this memo.

    Let me know if this helps!

  • 2016-05-14 Kosta Andonovski

    hey again ryan, I am so thankful for all our help and tutorials brother they are awesome! I have come to a little problem and I know the answer is simple. I have two databse tables memo and users. when a memo is made it saves the id of the user who made it. eg.1, now if I'm viewing the memo list on the html page. I can see the message and under the html colum "created by" - it will show currently the number 1. not the name of the user because the memo db table only has stored the number 1. My question is what is the best way to get all the details of that user (from the users table) so that I can display his name. Should I create a public function getAuthor() and place it inside the entity class memo.php or do I get it inside the controller for that page. Also if there is already a tutorial that covers this similar thing as it would happen all the time, can you point me to the tutorial, thank you so much for everything!

  • 2016-05-05 weaverryan

    Hi Kosta!

    Yes, really good question! There is only *one* reason I do this: PHPStorm auto-completion. Doing this doesn't change any behavior in my application. But now, when I call the findAllPublishedOrderedBySize() method, PhpStorm knows that it returns an *array* of Genus objects - the [] tells it that it's an array. If I iterate over that array, PhpStorm will auto-complete the methods on the Genus object :). So basically, I *love* autocompletion, so I do these types of things.

    Cheers!

  • 2016-05-05 Kosta Andonovski

    Hello again! Towards the end of the tutorial you replace @return mixed with @return Genus[], in the GenusRepository.php - I dont fully understand why this has been done. I understand where it automatically adds use AppBundle\Entity\Genus; Is there any other reason you did that? Thank you so much for all your help sir!

  • 2016-04-15 Neandher Carlos

    Thank you for the answer '')

  • 2016-04-15 weaverryan

    Hi there!

    You're absolutely right! The first way is taught more commonly because it's easier to be returned objects (it's nice when you're working with the same object 99% of the time, instead of an array with certain fields) *and* performance often doesn't matter. I'd recommend doing the first way *until* you find that you need to increase performance. And once you know that you need more performance, I'd also recommend using Blackfire.io to identify what's slow: you might find that the queries are not the problem anyways :).

    Cheers!

  • 2016-04-15 Neandher Carlos

    This return all object, but maybe unnecessary fields.

    $qb = $this->createQueryBuilder('cat')
    ->leftJoin('cat.fortuneCookies', 'fc')
    ->addSelect('fc');

    This return array, but only with the fields i want.

    $qb = $this->createQueryBuilder('cat')
    ->select('cat.id')
    ->addSelect('cat.name')
    ->leftJoin('cat.fortuneCookies', 'fc')
    ->addSelect('fc.id')
    ->addSelect('fc.fortune');

    I believe, about perfomance, second option is better. But many courses teach only the first option.