Symfony2 Relationship entities + forms

Question

I'm trying to write one form for submitting against MySQL DB, but I can't get it working, I've tried a lot of things (separate forms, create an ->add('foo', new foo()) to a field, and trying to parse plain SQL with a normal HTML form is my only solution, which is obviously not the best.

This is my DB structure:

![enter image description here][1]

As you can see I need to insert the comments textarea to ticketcomments among the user who wrote it, etc.

On crmentity the description field.

Then on ticketcf the fields that I need to submit from form, are this (because you wont know if I don't tell you because of the field names):

tcf.cf594 AS Type,
tcf.cf675 AS Suscription,
tcf.cf770 AS ID_PRODUCT,
tcf.cf746 AS NotificationDate,
tcf.cf747 AS ResponseDate,
tcf.cf748 AS ResolutionDate,

And, of course, every table needs to have the same ticketid id for the submitted form, so we can retrieve it with one simple query.

It will be easy to do with plain SQL instead of using DQL and Symfony2 forms, but is not a good way to do it.

EDIT This is my new created entity Ticket.php, which has relations to the tables above... if someone could check it out and tell me if it's okay...

ticket.php

<?php 
namespace WbsGo\clientsBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * VtigerTicketcomments
 *
 * @ORM\Table(name="vtiger_troubletickets")
 * @ORM\Entity(repositoryClass="WbsGo\clientsBundle\Entity\TicketsRepository")
 */

class Tickets
{
    /**
     * @var \WbsGo\clientsBundle\Entity\VtigerCrmentity
     *
     * @ORM\OneToOne(targetEntity="WbsGo\clientsBundle\Entity\VtigerCrmentity")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="ticketid", referencedColumnName="crmid", unique=true)
     * })
     * @ORM\Id
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="ticket_no", type="string", length=100, nullable=false)
     *
     */
    private $ticketNo;

    /**
     * @var string
     *
     * @ORM\Column(name="groupname", type="string", length=100, nullable=true)
     */
    private $groupName;

    /**
     * @ORM\ManyToOne(targetEntity="VtigerContactdetails")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="parent_id", referencedColumnName="contactid", unique=true)
     * })
     */
    private $parentId;

    /**
     * @ORM\ManyToOne(targetEntity="VtigerAssets")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="product_id", referencedColumnName="assetsid", unique=true)
     * })
     */
    private $productId;

    /**
     * @var string
     *
     * @ORM\Column(name="priority", type="string", length=100, nullable=true)
     */
    private $priority;

    /**
     * @var string
     *
     * @ORM\Column(name="severity", type="string", length=100, nullable=true)
     */
    private $severity;

    /**
     * @var string
     *
     * @ORM\Column(name="status", type="string", length=100, nullable=true)
     */
    private $status;

    /**
     * @var string
     *
     * @ORM\Column(name="category", type="string", length=100, nullable=true)
     */
    private $category;

    /**
     * @var string
     *
     * @ORM\Column(name="title", type="string", length=255, nullable=true)
     */
    private $title;

    /**
     * @var text
     *
     * @ORM\Column(name="solution", type="text", nullable=true)
     */
    private $solution;

    /**
     * @var text
     *
     * @ORM\Column(name="update_log", type="text", nullable=true)
     */
    private $updateLog;

    /**
     * @var integer
     *
     * @ORM\Column(name="version_id", type="integer", nullable=true)
     */
    private $versionId;

    /**
     * @var string
     *
     * @ORM\Column(name="hours", type="string", length=255, nullable=true)
     */
    private $hours;

    /**
     * @var string
     *
     * @ORM\Column(name="days", type="string", length=255, nullable=true)
     */
    private $days;

    /**
     * @var integer
     *
     * @ORM\Column(name="from_portal", type="integer", nullable=true)
     */
    private $fromPortal;

    /**
     * @ORM\OneToMany(targetEntity="VtigerTicketcomments", mappedBy="ticketid")
     * 
     */
    protected $comments;

    /**
     * @ORM\OneToOne(targetEntity="VtigerTicketcf", mappedBy="id")
     */
    protected $ticketcf;
    /**
     * @ORM\OneToOne(targetEntity="VtigerCrmentity", mappedBy="crmid")
     */
    protected $crmtable;
}

VtigerTicketcf.php

<?php

namespace WbsGo\clientsBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * VtigerTicketcf
 *
 * @ORM\Table(name="vtiger_ticketcf")
 * @ORM\Entity
 */
class VtigerTicketcf
{
    /**
     * @var string
     *
     * @ORM\Column(name="cf_546", type="string", length=255, nullable=true)
     */
    private $cf546;

    /**
     * @var string
     *
     * @ORM\Column(name="cf_556", type="string", length=255, nullable=true)
     */
    private $cf556;

    /**
     * @var string
     *
     * @ORM\Column(name="cf_589", type="string", length=3, nullable=true)
     */
    private $cf589;

    /**
     * @var string
     *
     * @ORM\Column(name="cf_590", type="string", length=3, nullable=true)
     */
    private $cf590;

    /**
     * @var string
     *
     * @ORM\Column(name="cf_592", type="string", length=100, nullable=true)
     */
    private $cf592;

    /**
     * @var string
     *
     * @ORM\Column(name="cf_593", type="string", length=255, nullable=true)
     */
    private $cf593;

    /**
     * @var string
     *
     * @ORM\Column(name="cf_594", type="string", length=255, nullable=true)
     */
    private $cf594;

    /**
     * @var string
     *
     * @ORM\Column(name="cf_675", type="string", length=50, nullable=true)
     */
    private $cf675;

    /**
     * @var float
     *
     * @ORM\Column(name="cf_689", type="decimal", nullable=true)
     */
    private $cf689;

    /**
     * @var float
     *
     * @ORM\Column(name="cf_690", type="decimal", nullable=true)
     */
    private $cf690;

    /**
     * @var float
     *
     * @ORM\Column(name="cf_691", type="decimal", nullable=true)
     */
    private $cf691;

    /**
     * @var float
     *
     * @ORM\Column(name="cf_693", type="decimal", nullable=true)
     */
    private $cf693;

    /**
     * @var string
     *
     * @ORM\Column(name="cf_746", type="string", length=50, nullable=true)
     */
    private $cf746;

    /**
     * @var string
     *
     * @ORM\Column(name="cf_747", type="string", length=50, nullable=true)
     */
    private $cf747;

    /**
     * @var string
     *
     * @ORM\Column(name="cf_748", type="string", length=50, nullable=true)
     */
    private $cf748;

    /**
     * @var string
     *
     * @ORM\Column(name="cf_761", type="string", length=255, nullable=true)
     */
    private $cf761;

    /**
     * @var string
     *
     * @ORM\Column(name="cf_763", type="string", length=255, nullable=true)
     */
    private $cf763;

    /**
     * @var string
     *
     * @ORM\Column(name="cf_764", type="string", length=255, nullable=true)
     */
    private $cf764;

    /**
     * @var string
     *
     * @ORM\Column(name="cf_765", type="string", length=255, nullable=true)
     */
    private $cf765;

    /**
     * @var string
     *
     * @ORM\Column(name="cf_770", type="string", length=50, nullable=true)
     */
    private $cf770;

    /**
     * @var \WbsGo\clientsBundle\Entity\Tickets
     * @ORM\Id
     * @ORM\OneToOne(targetEntity="WbsGo\clientsBundle\Entity\Tickets")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="ticketid", referencedColumnName="ticketid", unique=true)
     * })
     * 
     */
    private $id;
}

VtigerTicketcomments.php

<?php

namespace WbsGo\clientsBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * VtigerTicketcomments
 *
 * @ORM\Table(name="vtiger_ticketcomments")
 * @ORM\Entity
 */
class VtigerTicketcomments
{
    /**
     * @var string
     *
     * @ORM\Column(name="comments", type="text", nullable=true)
     */
    private $comments;

    /**
     * @var integer
     *
     * @ORM\Column(name="ownerid", type="integer", nullable=false)
     */
    private $ownerid;

    /**
     * @var string
     *
     * @ORM\Column(name="ownertype", type="string", length=10, nullable=true)
     */
    private $ownertype;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="createdtime", type="datetime", nullable=false)
     */
    private $createdtime;

    /**
     * @var integer
     *
     * @ORM\Column(name="commentid", type="integer")
     * @ORM\Id
     */
    private $id;

    /**
     * @var \WbsGo\clientsBundle\Entity\Tickets
     * @ORM\OneToOne(targetEntity="WbsGo\clientsBundle\Entity\Tickets")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="ticketid", referencedColumnName="ticketid", unique=true)
     * })
     * 
     */
    private $ticketid;
}

