Author: rolandow

Raw mysql in Doctrine

To perform a raw mysql statement in symfony through Doctrine:

$sql = ‘SELECT c.CampaignId as campaignId,
               c.ProfileId as profileId,
               p.Name as name
          FROM Profile.ProfileCampaign c
    INNER JOIN Profile.Profile p
            ON p.ProfileId = c.ProfileId
         WHERE c.CampaignId IN (:campaignIdList)
           AND p.Deleted = 0
           AND p.Active = 1’;

$stmt = $this->getDoctrine()
    ->getManager()
    ->getConnection()
    ->prepare($sql);
$stmt->bindValue(‘campaignIdList’, implode(“,”, $campaignIdList));
$stmt->execute();

$sql = ‘SELECT c.CampaignId as campaignId,

c.ProfileId as profileId,

p.Name as name

FROM Profile.ProfileCampaign c

INNER JOIN Profile.Profile p

ON p.ProfileId = c.ProfileId

WHERE c.CampaignId IN (:campaignIdList)

AND p.Deleted = 0

AND p.Active = 1′;

$stmt = $this->getDoctrine()

->getManager()

->getConnection()

->prepare($sql);

$stmt->bindValue(‘campaignIdList’, implode(“,”, $campaignIdList));

$stmt->execute();

This example was being used in a controller.

October 1, 2013

Pass array of options to form builder

This post is just to archive a solution I used to pass an option list, to create a selectbox. The code I used to create the form type:

<?php
namespace Mb\MHoverAdBundle\Form\Type;

use Symfony\Component\Form\AbstractType;
use Symfony\Component\Form\FormBuilderInterface;
use Symfony\Component\OptionsResolver\OptionsResolverInterface;

class SettingType extends AbstractType
{
    public function buildForm(FormBuilderInterface $builder, array $options) {
        $builder->add(‘webmatchId’, ‘choice’, array(‘choices’ => $options[‘webmatchList’]))
            ->add(‘description’)
            ->add(‘hash’)
            ->add(‘save’, ‘submit’);
    }

    public function setDefaultOptions(OptionsResolverInterface $resolver) {
        $resolver->setRequired(array(‘webmatchList’));
    }

    public function getName() {
        return ‘setting’;
    }
}

<?php

namespace Mb\MHoverAdBundle\Form\Type;

use Symfony\Component\Form\AbstractType;

use Symfony\Component\Form\FormBuilderInterface;

use Symfony\Component\OptionsResolver\OptionsResolverInterface;

class SettingType extends AbstractType

