Jobeet Day 6: More with the Entity

The Doctrine Query Object

From the second day’s requirements: “On the homepage, the user sees the latest active jobs”. But as of now, all jobs are displayed, whether they are active or not:

namespace App\Controller;

//...

class JobController extends AbstractController
{
    //...
    public function list() : Response
    {
        $jobs = $this->getDoctrine()->getRepository(Job::class)->findAll();

        return $this->render('job/list.html.twig', [
            'jobs' => $jobs,
        ]);
    }

    //...
}

An active job is one that was posted less than 30 days ago. The $jobs = $this->getDoctrine()->getRepository(Job::class)->findAll(); method will make a request to the database to get all the jobs. We are not specifying any condition which means that all the records are retrieved from the database.

Let’s change it to only select active jobs:

namespace App\Controller;

//...
use Doctrine\ORM\EntityManagerInterface;

class JobController extends AbstractController
{
    //...
    public function list(EntityManagerInterface $em) : Response
    {
        $query = $em->createQuery(
            'SELECT j FROM App:Job j WHERE j.createdAt > :date'
        )->setParameter('date', new \DateTime('-30 days'));
        $jobs = $query->getResult();

        return $this->render('job/list.html.twig', [
            'jobs' => $jobs,
        ]);
    }

    //...
}

Debugging Doctrine generated SQL

Sometimes, it is of great help to see the SQL generated by Doctrine; for instance, to debug a query that does not work as expected. In the dev environment, thanks to the symfony web debug toolbar, all the information you need is available within the comfort of your browser

More lifecycle callbacks

Even if the above code works, it is far from perfect as it does not take into account some requirements from day 2: “A user can come back to re-activate or extend the validity of the job for an extra 30 days…”.

But as the above code only relies on the createdAt value, and because this column stores the creation date, we can not satisfy the above requirement.

If you remember the database schema we have described during day 3, we also have defined an expiresAt column. Currently, if this value is not set in fixture file, it remains always empty. But when a job is created, it can be automatically set to 30 days after the current date.

When you need to do something automatically before a Doctrine object is serialized to the database, you can add a new action to the lifecycle callbacks, like we did earlier for the createdAt column. Open src/Entity/Job.php and modify prePersist method:

/**
 * @ORM\PrePersist()
 */
public function prePersist()
{
    $this->createdAt = new \DateTime();
    $this->updatedAt = new \DateTime();

    if (!$this->expiresAt) {
        $this->expiresAt = (clone $this->createdAt)->modify('+30 days');
    }
}

Now, let’s change the action to use the expiresAt column instead of the createdAt one to select the active jobs:

public function list(EntityManagerInterface $em) : Response
{
    $query = $em->createQuery(
        'SELECT j FROM App:Job j WHERE j.expiresAt > :date'
    )->setParameter('date', new \DateTime());
    $jobs = $query->getResult();

    return $this->render('job/list.html.twig', [
        'jobs' => $jobs,
    ]);
}

More with Fixtures

Refreshing the Jobeet homepage in your browser won’t change anything as the jobs in the database have been posted just a few days ago. Let’s change the fixtures to add a job that is already expired (src/DataFixtures/JobFixtures.php):

$jobExpired = new Job();
$jobExpired->setCategory($manager->merge($this->getReference('category-programming')));
$jobExpired->setType('full-time');
$jobExpired->setCompany('Sensio Labs');
$jobExpired->setLogo('sensio-labs.gif');
$jobExpired->setUrl('http://www.sensiolabs.com/');
$jobExpired->setPosition('Web Developer Expired');
$jobExpired->setLocation('Paris, France');
$jobExpired->setDescription('Lorem ipsum dolor sit amet, consectetur adipisicing elit.');
$jobExpired->setHowToApply('Send your resume to lorem.ipsum [at] dolor.sit');
$jobExpired->setPublic(true);
$jobExpired->setActivated(true);
$jobExpired->setToken('job_expired');
$jobExpired->setEmail('job@example.com');
$jobExpired->setExpiresAt(new \DateTime('-10 days'));

// ...

$manager->persist($jobExpired);

Reload the fixtures and refresh your browser to ensure that the old job does not show up:

bin/console doctrine:fixtures:load

Refactoring

Although the code we have written works fine, it’s not quite right yet. Can you spot the problem?

The Doctrine query code does not belong to the action (the Controller layer), it belongs to the Model layer. In the MVC model, the Model defines all the business logic, and the Controller only calls the Model to retrieve data from it. As the code returns a collection of jobs, let’s move the code to the repository, that is part of model layer. For that we will need to create a custom repository class for Job entity and to add the query to that class.