VtigerCrmentity.php

<?php

namespace WbsGo\clientsBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * VtigerCrmentity
 *
 * @ORM\Table(name="vtiger_crmentity")
 * @ORM\Entity
 */
class VtigerCrmentity
{
    /**
     * @var integer
     *
     * @ORM\Column(name="smcreatorid", type="integer", nullable=false)
     */
    private $smcreatorid;

    /**
     * @var integer
     *
     * @ORM\Column(name="smownerid", type="integer", nullable=false)
     */
    private $smownerid;

    /**
     * @var integer
     *
     * @ORM\Column(name="modifiedby", type="integer", nullable=false)
     */
    private $modifiedby;

    /**
     * @var string
     *
     * @ORM\Column(name="setype", type="string", length=30, nullable=false)
     */
    private $setype;

    /**
     * @var string
     *
     * @ORM\Column(name="description", type="text", nullable=true)
     */
    private $description;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="createdtime", type="datetime", nullable=false)
     */
    private $createdtime;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="modifiedtime", type="datetime", nullable=false)
     */
    private $modifiedtime;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="viewedtime", type="datetime", nullable=true)
     */
    private $viewedtime;

    /**
     * @var string
     *
     * @ORM\Column(name="status", type="string", length=50, nullable=true)
     */
    private $status;

    /**
     * @var integer
     *
     * @ORM\Column(name="version", type="integer", nullable=false)
     */
    private $version;

    /**
     * @var integer
     *
     * @ORM\Column(name="presence", type="integer", nullable=true)
     */
    private $presence;

    /**
     * @var integer
     *
     * @ORM\Column(name="deleted", type="integer", nullable=false)
     */
    private $deleted;

    /**
     * @var integer
     *
     * @ORM\Column(name="crmid", type="integer")
     * @ORM\Id
     */
    private $crmid;

}

