web notes - a blog

When developing application that use databases, it is often helpful to run SQL standalone to get the information you need, then integrate it into your code.

With Symfony and Doctrine, it is nice to use DQL because it is a nice bridge between the database and the entities.

You can run the commands, using DQL on the command line with the doctrine:query:dql command, like so:

php bin/console doctrine:query:dql "SELECT p.firstname,p.lastname FROM AppBundle\Entity\Common\Person p WHERE p.id=125"

Objective: Create an efficient way to use a Symfony CollectionType of Entities. I wanted to be able to provide a Dijit FilteringSelect (autocomplete) for a collection of entities.

Issues: Using a CollectionType of EntityTypes loaded all the entities into a selection statement. This is fine if there are only a few entities, but not if there are hundreds. Also, model transformers cannot be used with CollectionTypes.

Solution: Create a custom FieldType and apply a model transformer.

The custom field type is based off a TextType, which allows the id of the entity to be passed as text, then transformed by the transformer. Transformers are described here.

The Dijit FilteringSelect only needs an id to serve as an anchor within the page layout. One may argue it could all be dynamic, but I like to use the HTML as a foundation. A text input is used as the base element which is replaced by the Dijit widget. The widget is created and populated with the data from a store.

<?php

namespace AppBundle\Form\Admin\Asset\Type;

use Symfony\Component\Form\AbstractType;
use Symfony\Component\Form\FormBuilderInterface;
use Symfony\Component\OptionsResolver\OptionsResolver;
use Symfony\Component\Form\Extension\Core\Type\TextType;
use AppBundle\Form\Admin\Asset\DataTransformer\ModelToIdTransformer;

class ModelRelationshipType extends AbstractType
{

    private $modelToIdTransformer;

    public function __construct( ModelToIdTransformer $modelToIdTransformer )
    {
        $this->modelToIdTransformer = $modelToIdTransformer;
    }

    /**
     * @param FormBuilderInterface $builder
     * @param array $options
     */
    public function buildForm( FormBuilderInterface $builder, array $options )
    {

        $builder
                ->addModelTransformer( $this->modelToIdTransformer );
    }

    public function configureOptions( OptionsResolver $resolver )
    {
        // Stub
    }

    public function getParent()
    {
        return TextType::class;
    }

}

The new custom field type was then added as a service, with the transformer injected in:

    app.admin.field.type.model_relationship:
        class: AppBundle\Form\Admin\Asset\Type\ModelRelationshipType
        arguments: ['@app.form.data_transformer.model']
        tags:
            - { name: form.type, alias: 'app_model_relationship_type' }

The Twig template code to display each row of the collection uses a macro, because there are four of these fields.

{% block _model_extends_entry_row %}
{% import 'form/macros.html.twig' as form_macros %}
{{ form_macros.model_relationship_entry_row('requires',form) }}
{% endblock %}

{%macro relation(title,type,form) %}{%spaceless%}
        <div id="{{type}}" class="{{type}}">
            <h3>{{title}}</h3>
            {{form_row(form)}}
            {% if form.vars.allow_add %}
                <div data-type="{{type}}" class="add-one-more-row">{{ 'common.add_one_more'|trans}}
            {% endif %}
        </div>
{%endspaceless%}{%endmacro %}

To use the new field type in the form, you add the CollectionType, and the entry_type is ModelRelationshipType.

->add( 'requires', CollectionType::class, [
                    'entry_type' => ModelRelationshipType::class,
                    'required' => false,
                    'label' => false,
                    'empty_data' => null,
                    'allow_add' => true,
                    'allow_delete' => true,
                    'delete_empty' => true
                ] )

This is a modified version of the earlier post which created a static JSON representation of a menu.

What makes it different is that the earlier version rendered the items from the MenuBuilder into a static JSON object that was embedded in a page with a script tag. This approach removes the script tag (and the unnecessary twig template elements) and switches to a more graceful and efficient lazy loading menu.

