Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine
TRACK

Symfony 5 >

COURSE

Doctrine, Symfony & the Database

Buy Access to Course

Custom Repository Class

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
Previous Chapter

Next Chapter
Edit on GitHub

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

Login Subscribe

Now that the show page is working, let's bring the homepage to life! This time, instead of querying for one Question object, we want to query for all of them.

findAll() for All Data

Head over to QuestionController and scroll up to homepage(). Ok, to fetch data, we need to autowire the entity manager with EntityManagerInterface $entityManager.

100 lines src/Controller/QuestionController.php
... lines 1 - 12
class QuestionController extends AbstractController
{
... lines 15 - 27
public function homepage(EntityManagerInterface $entityManager)
{
... lines 30 - 34
}
... lines 36 - 98
}

Now add $repository = $entityManager->getRepository(Question::class).

100 lines src/Controller/QuestionController.php
... lines 1 - 12
class QuestionController extends AbstractController
{
... lines 15 - 27
public function homepage(EntityManagerInterface $entityManager)
{
$repository = $entityManager->getRepository(Question::class);
... lines 31 - 34
}
... lines 36 - 98
}

And finally, $questions = $repository->findAll(). Let's dd($questions) to see what these look like.

100 lines src/Controller/QuestionController.php
... lines 1 - 12
class QuestionController extends AbstractController
{
... lines 15 - 27
public function homepage(EntityManagerInterface $entityManager)
{
$repository = $entityManager->getRepository(Question::class);
$questions = $repository->findAll();
dd($questions);
... lines 33 - 34
}
... lines 36 - 98
}

Rendering all the Questions

Ok, refresh the homepage. There we go! 12 Question objects for the 12 rows in my table. Now we're dangerous because we can pass these into our template. Add a second argument to render() - an array - to pass a questions variable set to our array of Question objects.

101 lines src/Controller/QuestionController.php
... lines 1 - 12
class QuestionController extends AbstractController
{
... lines 15 - 27
public function homepage(EntityManagerInterface $entityManager)
{
$repository = $entityManager->getRepository(Question::class);
$questions = $repository->findAll();
return $this->render('question/homepage.html.twig', [
'questions' => $questions,
]);
}
... lines 37 - 99
}

Pop open the template: templates/question/homepage.html.twig. Let's see: the homepage currently has two hard coded questions. I want to loop right inside the row: {% for question in questions %}. Trace the markup down to see where this ends and... add {% endfor %}. Delete the 2nd hard-coded question completely.

50 lines templates/question/homepage.html.twig
... lines 1 - 9
<div class="container">
... lines 11 - 15
<div class="row">
{% for question in questions %}
<div class="col-12 mb-3">
... lines 19 - 43
</div>
{% endfor %}
</div>
</div>
... lines 48 - 50

Perfect. Now it's just like the show page because we have a question variable. The first thing to update is the question name - {{ question.name }} and the slug also needs to be dynamic: question.slug.

50 lines templates/question/homepage.html.twig
... lines 1 - 9
<div class="container">
... lines 11 - 15
<div class="row">
{% for question in questions %}
<div class="col-12 mb-3">
<div style="box-shadow: 2px 3px 9px 4px rgba(0,0,0,0.04);">
<div class="q-container p-4">
<div class="row">
... lines 22 - 27
<div class="col">
<a class="q-title" href="{{ path('app_question_show', { slug: question.slug }) }}"><h2>{{ question.name }}</h2></a>
... lines 30 - 34
</div>
</div>
</div>
... lines 38 - 42
</div>
</div>
{% endfor %}
</div>
</div>
... lines 48 - 50

Below, for the question text, use {{ question.question|parse_markdown }}. We might also want to only show some of the question on the page - we could do that by adding a new method - like getQuestionPreview() to the entity - and using it here. We'll see this idea of custom entity methods later.

50 lines templates/question/homepage.html.twig
... lines 1 - 9
<div class="container">
... lines 11 - 15
<div class="row">
{% for question in questions %}
<div class="col-12 mb-3">
<div style="box-shadow: 2px 3px 9px 4px rgba(0,0,0,0.04);">
<div class="q-container p-4">
<div class="row">
... lines 22 - 27
<div class="col">
<a class="q-title" href="{{ path('app_question_show', { slug: question.slug }) }}"><h2>{{ question.name }}</h2></a>
<div class="q-display p-3">
<i class="fa fa-quote-left mr-3"></i>
<p class="d-inline">{{ question.question|parse_markdown }}</p>
<p class="pt-4"><strong>--Tisha</strong></p>
</div>
</div>
</div>
</div>
... lines 38 - 42
</div>
</div>
{% endfor %}
</div>
</div>
... lines 48 - 50

At the bottom, there's one more link: question.slug.