And this is my repository method...

public function findByIdAndCustomerId($id) {
        $query = $this->getEntityManager()
                ->createQuery(
                        '
                        SELECT 
                        IDENTITY(t.id) AS id,
                        t.ticketNo AS Ticket,
                        t.title AS Asunto,
                        t.status AS Estado,
                        t.updateLog AS LOG,
                        t.hours AS Horas,
                        t.solution AS Solucion,
                        t.priority AS Prioridad,
                        tcf.cf748 AS F_Reso,
                        tcf.cf747 AS F_Resp,
                        tcf.cf746 AS F_Noti,
                        tcf.cf770 AS IDPROD,
                        tcf.cf594 AS Tipo,
                        tcf.cf675 AS Suscripcion,
                        c.comments AS Comments,
                        CONCAT (CONCAT(s.firstname, \' \'), s.lastname) AS Contacto
                        FROM WbsGoclientsBundle:Tickets t
                        JOIN t.parentId s
                        JOIN t.ticketcf tcf
                        JOIN t.comments c
                        WHERE  t.ticketNo = :ticketNo

        ')
        ->setParameter('ticketNo', $id)
        ;

        try {
            //return $query->getSingleResult();
            return $query->getResult();
        }
        catch (\Doctrine\ORM\NoResultException $e)
        {
            return null;
        }
    }

I can retrieve an array of X ticket even if I just search by ONE ID, because if ID1 has 4comments, then I got 4 same tickets, one per comment... How can I make it only ONE ticket with comments => array(...) so I can iterate inside that comments array inside twig?

And also VtigerCrmentity.Description doesn't work either, it returns this error...

Notice: Undefined index: crmid in /var/www/wbsgo/dev.wbsgo/vendor/doctrine/orm/lib/Doctrine/ORM/Query/SqlWalker.php line 826

I don't have getter and setter because I'm re-generating them again, if annotation are OK, the entities will update correctly with get/set and my forms will be able to submit using relationships, right?

Answer 1

I've found the way to do it, and the problems.

The relations are this:

crmentity.id > Main, this table has not relationships

troubleticket.ticketid > This one has relation with crmentity.id

ticketcf.ticketid > this one has relation with troubleticket.ticketid

So the order when you insert needs to be crmentity->troubleticket->ticketcf

But in Symfony2, when you make forms, you must create the last table element as the first form to be generated, so I'll post my code in case someone has the same problem as me.

http://pastebin.com/wLPyXvbj

:)

Answer 2

This answer is built on what @usoban said about using an array but takes a slightly different approach.

// In the controller
$formData = array();
$formData['ticket'] = new Ticket(),
$formData['comments'] = array(new Comment(), new Comment());

$form = $this->createForm(new TicketCommentsType(), $formData);
$form->handleRequest($request);

if ($form->isValid()) 
{ 
    $formData = $form->getData();
    $ticket   = $formData['ticket'];
    $comments = $formData['comments'];

    // Persist ticket

    // Persist comments

TicketCommentsFormType simply brings the two different entities into one form.

class TicketCommentsFormType

public function buildForm(

    $builder->add('ticket',new OpenTicketType());
    $builder->add('comments','collection',array(
            'type' => new VtigerTicketcommentsType()

With this approach there is no need to have any direct relation between Ticket and Comment.

Answer 3

I think @usoban has taken you most of the way. Think of these as two long comments as opposed to answers.

It seems you have a ticket entity and a comment entity with a 1 to many relationship between them but you don't want to actually establish a formal relationship between them because the "DB is used by another platform"? What exactly does that mean. Are you sharing php code with another application? Are you using doctrine 2? With Doctrine 2 you can establish the relationship without changing the actual database.

You really should look at adding a getComments to your ticket entity. It will make your S2 life much easier.

Answer 4

You may embed a form that does represent an underlying entity field by setting mapped option to false, e.g.:

->add('comments', 'collection', array(
    'type'   => new VtigerTicketcommentsType(),
    'mapped' => false
))

However, be careful when assigning form data, since $form->submit($data) will not set data to underlying collection of comment objects. You'll need to process them manually inside the controller. You may access the data that has been flagged as non-mapped using $form->getExtraData() after the data is submitted to the form.

If you'd like Symfony to automatically set data to comments, you'll need to construct a relationship between VtigerTroubletickets and VtigerTicketcomments entities, which, according to the question title, you don't have and try to avoid.

Edit:

A form type is bound to a given entity class. By default, each field you add to a form type must have an underlying property in a declared entity class (or getter/setter pair if property is not defined public). When you construct a form object from form type (OpenTicketType) and data (new VtigerTroubletickets()):

$form = $this->createForm(new OpenTicketType(), new VtigerTroubletickets());

the form get bounds with data present in the object you just created. The object does not have comments property, even more, the form itself knows the field is not mapped and it will not try to populate it from the object, so naturally the collection is rendered as empty, as no data about comments was passed.

To get past this, you may pass array of data instead of newly created object (note: the values may be empty, however comments array must have length > 0 - the comment has to exist, though without actual data).

[
  "title"    => "...",
  "priority" => "...",
  "solution" => "...",
  "comments" => [
    0 => [/* comment 0 data */],
    1 => [/* comment 1 data */],
    .......
  ]
]

This is the form creation. When the form is POST-ed, there is one additional step beside constructing the form. The request data has to be bound onto form, so you do $form->submit($request). Now the form AND underlying entity object will get populated with fresh data.

The $form->getExtraData() is simply a method for accessing the data that form holds and is not mapped to an underlying object - which comments field is, since we flagged it as such.

Alternatively to using getExtraData - extracting data from form manually - you may add a property comments to VtigerTroubletickets entity with getters/setters and not mark it as a db column. That way, you may remove the mapped => false option at form type, and form will automatically read/populate comments property. Still, when persisting, these comments will not be considered for storing, so you'll have to handle them manually. Likewise, when fetching an object from database, the comments property would be empty, so prior to creating a form, you'd have to add some comments to an object, e.g.:

$tickets = new VtigerTroubletickets();
$tickets->setComments(....);
$form = $this->createForm(new OpenTicketType(), $tickets);