We are open-sourcing our QOTD application

At JoliCode, we like to share our knowledge and our tools. We also like to have fun. That’s why we created a QOTD application for our Slack workspace.

We are happy to announce that we are open sourcing it so you can use it in your own workspace.

This project is a “pet project”. Its main goal is to learn new technologies while having fun (don’t miss this part!).

Section intitulée what-is-itWhat is it?

This application is a bot that posts a quote of the day to a Slack channel.

The bot checks every morning for a new quote of the day and posts it to the channel of your choice. To elect the best QOTD, the bot will search for the message with the most reactions. You can customize the searched reaction in the application configuration.

Ok, quite simple isn’t it? But wait, there is more!

The application also provides a frontend, with some niceties:

• A way to vote (👍 / 👎) on each QTOD;
• Some pages to list them all, by date, by top, by flop;
• A hall of fame page to list the best QOTD of the year, month, week;
• A search engine to find a QOTD by word or sentence;
• A stats section with the most productive authors, the serial voters, etc.

Section intitulée how-does-it-workHow does it work?

Obviously, this is a Symfony application. But we wanted to test some new technologies. The first one is Symfony UX.

Symfony UX is a series of tools to create a bridge between Symfony and the JavaScript ecosystem. It stands on the shoulders of JavaScript giants: npm, Webpack Encore, Turbo and Stimulus.

Section intitulée turboTurbo

We wanted a very fast application, so we used Turbo. It is a JavaScript library that avoids reloading the full page. It’s able to replace only the part of the page that changed.

For example, when the user votes for a QTOD, only this one (thanks to <turbo-frame> HTML markup) is reloaded with the new values. And we don’t need a single line of JavaScript to do that.

<turbo-frame id="qotd--{{ qotd.id }}" >
      {{ qotd_macro.message(qotd) }}
      {# .... #}
      <div class="mx-1">
            <span class="badge bg-primary" }}>
            {{ qotd.vote }}
            </span>
      </div>
      <form action="{{ url('qotd_vote_up', { id: qotd.id }) }}" method="POST">
            <button type="submit">👍</button>
      </form>
</turbo-frame>

Section intitulée stimulusStimulus

We didn’t want to use a full JavaScript framework. We wanted to keep the application simple. So we used Stimulus. This JavaScript library allows you to add some behavior to your HTML.

For example, we added a loader when an HTTP request is made:

<div {{ stimulus_controller('loading') }} class="d-none">
      <div class="spinner-border text-secondary" role="status" >
            <span class="visually-hidden">Loading...</span>
      </div>
</div>

export default class extends Controller {
    // When the controller is connected to the DOM
    connect() {
        document.addEventListener("turbo:before-fetch-request", () => {
            this.element.classList.remove('d-none');
        });
        document.addEventListener("turbo:before-fetch-response", () => {
            this.element.classList.add('d-none');
        });
    }
}

You can do much more with Stimulus, but we don’t have time to share everything here. You can read the documentation to learn more.

The application also uses Bootstrap for the CSS, so we bound Stimulus to Bootstrap. Now we have Bootstrap Javascript components like Toast, or Modal, directly available in our HTML.

You want to open the response of a link directly in a Modal? No problem:

<a
    href="{{ url('qotd_show_details', { id: qotd.id }) }}"
    data-turbo-frame="modal"
>
    Open me in a modal
</a>

That’s it! No JavaScript needed.

Section intitulée twig-livecomponentTwig LiveComponent

We think it’s the best feature from Symfony/UX.

It’s a classic PHP class associated with a Twig template. The class exposes some of its properties, usable in the template. And the binding is bi-directional.

What?! When the user interacts with the component in its browser, by typing some text for example, symfony/ux will call your PHP class with the new values, re-render the template, and refresh the HTML.

The search engine is a good example. As soon as the user types something, the application will refresh the list of QOTD that match the query.

The twig code looks like this:

<div {{ attributes.add(stimulus_controller('search')) }}>
    <form class="mb-3">
        <input
            type="search"
            name="query"
            data-model="query"
        >
        <button class="btn btn-primary">
            Search
        </button>
    </form>

    <div data-loading="addClass(opacity-50)">
        {% if query %}
            {% for qotd in computed.qotds %}
                {{ include('qotd/_qotd.html.twig', { qotd }, with_context = false) }}
            {% else %}
                <div class="card mb-2">
                    <div class="card-body">
                        This query did not match any QOTD
                    </div>
                </div>
            {% endfor %}
        {% else %}
            <div class="card mb-2">
                <div class="card-body">
                    Please, type something to search
                </div>
            </div>
        {% endif %}
    </div>
</div>

And the PHP class looks like this:

#[AsLiveComponent('search')]
final class SearchComponent
{
    use DefaultActionTrait;

    #[LiveProp(writable: true)]
    public string $query;

    public function __construct(
        private readonly QotdRepository $qotdRepository,
        private readonly RequestStack $requestStack,
    ) {
        $this->query = (string) $this->requestStack->getCurrentRequest()?->query->get('query', '');
    }

    /**
     * @return array<Qotd>
     */
    public function getQotds(): array
    {
        return $this->qotdRepository->search($this->query);
    }
}

That’s all! Again, no JavaScript needed. symfony/ux takes care of everything: debounce, refresh, etc.

We could continue to talk about symfony/ux, but there are more things to discover in this application!

Section intitulée advanced-doctrine-and-postgresql-usagesAdvanced Doctrine and PostgreSQL usages

We really love PostgreSQL. It’s a very powerful database. And we wanted to use it at its full potential. Unfortunately, Doctrine DQL is really behind SQL. For example, it’s not possible to use CTE, and it’s hard to use Windows Functions or JSON operator among others goodness.

But, to mitigate that, Doctrine provides a way to execute native SQL queries. However using raw SQL result (array) is not very convenient. That’s why Doctrine ships a way to hydrate everything into entities. This feature is called ResultSetMapping. This is not new at all, but we used it a lot in this application.

When you combine the power of PostgreSQL, and Doctrine native queries, you can write a method that returns the best QOTDs over a period of time, in a single SQL query:

/**
 * @return array<array{start_of_period: \DateTimeImmutable, 0: Qotd}>
 */
public function findBestsOver(string $period): array
{
    $rsm = new ResultSetMappingBuilder($this->_em);
    $rsm->addRootEntityFromClassMetadata(Qotd::class, 'q');
    $rsm->addScalarResult('start_of_period', 'start_of_period', 'datetime_immutable');

    $select = $rsm->generateSelectClause();

    $sql = <<<EOSQL
            WITH
                date_boundary AS (
                    SELECT
                        min(date_trunc(:period, date)) AS startp,
                        max(date_trunc(:period, date)) AS endp
                    FROM qotd
                ),
                periods AS (
                    SELECT generate_series(startp, endp, ('1 ' || :period)::interval) AS start_of_period
                    FROM date_boundary
                ),
                qotd AS (
                    SELECT
                        p.start_of_period,
                        q.*,
                        rank() OVER w AS rank
                    FROM periods p
                        LEFT OUTER JOIN qotd q on date_trunc(:period, q.date) = p.start_of_period
                    WINDOW w AS (
                        PARTITION BY p.start_of_period ORDER BY q.vote DESC, q.date DESC
                    )
                )
            SELECT start_of_period, {$select}
            FROM qotd q
            WHERE rank = 1
            ORDER BY start_of_period DESC
            LIMIT 20
        EOSQL;

    return $this
        ->_em
        ->createNativeQuery($sql, $rsm)
        ->setParameters([
            'period' => $period,
        ])
        ->getResult()
    ;
}

Note: The SQL query is complex, but that’s not the point here. The point is to show you how to use native queries with Doctrine. If you want more information about the query, please drop us a comment (here or on twitter) and we may write another article about it.

Each graph is also powered by a single SQL query:

And now, how to paginate Native Queries? At the time of writing the application, we didn’t find a library for paginating native queries. So we wrote our own on top of knplabs/knp-paginator-bundle. The usage is almost the same as regular SQL queries:

/**
 * @return PaginationInterface<string, Qotd>
 */