{

public function buildForm(FormBuilderInterface $builder, array $options) {

$builder->add(‘webmatchId’, ‘choice’, array(‘choices’ => $options[‘webmatchList’]))

->add(‘description’)

->add(‘hash’)

->add(‘save’, ‘submit’);

}

public function setDefaultOptions(OptionsResolverInterface $resolver) {

$resolver->setRequired(array(‘webmatchList’));

}

public function getName() {

return ‘setting’;

}

The setDefaultOptions method makes sure you pass the array of options. If you don’t, it will throw an exception.

September 5, 2013

Force Doctrine to close mysql connections

So I wrote a nice shell within app/console, which also used pcntl_fork(). Of course there was a very old archive somewhere in my mind that knew that this would mean trouble when you use mysql connections. But since I don’t explicitly use mysql_connect anymore, because Doctrine does this for me, I didn’t realise I faced the same problem as years ago.

When you fork a process with pcntl_fork(), it will copy the current process. This also contains the handles to your mysql connection. When the first child process dies, it will clean up these handles, and therefore close the connection.

Simple solution: make sure all connections are closed before you fork a child. That way, the child is forced to create a new connection.

I solved this using the following piece of code in the script.

// Close connections
$connections = $this->getContainer()
    ->get(‘doctrine’)
    ->getConnections();
foreach($connections as $conn) {
    $this->logger->debug(“Closing connection”);
    $conn->close();
}

// Close connections

$connections = $this->getContainer()

->get(‘doctrine’)

->getConnections();

foreach($connections as $conn) {

$this->logger->debug(“Closing connection”);

$conn->close();

}

July 18, 2013

Use Twig’s renderBlock to update a table row with AJAX

So I was developing a page with a table, where one could delete and edit the data on the table. Deleting was simple, it would do an AJAX request to delete, and then I’d remove the table row using jQuery.

But when you edit the row, you would also need to update the row after you did the edit. The table row was already defined in the twig template though, so couldn’t I just reuse it? Yes we can. This is probably rookie beginners stuff, but well, I’m still learning Symfony (and bootstrap, and basically all frontend stuff).

So, to illustrate, I had this piece of template in my page.

<div class=”paginationContainer” id=”scheduledList”>
    {% block scheduleTable %}
        <table class=”table table-striped table-condensed”>
            <thead>
            <tr>
                {# sorting of properties based on query components #}
                <th style=”width: 20%”>{{ knp_pagination_sortable(scheduledPagination, ‘Sent at’, ‘s.startDateTime’) }}</th>
                <th style=”width: 49%” {% if scheduledPagination.isSorted(‘s.topic’) %} class=”sorted”{% endif %}>{{ knp_pagination_sortable(scheduledPagination, ‘Topic’, ‘s.topic’) }}</th>
                <th style=”width: 10%”>Status</th>
                <th style=”width: 10%”>PreProcess</th>
                <th style=”width: 5%”>Msgs</th>
                <th style=”width: 2%”> </th>
                <th style=”width: 2%”> </th>
                <th style=”width: 2%”> </th>
            </tr>
            </thead>

            <tbody>
            {# table body #}
            {% for emailPromotionSchedule in scheduledPagination %}
                {% block scheduleRow %}
                    <tr>
                        <td>{{ emailPromotionSchedule.startDateTime|date(‘d-m-Y H:i:s’) }}</td>
                        <td>{{ emailPromotionSchedule.topic }}</td>
                        <td>{{ emailPromotionSchedule.statusLabel }}</td>
                        <td>{{ emailPromotionSchedule.preProcessAt is empty ? “” : emailPromotionSchedule.preProcessAt|date(‘d-m-Y H:i:s’) }}</td>
                        <td>{{ emailPromotionSchedule.numberOfMessages }}</td>
                        <td><a href=”{{ path(‘_promotion_est_members’) }}?id={{ emailPromotionSchedule.emailPromotionScheduleId }}” class=”icon icon-eye-open”> </a></td>
                        <td>
                            {% if (“now”|date(‘U’) < (emailPromotionSchedule.preProcessAt|date(‘U’)-600)) and not ((emailPromotionSchedule.status b-and 4) == 4) %}
                                {# edit only for promotions aren’t sent yet #}
                                <a href=”{{ path(‘_promotion_edit’, {‘id’: emailPromotionSchedule.emailPromotionScheduleId }) }}” class=”icon icon-edit”></a>
                            {% endif %}

                        </td>
                        <td><a href=”{{ path(‘_promotion_remove’, { ‘id’: emailPromotionSchedule.emailPromotionScheduleId }) }}” class=”icon icon-remove”> </a></td>
                    </tr>
                {% endblock scheduleRow %}
            {% endfor %}
            </tbody>
        </table>
        {# display navigation #}
        <div class=”navigation”>
            {{ knp_pagination_render(scheduledPagination) }}
        </div>
    {% endblock %}
</div>

{% block scheduleTable %}

<thead>

<tr>

{# sorting of properties based on query components #}

<th style=“width: 20%”>{{ knp_pagination_sortable(scheduledPagination, ‘Sent at’, ‘s.startDateTime’) }}</th>

<th style=“width: 49%” {% if scheduledPagination.isSorted(‘s.topic’) %} class=“sorted”{% endif %}>{{ knp_pagination_sortable(scheduledPagination, ‘Topic’, ‘s.topic’) }}</th>

<th style=“width: 10%”>Status</th>

<th style=“width: 10%”>PreProcess</th>

</tr>

</thead>

<tbody>

{# table body #}

{% for emailPromotionSchedule in scheduledPagination %}

{% block scheduleRow %}

<tr>

<td>{{ emailPromotionSchedule.startDateTime|date(‘d-m-Y H:i:s’) }}</td>

<td>{{ emailPromotionSchedule.topic }}</td>

<td>{{ emailPromotionSchedule.statusLabel }}</td>

<td>{{ emailPromotionSchedule.preProcessAt is empty ? “” : emailPromotionSchedule.preProcessAt|date(‘d-m-Y H:i:s’) }}</td>

<td>{{ emailPromotionSchedule.numberOfMessages }}</td>

<td>

{% if (“now”|date(‘U’) < (emailPromotionSchedule.preProcessAt|date(‘U’)-600)) and not ((emailPromotionSchedule.status b–and 4) == 4) %}

{# edit only for promotions aren‘t sent yet #}

{% endif %}

</td>

</tr>

{% endblock scheduleRow %}

{% endfor %}

</tbody>

</table>

{# display navigation #}

</div>

{% endblock %}

</div>

With jQuery, I had an event handler on the paginationContainer, to catch the edit button. This would open up a modal where the record can be edited. After that, the save button was pressed, which I was also catching using jQuery.

So this javascript code looked like this:

$(“#editRecord”).on(‘click’, ‘.save-button’, function(event) {
    event.preventDefault();

    $(“#editRecord”).modal(‘hide’);

    var target = $(“#saveSchedule”).attr(‘action’);
    $.post(target, $(“#saveSchedule”).serialize(), function(data) {
        if (data.result == ‘ok’) {
            if (tableRow) {
                tableRow.replaceWith(data.newRow);
                delete tableRow;
            }
        }
        else {
            $(“#errorMsgContent”).html(data.error);
            $(“#errorMsg”).modal(‘show’);
        }
    }, “json”)
        .fail(function() {
            $(“#errorMsgContent”).html(‘Error!’);
            $(“#errorMsg”).modal(‘show’);
        });
});

$(“#editRecord”).on(‘click’, ‘.save-button’, function(event) {

event.preventDefault();

$(“#editRecord”).modal(‘hide’);

var target = $(“#saveSchedule”).attr(‘action’);

$.post(target, $(“#saveSchedule”).serialize(), function(data) {

if (data.result == ‘ok’) {

if (tableRow) {

tableRow.replaceWith(data.newRow);

delete tableRow;

}

else {

$(“#errorMsgContent”).html(data.error);

$(“#errorMsg”).modal(‘show’);

}

}, “json”)

.fail(function() {

$(“#errorMsgContent”).html(‘Error!’);

$(“#errorMsg”).modal(‘show’);

});

So the controller needed to just return the table row, as defined in the template at the beginning of the post. The code in the controller was like this:

/**
 * Update schedule through ajax
 * @param $id
 *
 * @Route(“/update/{id}”, requirements={“id” = “\d+”}, name=”_promotion_update”)
 */
public function updateAction($id) {
    $em = $this->getDoctrine()
        ->getManager();

    $emailPromo = $em
        ->getRepository(‘MbDbMVibesEmailBundle:EmailPromotionSchedule’)
        ->find($id);

    if ($emailPromo->getPid() > 0) {
        return new Response(
            json_encode(array(
                ‘result’ => ‘error’, 
                ‘error’ => ‘Schedule is already being processed. Could not save update.’
            )
        ));
    }

    $form = $this->createForm(new EmailPromotionScheduleType(),
        $emailPromo);

    $request = $this->getRequest();

    if ($request->isMethod(‘POST’)) {
        $form->bind($request);

        if ($emailPromo->getStartDateTime()->format(‘U’) < time()) {
            return new Response(
                json_encode(array(
                    ‘result’ => ‘error’, 
                    ‘error’ => ‘Start datetime is in the past’
                )
            ));
        }

        if ($form->isValid()) {
            $em = $this->getDoctrine()->getManager();
            $em->persist($emailPromo);
            $em->flush();

            $template = $this->get(‘twig’)
                ->loadTemplate(“MbMVibesEmailBundle:Promotion:list/scheduleTable.html.twig”);

            $newRow = $template->renderBlock(‘scheduleRow’, array(
                ’emailPromotionSchedule’ => $emailPromo,
            ));

            return new Response(
                json_encode(array(
                    ‘result’ => ‘ok’, 
                    ‘newRow’ => $newRow
                )
            ));
        }
    }

    return new Response(
        json_encode(array(
            ‘result’ => ‘error’, 
            ‘error’ => ‘Unable to save schedule’
        )
    ));
}

/**

* Update schedule through ajax

* @param $id

* @Route(“/update/{id}”, requirements={“id” = “\d+”}, name=”_promotion_update”)

public function updateAction($id) {

$em = $this->getDoctrine()

->getManager();

$emailPromo = $em

->getRepository(‘MbDbMVibesEmailBundle:EmailPromotionSchedule’)

->find($id);

if ($emailPromo->getPid() > 0) {

return new Response(

json_encode(array(

‘result’ => ‘error’,

‘error’ => ‘Schedule is already being processed. Could not save update.’

)

));

}

$form = $this->createForm(new EmailPromotionScheduleType(),

$emailPromo);

$request = $this->getRequest();

if ($request->isMethod(‘POST’)) {

$form->bind($request);

if ($emailPromo->getStartDateTime()->format(‘U’) < time()) {

return new Response(

json_encode(array(

‘result’ => ‘error’,

‘error’ => ‘Start datetime is in the past’

)

));

}

if ($form->isValid()) {

$em = $this->getDoctrine()->getManager();

$em->persist($emailPromo);

$em->flush();

$template = $this->get(‘twig’)

->loadTemplate(“MbMVibesEmailBundle:Promotion:list/scheduleTable.html.twig”);

$newRow = $template->renderBlock(‘scheduleRow’, array(

’emailPromotionSchedule’ => $emailPromo,

));

return new Response(

json_encode(array(

‘result’ => ‘ok’,

‘newRow’ => $newRow

)

));

}

return new Response(

json_encode(array(

‘result’ => ‘error’,

‘error’ => ‘Unable to save schedule’

)

));

}

July 5, 2013

Use KnpPaginatorBundle just for calculation

I was working on a table in Symfony where I needed to show a table. To add pagination without doing the hard work myself, I added KnpPaginatorBundle to my project. This worked really nice, and I had my tables paginated in no time. I just followed the example on the github page.

But then it gave me an error that looked much like this:

Not all identifier properties can be found in the ResultSetMapping: id

Right. So I googled the error message, and found that it had something to do with Doctrine, that could use Pagination if you select only specific fields of a table. And of course, that’s exactly what I did.

I built a repository class that would give me some results, based on a comprehensive html form. I would store the entire form in the database, to be able to reproduce the search, just by applying the search form again.

When processing the result, I already limited the search results though, using setFirstResult and setMaxResults. So it should be possible to create pagination.

Fortunately, I took a closer look to the paginator bundle, before I continued writing my own paginator code. I started with taking a look at the twig template, to give me inspiration on what variables my paginator should return. But then it seemed to me that KnpPaginatorBundle could do the math for me. All I had to do, is fill some variables my self.

So need to set:

the total number of records
the number of items on a page
the current page

Easy does it!

I ended up with the following code in my controller:

/**
 * Fetch email promotion schedule member list, based on the
 * used filter.
 *
 * @param $emailPromotionScheduleId
 *
 * @Route(“/estimatedMembers”, name=”_promotion_est_members”)
 *
 */
public function estimatedMembers() {
    $id = $this->getRequest()->get(‘id’);
    if (!$id)
        throw new Exception(“Id not set”);

    $em = $this->getDoctrine()
        ->getManager();

    $emailPromotionSchedule = $em->getRepository(‘MbDbMVibesEmailBundle:EmailPromotionSchedule’)
        ->find($id);

    $limit = 10;

    $rep = $this->getDoctrine()
        ->getRepository(‘MbDbMVibesBundle:Member’, ‘mvibes’);

    // load search form
    parse_str($emailPromotionSchedule->getSearchForm(), $searchForm);

    if (count($searchForm)) {
        $searchForm[‘contactId’] = $emailPromotionSchedule->getContactId();
        /** Pagination */
        $count = $this->getRequest()->get(‘mCount’) ? $this->getRequest()->get(‘mCount’) : $rep->countByFilter($searchForm);
        $page = $this->getRequest()->get(‘mPage’) ? $this->getRequest()->get(‘mPage’) : 1;

        $params = array();
        $pagination = new SlidingPagination($params);
        $pagination->setTotalItemCount($count);
        $pagination->setItemNumberPerPage($limit);
        $pagination->setCurrentPageNumber($page);

        $viewData = $pagination->getPaginationData();
        $viewData[‘route’] = “_promotion_est_members”;
        $viewData[‘query’] = array(
            ‘id’ => $id,
            ‘mCount’ => $count,
        );
        $viewData[‘pageParameterName’] = “mPage”;

        $template = $this->get(‘twig’)
            ->loadTemplate(‘KnpPaginatorBundle:Pagination:twitter_bootstrap_pagination.html.twig’);

        $paginationHtml = $template->render($viewData);

        /** select members */
        $selectFields = “m.memberId, m.email, m.nickName, m.languageId”;
        $selectFields .= “, s.siteId, s.url, s.name as siteName, s.emailAddress as siteEmailAddress”;

        $query = $rep->queryByFilter($searchForm, $selectFields);

        if(strpos($query->getQuery()->getDQL(), “INNER JOIN m.siteAlias”) === false)
            $query->innerJoin(“m.siteAlias”, “sa”);

        if(strpos($query->getQuery()->getDQL(), “INNER JOIN sa.site”) === false)
            $query->innerJoin(“sa.site”, “s”);

        $query->setFirstResult( ($page-1) * $limit);
        $query->setMaxResults($limit);

        $memberList = $query->getQuery()->getArrayResult();

        return $this->render(‘MbMVibesEmailBundle:Promotion:memberList.html.twig’,
            array(
                ‘memberList’ => $memberList,
                ‘pagination’ => $paginationHtml,
            )
        );

    }

    return new Response(“<div class=’alert alert-error’>No members found for this schedule</div>”);
}

/**

* Fetch email promotion schedule member list, based on the

* used filter.

* @param $emailPromotionScheduleId

* @Route(“/estimatedMembers”, name=”_promotion_est_members”)

public function estimatedMembers() {

$id = $this->getRequest()->get(‘id’);

if (!$id)

throw new Exception(“Id not set”);

$em = $this->getDoctrine()

->getManager();

$emailPromotionSchedule = $em->getRepository(‘MbDbMVibesEmailBundle:EmailPromotionSchedule’)

->find($id);

$limit = 10;

$rep = $this->getDoctrine()

->getRepository(‘MbDbMVibesBundle:Member’, ‘mvibes’);

// load search form

parse_str($emailPromotionSchedule->getSearchForm(), $searchForm);

if (count($searchForm)) {

$searchForm[‘contactId’] = $emailPromotionSchedule->getContactId();

/** Pagination */

$count = $this->getRequest()->get(‘mCount’) ? $this->getRequest()->get(‘mCount’) : $rep->countByFilter($searchForm);

$page = $this->getRequest()->get(‘mPage’) ? $this->getRequest()->get(‘mPage’) : 1;

$params = array();

$pagination = new SlidingPagination($params);

$pagination->setTotalItemCount($count);

$pagination->setItemNumberPerPage($limit);

$pagination->setCurrentPageNumber($page);

$viewData = $pagination->getPaginationData();

$viewData[‘route’] = “_promotion_est_members”;

$viewData[‘query’] = array(

‘id’ => $id,

‘mCount’ => $count,

);

$viewData[‘pageParameterName’] = “mPage”;

$template = $this->get(‘twig’)

->loadTemplate(‘KnpPaginatorBundle:Pagination:twitter_bootstrap_pagination.html.twig’);

$paginationHtml = $template->render($viewData);

/** select members */

$selectFields = “m.memberId, m.email, m.nickName, m.languageId”;

$selectFields .= “, s.siteId, s.url, s.name as siteName, s.emailAddress as siteEmailAddress”;

$query = $rep->queryByFilter($searchForm, $selectFields);

if(strpos($query->getQuery()->getDQL(), “INNER JOIN m.siteAlias”) === false)

$query->innerJoin(“m.siteAlias”, “sa”);

if(strpos($query->getQuery()->getDQL(), “INNER JOIN sa.site”) === false)

$query->innerJoin(“sa.site”, “s”);

$query->setFirstResult( ($page–1) * $limit);

$query->setMaxResults($limit);

$memberList = $query->getQuery()->getArrayResult();

return $this->render(‘MbMVibesEmailBundle:Promotion:memberList.html.twig’,

array(

‘memberList’ => $memberList,

‘pagination’ => $paginationHtml,

)

);

}

return new Response(“<div class=’alert alert-error’>No members found for this schedule</div>”);

}

July 5, 2013