50 lines templates/question/homepage.html.twig
... lines 1 - 9
<div class="container">
... lines 11 - 15
<div class="row">
{% for question in questions %}
<div class="col-12 mb-3">
<div style="box-shadow: 2px 3px 9px 4px rgba(0,0,0,0.04);">
<div class="q-container p-4">
<div class="row">
... lines 22 - 27
<div class="col">
<a class="q-title" href="{{ path('app_question_show', { slug: question.slug }) }}"><h2>{{ question.name }}</h2></a>
<div class="q-display p-3">
<i class="fa fa-quote-left mr-3"></i>
<p class="d-inline">{{ question.question|parse_markdown }}</p>
<p class="pt-4"><strong>--Tisha</strong></p>
</div>
</div>
</div>
</div>
<a class="answer-link" href="{{ path('app_question_show', { slug: question.slug }) }}" style="color: #fff;">
... lines 39 - 41
</a>
</div>
</div>
{% endfor %}
</div>
</div>
... lines 48 - 50

Done! Doctrine makes it easy to query for data and Twig makes it easy to render. Go team! At the browser, refresh and... cool!

Ordering the Data

Each question has a random askedAt date - you can see it by clicking into each one. What we probably want to do is put the newest questions on top. In other words, we want to do the same query but with ORDER BY askedAt DESC.

If you click the database icon on the web debug toolbar, you can see that the query doesn't have an ORDER BY yet. When you're working with the built-in methods on the repository class, you're a bit limited - there are many custom things that these methods simply can't do. For example, findAll() doesn't have any arguments: there's no way to customize the order or anything else. Soon we'll learn how to write custom queries so we can do whatever we want.

But, in this case, there is another method that can help: findBy(). Pass this an empty array - we don't need any WHERE statements - and then another array with 'askedAt' => 'DESC'.

101 lines src/Controller/QuestionController.php
... lines 1 - 12
class QuestionController extends AbstractController
{
... lines 15 - 27
public function homepage(EntityManagerInterface $entityManager)
{
... line 30
$questions = $repository->findBy([], ['askedAt' => 'DESC']);
... lines 32 - 35
}
... lines 37 - 99
}

Let's try it! Refresh! And... click the first: 10 days ago. Click the second: 1 month ago! I think we got it! If we jump into the profiler... yes! It has ORDER BY asked_at DESC.

We've now pushed the built-in repository methods about as far as they can go.

EntityRepository

Question time: when we call getRepository(), what does that actually return? It's an object of course, but what type of object? The answer is: EntityRepository.

In PhpStorm, I'll press Shift+Shift and type EntityRepository.php. I want to see what this looks like. Make sure to include all "non project items". Here it is!

EntityRepository lives deep down inside of Doctrine and it is where the methods we've been using live, like find(), findAll(), findBy(), findOneBy() and some more.

Our Custom Repository Class

But check this out: in the controller, dd($repository).

102 lines src/Controller/QuestionController.php
... lines 1 - 12
class QuestionController extends AbstractController
{
... lines 15 - 27
public function homepage(EntityManagerInterface $entityManager)
{
$repository = $entityManager->getRepository(Question::class);
dd($repository);
... lines 32 - 36
}
... lines 38 - 100
}

When we refresh... surprise! I lied! Sort of...

Instead of being an instance of EntityRepository - like I promised - this is an instance of App\Repository\QuestionRepository. Hey! That's a class that lives in our project! Open it up: src/Repository/QuestionRepository.php.

51 lines src/Repository/QuestionRepository.php
... lines 1 - 2
namespace App\Repository;
use App\Entity\Question;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
/**
* @method Question|null find($id, $lockMode = null, $lockVersion = null)
* @method Question|null findOneBy(array $criteria, array $orderBy = null)
* @method Question[] findAll()
* @method Question[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class QuestionRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Question::class);
}
... lines 21 - 49
}

When we originally ran make:entity to generate Question, it actually generated two classes: Question and QuestionRepository. This class extends another called ServiceEntityRepository. And if you hold Command or Ctrl and click into it, that class extends EntityRepository! The class we were just looking at.

When we ask for the repository for the Question entity, Doctrine actually returns a QuestionRepository object. But since that ultimately extends EntityRepository, we have access to all the helper methods like findAll() and findBy().

But... how does Doctrine knows to give us an instance of this class? How does it connect the Question entity to the QuestionRepository class? Is it relying on a naming convention?

Nope! The answer lives at the top of the Question class: we have @ORM\Entity() with repositoryClass=QuestionRepository::class. This was generated for us by make:entity.

93 lines src/Entity/Question.php
... lines 1 - 7
/**
* @ORM\Entity(repositoryClass=QuestionRepository::class)
*/
class Question
{
... lines 13 - 91
}

Here's the big picture: when we call getRepository() and pass it Question::class, Doctrine will give us an instance of QuestionRepository. And because that extends EntityRepository, we get access to the shortcut methods!

Custom Repository Methods

The reason this is cool is that anytime we need to write a custom query for the Question entity, we can add a new method inside of QuestionRepository.

The class already has an example: uncomment the findByExampleField() method. If I have a findByExampleField() method in the repository, it means that we can call this from the controller.

49 lines src/Repository/QuestionRepository.php
... lines 1 - 14
class QuestionRepository extends ServiceEntityRepository
{
... lines 17 - 21
/**
* @return Question[] Returns an array of Question objects
*/
public function findByExampleField($value)
{
return $this->createQueryBuilder('q')
->andWhere('q.exampleField = :val')
->setParameter('val', $value)
->orderBy('q.id', 'ASC')
->setMaxResults(10)
->getQuery()
->getResult()
;
}
... lines 36 - 47
}