public function findForHomepageNotVoted(int $page, UserInterface $user): PaginationInterface
{
    $rsm = new ResultSetMappingBuilder($this->_em);
    $rsm->addRootEntityFromClassMetadata(Qotd::class, 'q');

    $select = $rsm->generateSelectClause();

    $query = new NativeQuery(
        "SELECT {$select} FROM qotd AS q WHERE coalesce(voter_ids->>:userId, :notVoted) = :notVoted",
        [
            'userId' => $user->getUserIdentifier(),
            'notVoted' => QotdVote::Null->value,
        ],
        $rsm,
    );

    return $this->paginator->paginate(
        $query,
        $page,
        20,
    );
}

And finally, what about using PostgreSQL as a full text search engine? It’s supported natively, so let’s try it! There is a lot of documentation about it, so we will not explain everything here. But there are one or two little points to take care of.

The first one is about the schema and its migration. Since we add a computed column by PG, we don’t want doctrine migration to remove it. Take a look at the App\Doctrine\EventListener\SchemaListener class to see how we did it.

The second one is about the kind of index we want. The solution we retain is using two indices. The first one is a regular full text index without any configuration. The second one is a trigram index. The trigram index is a bit slower to query, but it returns much more data. And it’s very useful when the user makes a typo. Then we run a query by using the first index, and if nothing is returned, we run a second query by using the second index.

What about the Performance of the search engine? Let’s insert 100K elements in the DB.

Word that match the first query: consequatur, Duration: 57.02ms

Limit  (cost=17776.41..17778.16 rows=15 width=298)
  ->  Gather Merge  (cost=17776.41..20046.67 rows=19458 width=298)
        Workers Planned: 2
        ->  Sort  (cost=16776.39..16800.71 rows=9729 width=298)
              Sort Key: (ts_rank(message_ts, websearch_to_tsquery('consequatur'::text))) DESC
              ->  Parallel Bitmap Heap Scan on qotd q  (cost=225.21..16537.69 rows=9729 width=298)
                    Recheck Cond: (message_ts @@ websearch_to_tsquery('consequatur'::text))
                    ->  Bitmap Index Scan on qotd_message_ts  (cost=0.00..219.38 rows=23350 width=0)
                          Index Cond: (message_ts @@ websearch_to_tsquery('consequatur'::text))

Word that doesn’t match the first query: consequat, Duration: 650ms

Limit  (cost=12675.83..12675.87 rows=15 width=298)
  ->  Sort  (cost=12675.83..12741.49 rows=26263 width=298)
        Sort Key: (word_similarity(message, 'consequat'::text)) DESC, date DESC
        ->  Bitmap Heap Scan on qotd q  (cost=335.54..12031.48 rows=26263 width=298)
              Recheck Cond: (message ~~* '%consequat%'::text)
              ->  Bitmap Index Scan on qotd_message_trigram  (cost=0.00..328.97 rows=26263 width=0)
                    Index Cond: (message ~~* '%consequat%'::text)

If the search query matches the first index, performances are acceptable. Especially if you consider the very low setup to achieve it!

Section intitulée what-s-nextWhat’s next?

We have a lot of ideas to improve this application. Here is a non-exhaustive list:

• Use frankenphp to speed up the application to the extreme (there is a PR for that);
• Use symfony/remote-event, instead of a CRON to elect the best QOTD (there is a PR for that);
• Use symfony/import-maps or symfony/asset-mapper if possible;
• Use more Stimulus components (we need a use case for that 🙃);

Section intitulée conclusionConclusion

Writing this application was really fun. We hope you’ll read the code, it’s full of surprises and nice tips.

The DX of symfony/ux is really good! It just works as expected. And it’s very easy to use. We hope you will enjoy it as much as we do. The application is quite fast, with many user interactions, and being able to do that without writing a single line of JavaScript (except for binding bootstrap and stimulus) is really cool. It feels a bit like there is a new way to develop rich applications.

Writing Native Queries with Doctrine is also very cool. It’s a bit verbose, but it’s much more powerful. And it’s a good way to learn SQL. Once you set-up the pagination layer, it’s very easy to use, and you can use 100% of the power of your database.

We hope you enjoyed this article. Feel free to install this application in your own workspace, and to contribute to it. We will be happy to see your contributions.