The menu renderer is organizing the menu into the JSON format used by a Dijit Tree. This is set up for lazy loading, so it it only delivering the items in the assets tree. Items with URLs are pages, which you may click on to view the page, or expand to load more of the menu. These elements are defined in the menu builder (which isn't included in this post).

Dijit/Tree JSON

{
  "id": "admin-assets",
  "name": "Assets",
  "uri": null,
  "has_children": true,
  "parent": "admin",
  "children": [
    {
      "id": "equipment",
      "name": "Equipment",
      "uri": "\/admin\/asset\/equipment",
      "has_children": false,
      "parent": "admin-assets"
    },
    {
      "id": "issues",
      "name": "Issues",
      "uri": "\/admin\/issue\/issue",
      "has_children": false,
      "parent": "admin-assets"
    },
    {
      "id": "manufacturers",
      "name": "Manufacturers",
      "uri": "\/admin\/asset\/manufacturer",
      "has_children": true,
      "parent": "admin-assets"
    },
    {
      "id": "trailers",
      "name": "Trailers",
      "uri": "\/admin\/asset\/trailer",
      "has_children": true,
      "parent": "admin-assets"
    },
    {
      "id": "vendors",
      "name": "Vendors",
      "uri": "\/admin\/asset\/vendor",
      "has_children": true,
      "parent": "admin-assets"
    },
    {
      "id": "configuration",
      "name": "Configuration",
      "uri": null,
      "has_children": true,
      "parent": "admin-assets"
    }
  ]
}

Content Menu Options

The content menu options allow the user to navigate directly to content they have created in the system. In this case, it is manufacturers. As above, if a URL is provided, the user may go to the page indicated by the uri attribute, or expand the menu item to view the children. The children under a manufacturer menu item are brands.

The URL is /api/menustore/adminmenus/manufacturers.

{
  "children": [
    {
      "id": "manufacturer-14",
      "name": "Pelican",
      "parent": "manufacturer",
      "has_children": "1",
      "uri": "\/admin\/asset\/manufacturer?name=Pelican"
    },
    {
      "id": "manufacturer-2",
      "name": "Shed",
      "parent": "manufacturer",
      "has_children": "0",
      "uri": "\/admin\/asset\/manufacturer?name=Shed"
    },
    {
      "id": "manufacturer-1",
      "name": "Barn",
      "parent": "manufacturer",
      "has_children": "1",
      "uri": "\/admin\/asset\/manufacturer?name=Barn"
    }
  ]
}

Lazy loaded element

The content menu for the manufacturer named Barn follows. This leaf of the tree doesn't have any children. Clicking on it will take the user to the URL in the uri attribute.

The URL is /api/menustore/adminmenus/manufacturer-1

{
  "children": [
    {
      "id": 12,
      "name": "Barn",
      "parent": "manufacturer-1",
      "uri": "\/admin\/asset\/manufacturer\/Barn\/brand\/Barn",
      "has_children": false,
      "children": null
    }
  ]
}

This can be read as the manufacturer's name is Barn and they have one brand, Barn. If you click on the brand Barn under the manufacturer Barn, you will go to a page to manage the brand Barn.

The render code which returns the menu rendered ready for a Dijit/Tree. Reference.

    public function render( ItemInterface $item, array $options = array() )
    {
        $options = array_merge( $this->defaultOptions, $options );
        
        $translator = $options['translator'];

        $itemIterator = new \Knp\Menu\Iterator\RecursiveItemIterator( $item );

        $iterator = new \RecursiveIteratorIterator( $itemIterator, \RecursiveIteratorIterator::SELF_FIRST );

        $items = [];
        foreach( $iterator as $item )
        {
            $translatedLabel = $translator->trans($item->getLabel());
            $id = $item->getName();  
            $itemData = [ 'id' => strtolower( $item->getName() ), 'name' => $translatedLabel, 'uri' => $item->getUri()];
            $itemData['has_children'] = $item->hasChildren();
            $parentId = $item->getParent()->getName();
            if ($parentId !== $id) {
                $itemData['parent'] = strtolower($parentId);
                if (!isset($items[$parentId]['children'])) {
                    $items[$parentId]['children'] = [];
                }
                $items[$parentId]['children'][] = $itemData;
            }
            if (isset($items[$id])) {
                $items[$id] = array_merge($itemData, $items[$id]);
            } else {
                $items[$id] = $itemData;
            }
        }
        return $items;
    }

The menu builder and renderer are defined as services.

    app.menu_builder:
        class: AppBundle\Menu\MenuBuilder
        arguments: ["@knp_menu.factory"]
        calls:
            - [setContainer, ["@service_container"]]
        tags:
            - { name: knp_menu.menu_builder, method: createAdminMenu, alias: admin }
            - { name: knp_menu.menu_builder, method: createCalendarMenu, alias: calendar }
            - { name: knp_menu.menu_builder, method: createMainMenu, alias: main }
            - { name: knp_menu.menu_builder, method: createUserMenu, alias: user }
            - { name: knp_menu.menu_builder, method: createVendorMenu, alias: vendor }

    app.menu_renderer:
        # The class implements Knp\Menu\Renderer\RendererInterface
        class: AppBundle\Menu\JsonRenderer
        arguments: [ "@twig",  "knp_menu.html.twig", "@knp_menu.matcher", {"translator": "@translator" }]
        tags:
            # The alias is what is used to retrieve the menu
            - { name: knp_menu.renderer, alias: json }

The static routing is as follows (this is output from

php bin/console debug:route

r) :

app_admin_api_menu_store_get_adminmenu GET ANY ANY /api/menustore/adminmenus
app_admin_api_menu_store_get_adminmenu_alt GET ANY ANY /api/menustore/adminmenus/
app_admin_api_menu_store_get_adminmenu_parent GET ANY ANY /api/menustore/adminmenus/?parent={parent}
app_admin_api_menu_store_get_adminmenu_id GET ANY ANY /api/menustore/adminmenus/{id}

The code which delivers the admin menu follows.

This code is delivering the static menu elements, those defined in the MenuBuilder, using the JsonRenderer listed above.

They provide references which allow the menu to lazy load additional content.

The JSON listed above is from, /api/menustore/adminmenus/admin-assets.

Menu elements for content, are listed first by the name of the content type (trailers) and allow loading of the contents of the

app_admin_api_store_get_trailer GET ANY ANY /api/store/trailer
app_admin_api_store_get_trailercontents GET ANY ANY /api/store/trailercontents/{id}

    /**
     * @View()
     * @Get("/adminmenus")
     * @Get("/adminmenus/", name="_alt")
     * @Get("/adminmenus/?parent={parent}", name="_parent", defaults={"parent" = "admin"})
     * @Get("/adminmenus/{id}", name="_id", defaults={"id" = "admin"})
     *
     */
    public function getAdminmenuAction( Request $request )
    {
        $this->denyAccessUnlessGranted( 'ROLE_ADMIN', null, 'Unable to access this page!' );
        $adminMenu = $this->get( 'app.menu_builder' )->createAdminMenu( [] );
        $renderer = $this->get( 'app.menu_renderer' );
        $menu = [];
        $id = $request->get( 'id' );
        if( $id !== null )
        {
            // This renders URLs without an id
            if( !in_array( $id, $this->entityMenus ) && preg_match( '/\-\d+$/', $id ) === 0 )
            {
                $menu = $renderer->render( $adminMenu, ['depth' => 1] );
                $menu = $menu[$id];
            }
            else
            {
                // This renders dynamic menu elements - the dynamicId would be manufacturer for this blog post
                $dynamicId = trim( ucfirst( preg_replace( '/^([a-z]+).*$/', '$1', $id ) ), 's' );
                $menuMethod = 'get' . $dynamicId . 'Menu';
                if( method_exists( $this, $menuMethod ) )
                {
                    $menu = $this->{$menuMethod}( $adminMenu, $renderer, $id );
                }
            }
        }
        $parent = $request->get( 'parent' );
        if( $parent !== null )
        {
            foreach( $adminMenu as $name => $children )
            {
                if( $name === $parent )
                {
                    $menu['id'] = $parent;
                    $menu['name'] = $name;
                    $menu['children'] = $renderer->render( $children, ['depth' => 1], 'json' );

                    break;
                }
            }
        }
        if( isset( $menu['children'] ) )
        {
            foreach( $menu['children'] as $c => $child )
            {
                if( in_array( $child['id'], $this->entityMenus ) )
                {
                    $menu['children'][$c]['has_children'] = true;
                }
            }
        }
        return $menu;
    }

This code handles the lazy loading. The id may be manufacturers or manufacturer-id. If it is the former, a list of the manufacturers will be returned, the later will return all the brands for a manufacturer. If the system is likely to have a large number of manufacturers or sub items, you should add another layer to organize the elements by the first letter of their name - so you can click on A to see all the manufacturers whose name starts with A.

    function getManufacturerMenu( $adminMenu, $renderer, $id )
    {
        $limit = 2500; // TODO: Change to deliver first letters if there are too many manufacturers
        $em = $this->getDoctrine()->getManager();
        $base = explode( '-', $id );
        switch( $base[0] )
        {
            case 'manufacturers':
                $queryBuilder = $em->createQueryBuilder();
                $queryBuilder
                        ->select( ["CONCAT('manufacturer-',m.id) AS id", 'm.name', "'manufacturer' AS parent", 'COUNT(b.id) AS has_children'] )
                        ->from( 'AppBundle\Entity\Asset\Manufacturer', 'm' )
                        ->leftJoin( 'm.brands', 'b' )
                        ->orderBy( 'm.name' )
                        ->groupBy( 'm.id' )
                        ->setFirstResult( 0 )
                        ->setMaxResults( $limit );
                $menu = $renderer->render( $adminMenu['admin']['admin-assets']['manufacturers'] );
                $children = $queryBuilder->getQuery()->getResult();
                $l = count( $children );
                if( $l < $limit )
                {
                    for( $i = 0; $i < $l; $i++ )
                    {
                        $children[$i]['uri'] = $this->generateUrl(
                                'app_admin_asset_manufacturer_index', ['name' => $children[$i]['name']], true ); // absolute
                    }
                }
                $menu['children'] = $children;
                break;
            case 'manufacturer':
                $manufacturer = $em->getRepository( 'AppBundle\Entity\Asset\Manufacturer' )->find( $base[1] );
                $brands = $manufacturer->getBrands();
                $children = [];
                foreach( $brands as $b )
                {
                    $children[] = [
                        'id' => $b->getId(),
                        'name' => $b->getName(),
                        'parent' => $id,
                        'uri' => $this->generateUrl(
                                'app_admin_asset_manufacturer_getmanufacturerbrand', ['mname' => $manufacturer->getName(), 'bname' => $b->getName()], true ), // absolute
                        'has_children' => false,
                        'children' => null];
                }
                $menu['children'] = $children;
                break;
        }
        return $menu;
    }

The (Dojo) client side code:

define([
    "dojo/dom",
    "dojo/store/Memory",
    "dijit/tree/ObjectStoreModel",
    "dojo/store/JsonRest",
    "dijit/Tree",
    "dojo/domReady!"
], function (dom,
        Memory, ObjectStoreModel, JsonRest, Tree) {
//"use strict";
    function run() {

        var store = new JsonRest({
            target: "/api/menustore/adminmenus/",
            getChildren: function (object) {
                return this.get(object.id).then(function (fullObject) {
                    return fullObject.children;
                });
            }
        });

        var model = new ObjectStoreModel({
            store: store,
            mayHaveChildren: function (object) {
                return object.has_children;
            }, getRoot: function (onItem) {
                this.store.get("admin").then(onItem);
            },
        });
        // Create the Tree.
        var tree = new Tree({
            id: "admin-menu",
            model: model,
            persist: true,
            onClick: function (item) {
                if( typeof item.uri !== "undefined" && item.uri !== null ) {
                    location.href = item.uri;
                }
            },
            getIconClass: function (item, opened) {
                return (item && item.has_children) ? (opened ? "dijitFolderOpened" : "dijitFolderClosed") : "dijitLeaf"
            }
        });
        tree.placeAt(dom.byId("admin-left-menu"));
        tree.startup();
    }
    return {
        run: run
    };
});
//# sourceURL=menu.js

I wrote some code that parsed through the default KNP menu HTML and created a Dijit menu. It worked, but it was slow and awkward.

I couldn't find an easy way to get the KNP menu in the JSON form that would work well with Dijit/Tree, so I wrote my own.

JsonRenderer.php

<?php

namespace AppBundle\Menu;

use Knp\Menu\ItemInterface;
use Knp\Menu\Matcher\MatcherInterface;
use Knp\Menu\Renderer\RendererInterface;
use Translator;

class JsonRenderer implements RendererInterface
{

    /**
     * @var \Twig_Environment
     */
    private $environment;
    private $matcher;
    private $defaultOptions;

    /**
     * @param \Twig_Environment $environment
     * @param string            $template
     * @param MatcherInterface  $matcher
     * @param array             $defaultOptions
     */
    public function __construct( \Twig_Environment $environment, $template, MatcherInterface $matcher, array $defaultOptions = array() )
    {
        $this->environment = $environment;
        $this->matcher = $matcher;
        $this->defaultOptions = array_merge( array(
            'depth' => null,
            'matchingDepth' => null,
            'currentAsLink' => true,
            'currentClass' => 'current',
            'ancestorClass' => 'current_ancestor',
            'firstClass' => 'first',
            'lastClass' => 'last',
            'template' => $template,
            'compressed' => false,
            'allow_safe_labels' => false,
            'clear_matcher' => true,
            'leaf_class' => null,
            'branch_class' => null
                ), $defaultOptions );
   
    }

    public function render( ItemInterface $item, array $options = array() )
    {
        $options = array_merge( $this->defaultOptions, $options );
        
        $translator = $options['translator'];

        $itemIterator = new \Knp\Menu\Iterator\RecursiveItemIterator( $item );

        $iterator = new \RecursiveIteratorIterator( $itemIterator, \RecursiveIteratorIterator::SELF_FIRST );
        
        $items = [];
        foreach( $iterator as $item )
        {
            $translatedLabel = $translator->trans($item->getLabel());
            $id = $item->getName();
            $parentId = $item->getParent()->getName();
            $itemData = [ 'id' => strtolower( $item->getName() ), 'name' => $translatedLabel, 'uri' => $item->getUri()];
            if ($parentId !== $id) {
                $itemData['parent'] =strtolower($parentId);
            }
            $itemData['has_children'] = $item->hasChildren();
            $items[] = $itemData;
        }
        $lastItem = count( $items ) - 1;
        $items[$lastItem]['lastItem'] = true;

        $html = $this->environment->render( $options['template'], array('items' => $items, 'options' => $options, 'matcher' => $this->matcher) );

        if( $options['clear_matcher'] )
        {
            $this->matcher->clear();
        }
        return $html;
    }

}

Added JsonRenderer as a service in services.yml

    app.menu_renderer:
        # The class implements Knp\Menu\Renderer\RendererInterface
        class: AppBundle\Menu\JsonRenderer
        arguments: [ "@twig",  "knp_menu.html.twig", "@knp_menu.matcher", {"translator": "@translator" }]
        tags:
            # The alias is what is used to retrieve the menu
            - { name: knp_menu.renderer, alias: json }

I used a twig template (knp_menu.html.twig) to output the JSON, perhaps in the future I will update it to deliver it as a .js file.

var menuTreeStoreData = [{% for item in items%}{{item|json_encode()|raw}}{% if item.lastItem is not defined %},{%endif%}{% endfor %}];

The layout template calls the renderer with this line:

{{ knp_menu_render('admin',{'template': 'admin/parts/knp_menu.html.twig'}, 'json') }}

Finally, this is menu.js which uses the menu data produced by JsonRenderer to create a nice tree for a menu. You could use a different menu approach. I kind of like the tree for now.

define([
    "dojo/dom",
    "dojo/store/Memory",
    "dijit/tree/ObjectStoreModel",
    "dijit/Tree",
    "dojo/domReady!"
], function (dom,
        Memory, ObjectStoreModel, Tree) {
//"use strict";
    function run() {
        var store = new Memory({data: menuTreeStoreData
            ,
            getChildren: function (object) {
                return this.query({parent: object.id});
            }});
        var model = new ObjectStoreModel({
            store: store,
            query: {id: 'admin'}
        });
        // Create the Tree.
        var tree = new Tree({
            id: "admin-menu",
            model: model,
            persist: true,
            onClick: function (item) {
                if( typeof item.uri !== "undefined" && item.uri !== null ) {
                    location.href = item.uri;
                }
            },
            getIconClass: function (item, opened) {
                return (item && item.has_children) ? (opened ? "dijitFolderOpened" : "dijitFolderClosed") : "dijitLeaf"
            }
        });
        tree.placeAt(dom.byId("admin-left-menu"));
        tree.startup();
    }
    return {
        run: run
    };
});

If you use Dojo to provide a polished interface with a Symfony 3 application programmatically, you may use a data- attribute on a select tag to pass the options from Symfony 3 to Dojo.

The entity in this case is phone number type. This is a table with two columns, an id and the type which is a string.

dev=# \d+ phone_number_type
                          Table "public.phone_number_type"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               | not null  | plain    |              | 
 type   | character varying(16) | not null  | extended |              | 
Indexes:
    "phone_number_type_pkey" PRIMARY KEY, btree (id)

dev=# select * from phone_number_type;
 id |   type    
----+-----------
  1 | office
  2 | home
  3 | mobile
  4 | alternate
  5 | emergency
  6 | on-call
  7 | other
  8 | security
(8 rows)

The form uses the entity to allow the user to indicate the type of phone number.

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

        $builder
                ->add( 'type', EntityType::class, [
                    'class' => 'AppBundle:PhoneNumberType',
                    'choice_label' => 'type',
                    'multiple' => false,
                    'expanded' => false,
                    'required' => true,
                    'choice_translation_domain' => false
                ] )
                ->add( 'phonenumber', TextType::class, [
                    
                ] )
                ->add( 'comment', TextType::class )
        ;
    }