In a few minutes, we're going to write a custom query that finds all questions WHERE askedAt IS NOT NULL. In QuestionRepository, let's create a method to hold this. How about: findAllAskedOrderedByNewest() and this won't need any arguments.

49 lines src/Repository/QuestionRepository.php
... lines 1 - 14
class QuestionRepository extends ServiceEntityRepository
{
... lines 17 - 24
public function findAllAskedOrderedByNewest()
{
... lines 27 - 34
}
... lines 36 - 47
}

In the controller, remove the dd() and say $questions = $repository->findAllAskedOrderedByNewest().

101 lines src/Controller/QuestionController.php
... lines 1 - 12
class QuestionController extends AbstractController
{
... lines 15 - 27
public function homepage(EntityManagerInterface $entityManager)
{
$repository = $entityManager->getRepository(Question::class);
$questions = $repository->findAllAskedOrderedByNewest();
... lines 32 - 35
}
... lines 37 - 99
}

Of course, that won't work yet because the logic is all wrong, but it will call the new method.

Next, let's learn about DQL and the query builder. Then, we'll create a custom query that will return the exact results we want.

Leave a comment!

Most Useful Newest Oldest
46
Login or Register to join the conversation
jlchafardet Avatar

As a thank for all the help, let me throw a little love back into the community.

This is a quick and dirty "next/previous" link navigation generator to allow the users to browse the records from within the records themselves(next, previous link).