At this point, you may have a question - what is this Repository class anyway? Repository is a pattern, a common solution to the well know problem. It has been around for quite some time and was popularized around 90’s by people like Martin Fowler and Eric Evans. If you try to describe it briefly it can get quite complex. For now think of it as just another layer of abstraction above the Entity that contains all the useful methods to work with database. If you are still curious about Repository pattern continue to learn here.

Open src/Entity/Job.php and modify @ORM\Entity annotation to specify the repository class for this entity:

/**
 * @ORM\Entity(repositoryClass="App\Repository\JobRepository")
 * @ORM\Table(name="jobs")
 * @ORM\HasLifecycleCallbacks()
 */
class Job

Now let’s create file JobRepository.php in src/Repository folder:

namespace App\Repository;

use Doctrine\ORM\EntityRepository;

class JobRepository extends EntityRepository
{

}

Next, add a new method, findActiveJobs(), to the newly created repository class. This method will query for all of the active Job entities sorted by the expiresAt column and filtered by category if it receives the $categoryId parameter. The method will return special ArrayCollection object from Doctrine bundle, that object will contain all results and can be iterated with foreach just like the usual array.

// ...
use App\Entity\Job;

class JobRepository extends EntityRepository
{
    /**
     * @param int|null $categoryId
     *
     * @return Job[]
     */
    public function findActiveJobs(int $categoryId = null)
    {
        $qb = $this->createQueryBuilder('j')
            ->where('j.expiresAt > :date')
            ->setParameter('date', new \DateTime())
            ->orderBy('j.expiresAt', 'DESC');

        if ($categoryId) {
            $qb->andWhere('j.category = :categoryId')
                ->setParameter('categoryId', $categoryId);
        }

        return $qb->getQuery()->getResult();
    }
}

Now the action code can use this new method to retrieve the active jobs:

public function list(EntityManagerInterface $em) : Response
{
    $jobs = $em->getRepository(Job::class)->findActiveJobs();

    return $this->render('job/list.html.twig', [
        'jobs' => $jobs,
    ]);
}

This refactoring has several benefits over the previous code:

• The logic to get the active jobs is now in the Repository, where it belongs
• The code in the controller is thinner and much more readable
• The findActiveJobs() method is re-usable (for instance in another action)

Categories on the Homepage

According to the second day’s requirements we need to have jobs sorted by categories. Until now, we have not taken the job category into account. From the requirements, the homepage must display jobs by category. First, we need to get all categories with at least one active job.

Create a repository class for the Category entity like we did for Job:

/**
 * @ORM\Entity(repositoryClass="App\Repository\CategoryRepository")
 * @ORM\Table(name="categories")
 */
class Category

Create the repository class (src/Repository/CategoryRepository.php):

namespace App\Repository;

use Doctrine\ORM\EntityRepository;

class CategoryRepository extends EntityRepository
{

}

Add findWithActiveJobs() method:

use App\Entity\Category;
use Doctrine\ORM\EntityRepository;

class CategoryRepository extends EntityRepository
{
    /**
     * @return Category[]
     */
    public function findWithActiveJobs()
    {
        return $this->createQueryBuilder('c')
            ->select('c')
            ->innerJoin('c.jobs', 'j')
            ->where('j.expiresAt > :date')
            ->setParameter('date', new \DateTime())
            ->getQuery()
            ->getResult();
    }
}

These methods will give us only categories with active jobs, but if you will call getJobs method on each category you will receive all jobs, including expired. Let’s create getActiveJobs method in Category entity, which will return only non expired jobs:

/**
 * @return Job[]|ArrayCollection
 */
public function getActiveJobs()
{
    return $this->jobs->filter(function(Job $job) {
        return $job->getExpiresAt() > new \DateTime();
    });
}

Change the index action accordingly:

use App\Entity\Category;
// ...

public function list(EntityManagerInterface $em) : Response
{
    $categories = $em->getRepository(Category::class)->findWithActiveJobs();

    return $this->render('job/list.html.twig', [
        'categories' => $categories,
    ]);
}

In the template, we need to iterate through all categories and display the active jobs (templates/job/list.html.twig):