Next, the twig block is customized to render the options as a data- attribute on the select tag, rather than the options:

{%- block choice_widget_collapsed -%}
    {%- if required and placeholder is none and not placeholder_in_choices and not multiple and (attr.size is not defined or attr.size <= 1) -%}
        {% set required = false %}
    {%- endif -%}
    <select {{ block('widget_attributes') }}{% if multiple %} multiple="multiple" {% else %} {% endif %}
        {%- if placeholder is not none -%}
            {% set blank_value = (placeholder != '') ? (translation_domain is same as(false) ? placeholder : placeholder|trans({}, translation_domain)) %}
            {% set blank = { 'value': '', 'label': '{{ blank_value|raw }}'} %}
        {%- endif -%}
        {%- if preferred_choices|length > 0 -%}
            {% set choices = preferred_choices|merge(choices) %}
            {%- if choices|length > 0 and separator is not none -%}
                {% set choices = choices|merge([{'label':'{{separator}}','disabled':true}]) %}
            {%- endif -%}
        {%- endif -%}
        {%- set options = choices -%}
        data-options="{{options|json_encode()}}" data-selected="{% if required and value is empty %}{else}-1{% endif %}">
    
{%- endblock choice_widget_collapsed -%}

On the client side, the options are read into a MemoryStore

        // select is the select node
        data = JSON.parse(domAttr.get(select, "data-options"));
        // Convert the data to an array of objects
        storeData = [];
        for( d in data ) {
            storeData.push(data[d]);
        }
        memoryStore = new Memory({
            idProperty: "value",
            data: storeData});
        store = new ObjectStore({objectStore: memoryStore});

        typeSelect = new Select({
            store: store,
            placeholder: core.type,
            required: true
        }, select);
        typeSelect.startup();

This approach is good for cases where there aren't too many options and they are static. If you had a long list, or wanted an autocomplete, an Ajax solution would be better.

A similar approach may be used with other JavaScript libraries and frameworks.