In the repository add this code (modify it to your needs, I'm using a.accountId because what I will be navigating are "accounts", with next and previous links.


    public function findNextPrevious($id, $like)
    {
        $expr = $this->_em->getExpressionBuilder();
        $next = $this->createQueryBuilder('a')
            ->select($expr->min('a.accountId'))
            ->where($expr->gt('a.id', ':id'))
            ->andWhere('a.accountId LIKE :like');
        $previous = $this->createQueryBuilder('b')
            ->select($expr->max('b.accountId'))
            ->where($expr->lt('b.id', ':id'))
            ->andWhere('b.accountId LIKE :like');
        $query = $this->createQueryBuilder('s')
            ->select('COUNT(s.id) as total')
            ->addSelect('(' . $previous->getDQL() . ') as previous')
            ->addSelect('(' . $next->getDQL() . ') as next')
            ->setParameter('id', $id)
            ->setParameter('like', '%' . $like . '%')
            ->getQuery();
        return $query->getSingleResult();
    }

in the show method:


    $repo = $em->getRepository(Account::class);
    $id = $account->getId();
    $match = preg_replace('/[0-9]+/', '', $account->getAccountId());
    
    return $this->render('view-file-twig', [
        //other vars
        'nav' => $repo->findNextPrevious($id, $match),
    ]);

and in the view wherever you want them.


    {% if nav.previous %}
        &lt;a href="{{ path('your-route-name', {'accountId': nav.previous }) }}"
	    class="btn btn-sm btn-primary"&gt;
	     &lt;i class="fas fa-arrow-alt-circle-left"&gt; Previous &lt;/a&gt;
    {% endif %}
    {% if nav.next %}
         &lt;a href="{{ path('your-route-name', {'accountId': nav.next}) }}"
	    class="btn btn-sm btn-primary"&gt;
	     &lt;i class="fas fa-arrow-alt-circle-right"&gt;&lt;/i&gt; Next &lt;/a&gt;
    {% endif %}
1 Reply
MolloKhan Avatar

Thanks for sharing it with others!

1 Reply
jlchafardet Avatar

easier to access and read
https://gist.github.com/jlc...

1 Reply
jlchafardet Avatar

heya there how are you doing?
How's it going @weaverryan!

I've got myself into another issue that I've been googling without much luck.

I need a findOneBy with OR statement, what I've got, so far required me to add a repository method, so I did this.

`
public function findByDiscordOrWallet($wallet, $discord)

{
    return $this->createQueryBuilder('s')
        ->where('s.depositWalletAddress = :wallet')
        ->orWhere('s.discordId = :discord')
        ->setParameter('wallet', $wallet)
        ->setParameter('discord', $discord)
        ->getQuery()
        ->getResult();
}

`

What I'm trying to do is I've got a series of csv files that will be uploaded and parsed by the app every so often, so I want to make sure, that in the files, whenever a matching case occurs, it just skips that row.

so I have a simple condition
loop through file, every line it runs:
<br />$notaMember = $repo->findByDiscordOrWallet($data[1], $data[2]);<br />if (empty($notaScholar)) {...}<br />

if $notaMember gets a result, its skipped, if not executes some code. simple actually, but the query doesnt return squat. even when I tried with a simple csv file with 1 existing member and 1 that is not.

any pointers on this issue?

1 Reply
jlchafardet Avatar

found it :D it was me who was sending the data in the wrong order, it should have been 2 then 1.

1 Reply
MattWelander Avatar
MattWelander Avatar MattWelander | posted 7 months ago

Hi!
I'm trying to use one of the default repository methods to sort some data alphabetically, my problem is that Å and Ä get interpreted as A, and Ö gets interpreted as O when doctrine is sorting. The letters Å, Ä and Ö actually should go after Z, at the end of the alphabet.

Do I need to write a custom sorting function for this to work? Or can I set a locale for doctrine?

The app's locale is set like this in services.yaml:

parameters:
    locale: sv-SE

The current doctrine config:

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'

        # IMPORTANT: You MUST configure your server version,

    # either here or in the DATABASE_URL env var (see .env file)
    # I WILL SET IT IN THE CONNECTION STRING SINCE I HAVE MySQL in dev and MariaDB in dev./Mattias
    #server_version: '13'
orm:
    auto_generate_proxy_classes: true
    naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
    auto_mapping: true
    mappings:
        App:
            is_bundle: false
            dir: '%kernel.project_dir%/src/Entity'
            prefix: 'App\Entity'
            alias: App

/**
 * Lists all aCRGroup entities.
 *
 */
public function indexAction()
{
	$em = $this->em;

	$aCRGroups = $em->getRepository('App\Entity\ACRGroup')->findBy(array(), array('name' => 'ASC'));
	
	return $this->render('acrgroup/index.html.twig', array(
		'aCRGroups' => $aCRGroups,
	));
}
Reply
sadikoff Avatar

Hi!

Pretty complex issue, of course, it's good that you configured the locale in services... however, you need to double-check that it was set correctly on the PHP level, and your app works in this locale. Next step DB: You must set proper charset and collation to the table and fields. If it's a new project then you can configure Doctrine to use the charset you need for example utf8mb4_unicode_ci or utf8mb4_swedish_ci. It can be done depending on your configuration, inside config/packages/doctrine.yaml or in .env files.

.env way is

DATABASE_URL="mysql://app:!ChangeMe!@127.0.0.1:3306/app?serverVersion=8&charset=utf8mb4_unicode_ci"

config/packages/doctrine.yaml way

doctrine:
    dbal:
        charset: utf8mb4
        default_table_options:
            charset: utf8mb4
            collate: utf8mb4_unicode_ci

Then you will need to re-build the database and double-check it with PHPMyAdmin or another tool that everything is configured. If you can't re-build it, then you will need to tweak it with migrations or manually, no data should be corrupted, so it's pretty safe process.

Have a good coding, Cheers!

1 Reply
Patryk W. Avatar
Patryk W. Avatar Patryk W. | posted 1 year ago | edited

there, I do not know where should I ask this question
so feel free to move it to "the better place".

Let's assume we are designing the new invoicing system.
That software has the invoice table with the status column.
The codebase will quickly become HUGE and multiple teams will be working on it,
which basically means we do not know if or where the status
column is being used. We either do not want to remove during
refactoring the status field from for example customer.

Have you got any tips on how to find out if the status column
from Invoice table is being used, and in which file/line?

When using Propel, when creating a new table we were just defining inside for example InvoiceTableMap:
`
/**

  • the column name for the status field
    */
    const COL_STATUS = 'invoice.status';
    `

and then we will be able to see (by finding usages) if it is used or not.

Unfortunately, Doctrine does not have TableMap like Propel and
for example in filterBy you are using words instead of consts which looks like a big issue.

Reply
MolloKhan Avatar

Hey Patryk W.

That's a good question. I'm not sure if MySql/Doctrine has a feature for that purpose, so, what I think you can do is to use git grep across your project (or projects that uses the same database) to find out where it's been used the status field of your invoice table
It'd look something like this


git grep '->getStatus();```

Depending on your code base you'll need to make smarter the search logic, or you may need to use a Regex

Cheers!
Reply
Dima Avatar

Hey guys! After editing the homepage template the 'askedAt' marks on the questions pages turned to unpublished. Any ideas why this happened? I made all the steps twice, it doesn't work

Reply
weaverryan Avatar

Hey Dima!

Hmm, let's see. Obviously, if you only modified the homepage template, that "shouldn't" affect anything over on the show.html.twig template... so that's a mystery. If you look in the database, do all of your questions have a "null" askedAt suddenly? I'm trying to see if there is some sort of display bug in the show template, or if the underlying data in the database for asked_at has all be set to null.

Cheers!

Reply
jlchafardet Avatar

hey guys! its me again :D

Im here this time because I have a question.

I want to have a custom find method in the repository to return me only results where the relationship return count is less than 3.


    public function findAllWithoutAssets($manager)
    {
        return $this->createQueryBuilder('sa')
            ->where('sa.id = :manager')
            ->andWhere('sa.assets is EMPTY')
            ->setParameter('manager', $manager)
            ->getQuery()
            ->getResult();
    }

right now I'm trying "EMPTY" it still returns me no records, where as a fact I know there are, I just want to avoid going through records with 3 or more assets as those are far and inbetween and can be called upon manually fairly simply.

the issue is that the API im calling to with those results its very picky and restrictive, so I want to avoid going through accounts that wont need it, as the minimum asset per account is 3, but some might have errored when they were imported (not under my control), so I'm building a custom call to "make sure" that all accounts that belong to X manager have at least 3 assets. as again those with more than 3 are not over 10 and the api call can be invoked indifidually from their own view.

is there a way to do the
'sa.assets < 3' or something?

Reply
MolloKhan Avatar

Hey jlchafardet

It seems to me what you are looking for is using COUNT with a HAVING clause
You can see an example here: https://www.w3resource.com/sql/aggregate-functions/count-having.php

My recommendation is to write first a query that will fetch you all records with their relationship, and then, filter out the result using the clauses I mentioned above

Cheers!

Reply
jlchafardet Avatar

Very interesting. will give it a try!

1 Reply
jlchafardet Avatar

Hey guys! guess who is back for more questions?

haha, thanks a lot for all the help so far :D it's really appreciated :D.

Now, lets get to it.

as you remember (or may not), I have an employees table, where I store their data, yada yada, boring.

I want to build a query in its custom repository, with a group within a group!

lolwut? whattayawan? yes, I want a single query, where my employees are grouped by the "year" they were hired, and then within that group, group them again by the "month" they were hired

but why on earth do you need this? well they asked me for it. they want to have both the data structured that way, and also a count of the results (yes 2 sets of the same data, but to be used in different processes, one is for listing, the other is for a chart where they want to display the employees they hired grouped by "month" within the "year" they were hired.

so the keys are e.startDate that is in "DATE" format (yes not datetime, but just DATE).

The query needs to as said group by MONTH(e.startDate), YEAR(e.startDate) or something.

I've tried it though and it just keeps whining and whining about any and all kinds of issues with the query.

After googling for a very long while, I get I need some additional Doctrine Extension, yet the question really lies in, do I really need them? if so, whicn one would you recommend?

Code:


public function findAllGroupByDate()
    {
        return $this->createQueryBuilder('s')
            ->where('s.isFired = false')
            ->groupBy('YEAR(s.startDate)')
            ->addGroupBy('MONTH(s.startDate)')
            ->getQuery()
            ->getResult();
    }
Reply
jlchafardet Avatar

went ahead of myself and installed the berbeley extensions.

doctrine.yaml file


doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'

        # IMPORTANT: You MUST configure your server version,
        # either here or in the DATABASE_URL env var (see .env file)
        #server_version: '13'
    orm:
        auto_generate_proxy_classes: true
        naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
        auto_mapping: true
        mappings:
            App:
                is_bundle: false
                type: annotation
                dir: '%kernel.project_dir%/src/Entity'
                prefix: 'App\Entity'
                alias: App
        dql:
            datetime_functions:
                DAY: DoctrineExtensions\Query\Mysql\Day
                NOW: DoctrineExtensions\Query\Mysql\Now
                MONTH: DoctrineExtensions\Query\Mysql\Month
                YEAR: DoctrineExtensions\Query\Mysql\Year

my custom query:


    public function findAllGroupByDate()
    {
        return $this->createQueryBuilder('s')
            ->where('s.isFired = false')
            ->groupBy('YEAR(s.startDate)')
            ->addGroupBy('MONTH(s.startDate)')
            ->getQuery()
            ->getResult();
    }

when I dd it
dd($employeeRepository->findAllGroupByDate());

I get

[Semantical Error] line 0, col 68 near 'YEAR(s.startDate),': Error: Cannot group by undefined identification or result variable.

Reply
MolloKhan Avatar

Hey jlchafardet

That's an interesting query you're building. First, in order to use the YEAR & MONTH functions, you need to install that library, Doctrine does not have them enabled by default.
Second, you cannot group by a result value, you need to group by an entity field. You'll have to play with the query because the "group by" clause is kind of tricky
Recommendation: do not try to do everything in one query, consider running 2 queries to get all the desired data, and if grouping it up gets too complicated, you can always fall back to PHP

Cheers!

1 Reply
jlchafardet Avatar

Will do that. let me try with two queries. and if it's still a pain in my behind, I'll just sort the results myself with php.
I can get a single sort by year, then I'll run the sorting by month (some kind of bubble sort implementation could get this done in a jiffy)

Reply
MolloKhan Avatar

Cool, let me know if you managed to sort if out with SQL queries

1 Reply
jlchafardet Avatar

Will do! right now I'm addressing other modules of the app as to not slow its development due to one feature, so once I'm done with the current queue (which could very well be during the weekend) I'll start working on this. :D

Reply
jlchafardet Avatar

as this group within a group is not an urgent matter for the app, I was just preparing in advance, havent yet worked it out :D but its still on my to-do list.

1 Reply
jlchafardet Avatar

hey hey! guess whos back for another round of "give and take" :D

well this might be an odd question.

I have a new relation to "Employee" entity, its called "Employee comment" and its a ManyToOne relationship, where many comments belong to one employee, the comments are able to be posted by several "groups", that have several "staff members" in them.

so when you add a new comment for a specific Employee, it records "the employee" as the owner of the comment, the Staff id, for the person who added the comment, a couple of other fields, and a "dateAdded" field.

through the Employee object, in twig I can do the normal loop


{% for comment in employee.employeeComments %}
    my html here to print the comments.
{% endfor %}

is there any way to ask for "employeeComments to be "sorted" by the "dateAdded" field by default? that way I can just do a 2nd loop through "dateAdded" and print comments added on the same day together (display things, the css works in a timeline format design), like this


{% for commentdByDate in employee.employeeComments %}
    ---- date style design divs etc, thing for the output ---
    {% for comment in commentsByDate %}
        my html here to print the comments.
    {% endfor %}
{% endfor %}
Reply
Victor Avatar

Hey Jose,

Yes, it's possible with annotation, you need to add a special line above the property:


/**
 * ...
 * @ORM\OrderBy({"dateAdded" = "DESC"})
 */
private $employeeComments

This will do the trick in case you're using PHP annotations, otherwise you will nee da different syntax.

Cheers!

Reply
jlchafardet Avatar

Thanks Victor! , I just noticed that what I want is not to sort by but group by, its doable in the same way?

Reply
Victor Avatar

Hey Jose,

Unfortunately, only sorting, for group by you would need to write a custom query in entity repository and call it in your controller, then pass the data to the template. Or, you can try to group in the entity class using Doctrine Criteria, we talked about Doctrine Criteria on SymfonyCasts, try to search it.

I hope this helps!

Cheers!

1 Reply
jlchafardet Avatar

oh hello guys! guess whos here again! :D

I've got a question now related to the EntityType class for forms.

I've got this form type


            ->add('jobPosition', EntityType::class, [
                'class' => JobPosition::class,
                'choice_label' => 'positionName',
                'placeholder' => 'Select one',
                'multiple' => false,
                'required' => false,
                'attr' => [
                    'class' => "select2 select2-danger",
                    'data-dropdown-css-class=' => 'select2-danger',
                ]
            ])

but what I really want is a custom query from the repository that belongs to JobPosition entity.

how would I go around doing that without going through the trouble of query building (considering the custom query is already in the repository)?

Reply
Victor Avatar

Hey Jose!

I believe "query_builder" option is exactly what you need, see it in docs: https://symfony.com/doc/cur... - this will give you JobPositionRepository in the form type and you will be able to call that custom method in it that will return the data you need for this field.

Cheers!

Reply
jlchafardet Avatar

oh well! I'll go the "query_builder" way :D wanted to avoid it if I could haha.

Reply
Victor Avatar

Hey Jose,

Ah, I see... Well, I believe it will be easier and cleaner this way :) Otherwise, you can consider passing choices yourself, see https://symfony.com/doc/cur... - but it may require some extra work.

Cheers!

1 Reply
jlchafardet Avatar

hey there guys! guess what, It's me again :D

I'm having an odd issue, again on the same two custom queries in the repository.

in EmployeeRepository:


    public function findAllByManager($value): ?Employee
    {
        return $this->createQueryBuilder('s')
            ->where('s.manager = :val')
            ->setParameter('val', $value)
            ->getQuery()
            ->getResult();

    }

    public function findAllByIsFired($value): ?Employee
    {
        return $this->createQueryBuilder('s')
            ->andWhere('s.isFired = :val')
            ->setParameter('val', $value)
            ->getQuery()
            ->getResult();
    }

    public function findOneByDiscordOrWallet($discord,$wallet): ?Employee
    {
        return $this->createQueryBuilder('s')
            ->where('s.depositWalletAddress = :wallet')
            ->orWhere('s.discordId = :discord')
            ->setParameter('wallet', $wallet)
            ->setParameter('discord', $discord)
            ->getQuery()
            ->getResult();
    }

EVERY result gives me this now
<blockquote>
App\Repository\EmployeeRepository::findAllByManager(): Return value must be of type ?App\Entity\Employee, array returned
</blockquote>

same issues with other results, ifI go and remove the : ?Employee I get an array but I cant traverse the objects with the getters.

what in the heavens am I doing wrong? what I want are "Employee" Objects, not arrays. even if its just a collection of objects


$var[] = $repo->findAllByManager('1');

Some results go to twig, some others no, still I rather just get objects than arrays as long as I can navigate them.

Reply
MolloKhan Avatar

Hey jlchafardet

If you want to fetch only one record you have to call getOneOrNullResult() after calling ->getQuery(), otherwise, it will get you an array of objects (or arrays depending on the hydration mode you select). I think that's the only thing you're missing

Cheers!

Reply
jlchafardet Avatar

@MolloKhan I can indeed limit to one in specific cases, but that would only work for findOneByDiscordOrWallet which is the only query where I request a single result, the other are results for every record, based on specified conditions, findAllByManager should return only those who belong to a specified manager, (just like findAll(), but with a field condition) same goes with findAllByIsFired, I want to get all results, as long as they are marked as "isFired" true/false, where the last one could be grouped by manager, its still "all" records.

the issue is, they do work if I send them to twig, but if I use them for like exporting a file in a different format, or to do an operation in any othermethod, they give me issues.

Reply
weaverryan Avatar

Hey jlchafardet !

I understand :). So let me add even more details to Diego's info. There are two problems:

