Rikijs Rikijs - 5 days ago 5
PHP Question

Sort Gedmo Tree by arbitrary field using symfony3, doctrine2 and stofDoctrineExtensionsBundle

Introduction



I am using:


  • Windows 10 Pro

  • XAMPP with PHP v7.0.9

  • Symfony v3.1.7

  • Doctrine v2.5.4

  • StofDoctrineExtensionsBundle [1] in order to manage Tree structure.



Setting up



To setup Tree structure I used documentation on Symfony.com [2] followed by documentation on GitHub [3]. Then I proceeded with tree setup - used tree entity from example [4] and used code in [5] to create a tree.

I did setup the tree structure (that represents directories and files) called
FileTree
. I added several custom fields to the tree:
item_name
,
item_extension
and
is_file
. Removed
title
as I have
item_name
...

My
FileTree
entity:

<?php

namespace AppBundle\Entity;

use Gedmo\Mapping\Annotation as Gedmo;
use Doctrine\ORM\Mapping as ORM;
use Doctrine\ORM\Mapping\ManyToOne;
use Doctrine\ORM\Mapping\JoinColumn;
use Symfony\Component\Validator\Constraints as Assert;
use Doctrine\Common\Collections\ArrayCollection as ArrayCollection;

/**
* @Gedmo\Tree(type="nested")
* @ORM\Table(name="file_tree")
* use repository for handy tree functions
* @ORM\Entity(repositoryClass="AppBundle\Repository\FileTreeRepository")
*/
class FileTree
{
/**
* @ORM\Column(type="integer")
* @ORM\Id
* @ORM\GeneratedValue
*/
private $id;

/**
* @ORM\Column(type="string", length=1024)
*
* @var string
*/
private $item_path;

/**
* @ORM\Column(type="string", length=240)
*
* @var string
*/
private $item_name;

/**
* @ORM\Column(type="string", length=20)
*
* @var string
*/
private $item_extension;

/**
* @ORM\Column(type="boolean")
*/
private $is_file;

/**
* @Gedmo\TreeLeft
* @ORM\Column(type="integer")
*/
private $lft;

/**
* @Gedmo\TreeLevel
* @ORM\Column(type="integer")
*/
private $lvl;

/**
* @Gedmo\TreeRight
* @ORM\Column(type="integer")
*/
private $rgt;

/**
* @Gedmo\TreeRoot
* @ORM\ManyToOne(targetEntity="FileTree")
* @ORM\JoinColumn(referencedColumnName="id", onDelete="CASCADE")
*/
private $root;

/**
* @Gedmo\TreeParent
* @ORM\ManyToOne(targetEntity="FileTree", inversedBy="children")
* @ORM\JoinColumn(referencedColumnName="id", onDelete="CASCADE")
*/
private $parent;

/**
* @ORM\OneToMany(targetEntity="FileTree", mappedBy="parent")
* @ORM\OrderBy({"lft" = "ASC"})
*/
private $children;

/**
* @ManyToOne(targetEntity="Project", inversedBy="file_tree")
* @JoinColumn(name="project_id", referencedColumnName="id")
*/
private $project;

/**
* Constructor
*/
public function __construct()
{
$this->children = new ArrayCollection();
}

/**
* Get id
*
* @return integer
*/
public function getId()
{
return $this->id;
}

/**
* Set itemPath
*
* @param string $itemPath
*
* @return FileTree
*/
public function setItemPath($itemPath)
{
$this->item_path = $itemPath;

return $this;
}

/**
* Get itemPath
*
* @return string
*/
public function getItemPath()
{
return $this->item_path;
}

/**
* Set itemName
*
* @param string $itemName
*
* @return FileTree
*/
public function setItemName($itemName)
{
$this->item_name = $itemName;

return $this;
}

/**
* Get itemName
*
* @return string
*/
public function getItemName()
{
return $this->item_name;
}

/**
* Set isFile
*
* @param boolean $isFile
*
* @return FileTree
*/
public function setIsFile($isFile)
{
$this->is_file = $isFile;

return $this;
}

/**
* Get isFile
*
* @return boolean
*/
public function getIsFile()
{
return $this->is_file;
}

/**
* Set lft
*
* @param integer $lft
*
* @return FileTree
*/
public function setLft($lft)
{
$this->lft = $lft;

return $this;
}

/**
* Get lft
*
* @return integer
*/
public function getLft()
{
return $this->lft;
}

/**
* Set lvl
*
* @param integer $lvl
*
* @return FileTree
*/
public function setLvl($lvl)
{
$this->lvl = $lvl;

return $this;
}

/**
* Get lvl
*
* @return integer
*/
public function getLvl()
{
return $this->lvl;
}

/**
* Set rgt
*
* @param integer $rgt
*
* @return FileTree
*/
public function setRgt($rgt)
{
$this->rgt = $rgt;

return $this;
}

/**
* Get rgt
*
* @return integer
*/
public function getRgt()
{
return $this->rgt;
}

/**
* Set root
*
* @param \AppBundle\Entity\FileTree $root
*
* @return FileTree
*/
public function setRoot(\AppBundle\Entity\FileTree $root = null)
{
$this->root = $root;

return $this;
}

/**
* Get root
*
* @return \AppBundle\Entity\FileTree
*/
public function getRoot()
{
return $this->root;
}

/**
* Set parent
*
* @param \AppBundle\Entity\FileTree $parent
*
* @return FileTree
*/
public function setParent(\AppBundle\Entity\FileTree $parent = null)
{
$this->parent = $parent;

return $this;
}

/**
* Get parent
*
* @return \AppBundle\Entity\FileTree
*/
public function getParent()
{
return $this->parent;
}

/**
* Add child
*
* @param \AppBundle\Entity\FileTree $child
*
* @return FileTree
*/
public function addChild(\AppBundle\Entity\FileTree $child)
{
$this->children[] = $child;

return $this;
}

/**
* Remove child
*
* @param \AppBundle\Entity\FileTree $child
*/
public function removeChild(\AppBundle\Entity\FileTree $child)
{
$this->children->removeElement($child);
}

/**
* Get children
*
* @return \Doctrine\Common\Collections\Collection
*/
public function getChildren()
{
return $this->children;
}

/**
* Set project
*
* @param \AppBundle\Entity\Project $project
*
* @return FileTree
*/
public function setProject(\AppBundle\Entity\Project $project = null)
{
$this->project = $project;

return $this;
}

/**
* Get project
*
* @return \AppBundle\Entity\Project
*/
public function getProject()
{
return $this->project;
}

/**
* toString
*
* @return string
*/
public function __toString()
{
return $this->getItemName();
}

/**
* Set itemExtension
*
* @param string $itemExtension
*
* @return FileTree
*/
public function setItemExtension($itemExtension)
{
$this->item_extension = $itemExtension;

return $this;
}

/**
* Get itemExtension
*
* @return string
*/
public function getItemExtension()
{
return $this->item_extension;
}
}