{% block body %}
    {% for category in categories %}
        <h4>{{ category.name }}</h4>

        <table class="table text-center">
            <thead>
            <tr>
                <th class="active text-center">City</th>
                <th class="active text-center">Position</th>
                <th class="active text-center">Company</th>
            </tr>
            </thead>

            <tbody>
            {% for job in category.activeJobs %}
                <tr>
                    <td>{{ job.location }}</td>
                    <td>
                        <a href="{{ path('job.show', {id: job.id}) }}">
                            {{ job.position }}
                        </a>
                    </td>
                    <td>{{ job.company }}</td>
                </tr>
            {% endfor %}
            </tbody>
        </table>
    {% endfor %}
{% endblock %}

Limit the results

There is still one requirement to implement for the homepage job list: we have to limit the job list to 10 items. That’s simple enough to add slice filter in template templates/job/list.html.twig:

- {% for job in category.activeJobs %}
+ {% for job in category.activeJobs|slice(0, 10) %}

Custom Configuration

In the templates/job/list.html.twig template we have hardcoded the number of max jobs shown for a category. It would have been better to make the 10 limit configurable. In Symfony you can define custom parameters for your application in the config/services.yaml file, under the parameters key:

parameters:
    locale: 'en'
    max_jobs_on_homepage: 10

But this value will not be accessible in template until it will not be defined in twig configuration as global variable. Let’s open config/packages/twig.yaml and add it:

twig:
    # ...
    globals:
        max_jobs_on_homepage: '%max_jobs_on_homepage%'

This can now be accessed from a template:

- {% for job in category.activeJobs|slice(0, 10) %}
+ {% for job in category.activeJobs|slice(0, max_jobs_on_homepage) %}

Dynamic Fixtures

For now, you won’t see any difference because we have a very small amount of jobs in our database. We need to add a bunch of jobs to the fixture. So, you can copy and paste an existing job ten or twenty times by hand… but there’s a better way. Duplication is bad, even in fixture files. Let’s create more jobs in src/DataFixtures/JobFixtures.php:

public function load(ObjectManager $manager) : void
{
    // ...

    for ($i = 100; $i <= 130; $i++) {
        $job = new Job();
        $job->setCategory($manager->merge($this->getReference('category-programming')));
        $job->setType('full-time');
        $job->setCompany('Company ' . $i);
        $job->setPosition('Web Developer');
        $job->setLocation('Paris, France');
        $job->setDescription('Lorem ipsum dolor sit amet, consectetur adipisicing elit.');
        $job->setHowToApply('Send your resume to lorem.ipsum [at] dolor.sit');
        $job->setPublic(true);
        $job->setActivated(true);
        $job->setToken('job_' . $i);
        $job->setEmail('job@example.com');

        $manager->persist($job);
    }

    $manager->flush();
}

You can now reload the fixtures with the doctrine:fixtures:load task and see if only 10 jobs are displayed on the homepage for the Programming category.

Secure the Job Page

When a job expires, even if you know the URL, it must not be possible to access it anymore. Find expired job in DB and try the URL. Instead of displaying the job, we need to forward the user to a 404 page. For this we will create a new method in the JobRepository:

/**
 * @param int $id
 *
 * @return Job|null
 */
public function findActiveJob(int $id) : ?Job
{
    return $this->createQueryBuilder('j')
        ->where('j.id = :id')
        ->andWhere('j.expiresAt > :date')
        ->setParameter('id', $id)
        ->setParameter('date', new \DateTime())
        ->getQuery()
        ->getOneOrNullResult();
}

Now we need to change the show from the JobController to use the new repository method. It is necessary to let Doctrine Entity know about action by adding a new annotation. Do not forget to add use statement at the top of the page:

// ...
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Entity;

class JobController extends AbstractController
{
    // ...

    /**
     * Finds and displays a job entity.
     *
     * @Route("job/{id}", name="job.show", methods="GET", requirements={"id" = "\d+"})
     *
     * @Entity("job", expr="repository.findActiveJob(id)")
     *
     * @param Job $job
     *
     * @return Response
     */
    public function show(Job $job) : Response
    // ...
}

Now, if you try to get an expired job, you will be forwarded to a 404 page.

That’s all for today, you can find the code here: https://github.com/gregurco/jobeet/tree/day6

See you tomorrow!

Additional information

• How to Inject Variables into all Templates (i.e. global Variables)
• @ParamConverter and @Entity

Next Steps

Continue this tutorial here: Jobeet Day 7: Playing with the Category Page

Previous post is available here: Jobeet Day 5: The Routing

Main page is available here: Symfony 4.2 Jobeet Tutorial