1) As Diego mentioned, findOneByDiscordOrWallet should use getOneOrNullResult() instead of getResult(). This will return an Employee object or null. I'm pretty sure you already understand this part :).

2) The other two methods - findAllByManager() and findAllByIsFired() are perfect... except that the return type is wrong. You are correctly using getResult() with these, because you want it to return all the results, not just a single Employee object. And so, both of these are returning "an array full of Employee objects". But your return type is ?Employee, which is wrong: your method is not returning a single Employee object: it's returning an array of Employee objects. So, your method logic is wrong, you're just advertising the wrong return type. The fix:


    /**
    * @return Employee[]
    */
    public function findAllByManager($value): array
    {
        return $this->createQueryBuilder('s')
            ->where('s.manager = :val')
            ->setParameter('val', $value)
            ->getQuery()
            ->getResult();

    }

    /**
    * @return Employee[]
    */
    public function findAllByIsFired($value): array
    {
        return $this->createQueryBuilder('s')
            ->andWhere('s.isFired = :val')
            ->setParameter('val', $value)
            ->getQuery()
            ->getResult();
    }

    public function findOneByDiscordOrWallet($discord,$wallet): ?Employee
    {
        return $this->createQueryBuilder('s')
            ->where('s.depositWalletAddress = :wallet')
            ->orWhere('s.discordId = :discord')
            ->setParameter('wallet', $wallet)
            ->setParameter('discord', $discord)
            ->getQuery()
            ->getOneOrNullResult();
    }