Problem



I need to order files in the folders of the tree by file type - that is: by
item_extension
and then by
item_name
. At the moment
Gedmo Nestedset Tree
sorts by
root
and
lft
as is shown in next code block.

public function getFileTreeNodeArray($file_tree_root_id)
{
$em = $this->getEntityManager();

$query = $em
->createQueryBuilder()
->select('ft')
->from('AppBundle:FileTree', 'ft')
->where('ft.root = :root')
->setParameter('root', $file_tree_root_id)
->orderBy('ft.root, ft.lft', 'ASC')
->getQuery();

$query->setHint(\Doctrine\ORM\Query::HINT_INCLUDE_META_COLUMNS, true);
$build_my_tree = $query->getArrayResult();

return $build_my_tree;
}


Sadly adding additional
->orderBy('ft.item_extension', 'ASC')
did not yield any results...

Then I learned [6] that there are 2 functions that may aide me in this endeavor
reorderAll
and
reorder
. I tried both of them, but sadly items did not change their order, they remained ordered by
root
and
lft
.

My code from
Upload Listener
:

$repo_file_tree = $this->entityManager->getRepository('AppBundle:FileTree');
$root_node_id = $repo_file_tree->getFileTreeRootNodeIdByProjectId($selected_node_parent_id);
$root_node = $repo_file_tree->findOneBy(array('id' => $root_node_id));
$repo_file_tree->reorder($root_node, 'item_extension, item_name', 'ASC');
$this->entityManager->flush();


or

$repo_file_tree = $this->entityManager->getRepository('AppBundle:FileTree');
$repo_file_tree->reorderAll('item_extension, item_name', 'ASC');
$this->entityManager->flush();


Question



How do I get an array with
FileTree
items that are ordered by
item_extension, item_name
?

I would be happy with ether correct Doctrine query (preferably) or with explanation what is wrong with code blocks that contain functions
reorder
and
reorderAll
.

Conclusion



What am I missing?

Please advise.

Thank you for your time and knowledge.

Answer

Just updated my project to Symfony v3.2.0

Now I get error

Invalid sort options specified: field - item_extension, item_name, direction - ASC
500 Internal Server Error - InvalidArgumentException

with above mentioned code when using reorder.

I am not sure is it occurred after upgrade or it was there also with Symfony v3.1.x.

Anyhow - it is clear that reorder does take only one field by which it orders tree branch. Not multiple fields as I tried earlier.

So the answer is: not to use several ordering fields when calling reorder function.

$repo_file_tree = $this->entityManager->getRepository('AppBundle:FileTree');
$root_node_id = $repo_file_tree->getFileTreeRootNodeIdByProjectId($selected_node_parent_id);
$root_node = $repo_file_tree->findOneBy(array('id' => $root_node_id));
$repo_file_tree->reorder($root_node, 'item_extension', 'ASC');
$this->entityManager->flush();