The @return Employee[] isn't necessary, it's just to help your editor.

if I go and remove the : ?Employee I get an array but I cant traverse the objects with the getters.
what in the heavens am I doing wrong? what I want are "Employee" Objects, not arrays. even if its just a collection of objects

So, you ARE getting back a "collection" (an array) of objects :). So:


$employees = $repository->findAllByManager('something');

// you CAN loop over this
foreach ($employees as $employee) {
    // and call methods on the Employee object
    $employee->getName();
}

// but you cannot call methods directly on the array
// does not work:
$employees->getName();

// but you could (thought it's a bit weird) find a specific item in the array and call a method on it:
// DOES work
$employees[0]->getName();

Cheers!

Reply
jlchafardet Avatar

weaverryan I'll give it a try as indeed your explaination clarified a lot of information that was foggy in my mind! I am really thankful that you took the time to reply to this.

It was giving me a headache I'll see if that way it solves my issue, so then I can move to my next part of the app, authentication, which I already know its going to be a pain :D

1 Reply
jlchafardet Avatar

it did indeed fixed my issue, thanks a lot :D

Reply
jlchafardet Avatar

hey there it's been a while, how are things going on your end? I've been away from SF for a long time, was doing some backend microservies stuff in scala for the past year and some and now that I'm back at SF, I find myself having some issues here.

I have 2 tables that are related.
one table has managers, the other has employees.
each employees can only be under one manager, but each manager can supervise many employees, that is quite ok.
employees can be "fired" (yet not deleted, so basically, they turn invisible in results, ideally).

I want to query the Employee table, and get all the employees, as long as they are not kicked, and group the results by manager.


public function findAllByManager($value): ?Employee
    {
        return $this->createQueryBuilder('e')
            ->setParameter('value', $value)
            ->andWhere('e.isFired = :value')
            ->groupBy('s.manager')
            ->getQuery()
            ->getResult();
    }

if I send false or 0, I get no results at all, if I send "1" or true, i get EVERYTHING, even those who are supposed to be hidden.

dd($employeeRepository->findByManager('1'));

<blockquote>
array:72 [▼
0 => App\Entity\Employee{#1673 ▼

-id: 1
-accountId: "0001"
-firstName: "cccc"
-lastName: "rrrr eeee"
-depositAccount: "bleble"
-startDate: DateTime @1632268800 {#1671 ▶}
-picture: "https://cdn.ccc.jpg""
-isFired: true
-manager: Proxies\__CG__\App\Entity\Manager {#1702 ▶ …2}

}
1 => App\Entity\Employee{#1697 ▼

-id: 2
-accountId: "0002"
-firstName: "ttt"
-lastName: " fff tttt"
-depositAccount: "rararara"
-startDate: DateTime @1620604800 {#1698 ▶}
-picture: "https://cdn.827134544.jpg"
-isFired: false
-manager: Proxies\__CG__\App\Entity\Manager {#1702 ▶ …2}

}
</blockquote>

what is it that I'm doing wrong? or is it simply fundamentally wrong what I'm trying to do and I have to do as many queries as there are managers "after" getting each manager?

Reply
Victor Avatar

Hey Irhamel!

Nice to see you again! :)

Well, I see a type in your query, it probably should be "->groupBy('e.manager')" instead of "->groupBy('s.manager')" as I don't see any references to "s" alias in your query. But if that was jut a typo in your comment and not in your code - then hm, your query looks good to me, so it should just work. Please, double check your mapping, you can do this via a specific command:

$ bin/console doctrine:schema:validate

Do you have both schema and mapping OK?

If yes, maybe try to comment out that "->groupBy('e.manager')" line at all and try again, does it work now?

P.S, also minor, but better pass true or false as the $value, Doctrine will convert it to the proper type itself, but your code will be more clear as for me.

I hope this helps ;)

Cheers!

Reply
jlchafardet Avatar

victor Thanks for your reply! that was a typo as it is indeed e.manager than s.manager
Regarding the mapping, its ok.
<blockquote>
D:\vhosts\project [main ≡ +0 ~2 -0 !]> symfony console doctrine:schema:validate
Mapping

[OK] The mapping files are correct.

Database

[OK] The database schema is in sync with the mapping files.
D:\vhosts\project [main ≡ +0 ~4 -0 !]>
</blockquote>

now I tried as you requested, and commented the groupBy clause and I'm not certain if this was something old, or new as I went to bed very tired last night, but im getting this.
<blockquote>
App\Repository\EmployeeRepository::findAllByManager(): Return value must be of type ?App\Entity\Employee, array returned
</blockquote>

The Query now looks like this:
`
public function findAllByManager($value): ?Employee

{
    return $this->createQueryBuilder('e')
        ->andWhere('e.isFired = :val')
        //->groupBy('e.manager')
        ->setParameter('val', $value)
        ->getQuery()
        ->getResult();
}

`

the function call in the controller is in the template print (to send in towards the view for printing purposes.

`

return $this->render('employee/index.html.twig', [
    'employees' => $employeeRepository->findAllByManager(false),
]);

`

Reply
Victor Avatar

Hey Irhamel,

> App\Repository\EmployeeRepository::findAllByManager(): Return value must be of type ?App\Entity\Employee, array returned

Ah yes, that makes sense :) You're returning an array of Employee entities, but typehinted as "?Employee" ;) Change your return type to array and it will work.

Cheers!

Reply
jlchafardet Avatar

that worked! fairly well, thanks a lof victor you really saved me hours of googling :D

Reply
Victor Avatar

Hey Irhamel,

Awesome! :) Well, it was an easy win, but yeah, sometimes it's difficult to notice the problem, just always read the error messages carefully, they contain a lot of tips ;)

Cheers!

Reply
Gofin Avatar

look:
is: {% for question in questions %}
should be: {% for questions in question %}

Reply
MolloKhan Avatar

Hey there,

I'm afraid you're not correct. You can check Twig docs for the "for" tag here https://twig.symfony.com/do...

Cheers!

Reply
Farshad Avatar
Farshad Avatar Farshad | posted 2 years ago

dd($questions) on QuestionController.php shows the array.
On homepage.html.twig it gives an Error: Variable "question" does not exist.
I double checked and its {% for question in questions %}
Any idea what to do?

Reply
MolloKhan Avatar

Hey Farry7

How are you rendering the template? Remember that you need to pass in the $questions variable just like in this code block https://symfonycasts.com/screencast/symfony-doctrine/more-queries#codeblock-9226231640

Cheers!

Reply
Farshad Avatar

Yes I had a typo somewhere. Thanks.

Reply
Cat in space

"Houston: no signs of life"
Start the conversation!

This tutorial also works great for Symfony 6!
Symfony 5 Doctrine 2

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": "^7.4.1",
        "ext-ctype": "*",
        "ext-iconv": "*",
        "composer/package-versions-deprecated": "^1.11", // 1.11.99
        "doctrine/doctrine-bundle": "^2.1", // 2.1.1
        "doctrine/doctrine-migrations-bundle": "^3.0", // 3.0.2
        "doctrine/orm": "^2.7", // 2.8.2
        "knplabs/knp-markdown-bundle": "^1.8", // 1.9.0
        "knplabs/knp-time-bundle": "^1.11", // v1.16.0
        "sensio/framework-extra-bundle": "^6.0", // v6.2.1
        "sentry/sentry-symfony": "^4.0", // 4.0.3
        "stof/doctrine-extensions-bundle": "^1.4", // v1.5.0
        "symfony/asset": "5.1.*", // v5.1.2
        "symfony/console": "5.1.*", // v5.1.2
        "symfony/dotenv": "5.1.*", // v5.1.2
        "symfony/flex": "^1.3.1", // v1.17.5
        "symfony/framework-bundle": "5.1.*", // v5.1.2
        "symfony/monolog-bundle": "^3.0", // v3.5.0
        "symfony/stopwatch": "5.1.*", // v5.1.2
        "symfony/twig-bundle": "5.1.*", // v5.1.2
        "symfony/webpack-encore-bundle": "^1.7", // v1.8.0
        "symfony/yaml": "5.1.*", // v5.1.2
        "twig/extra-bundle": "^2.12|^3.0", // v3.0.4
        "twig/twig": "^2.12|^3.0" // v3.0.4
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.3", // 3.4.0
        "symfony/debug-bundle": "5.1.*", // v5.1.2
        "symfony/maker-bundle": "^1.15", // v1.23.0
        "symfony/var-dumper": "5.1.*", // v5.1.2
        "symfony/web-profiler-bundle": "5.1.*", // v5.1.2
        "zenstruck/foundry": "^1.1" // v1.5.0
    }
}
Edit on GitHub
Previous Chapter
Next Chapter
userVoice