Category Archives: Symfony (php framework)

MPTT — Managing Hierarchical Data in Mysql

For a hierarchical table which has depth more than 3 is a tough one to fetch data and show it. And for a bigger table its near to impossible to fetch. It will give you time out. Even if you manage the time the application will be slower. Let me give an example. Suppose you have a region table which have got 4 level of depth and has data about only 100 rows. The levels are State, City, Town, Area. So to show it hierarchically you will have to loop all the data, for this case it will 4 loops which is 100*100*100*100 = 100000000, you might lesser it a bit by some breaks but still it is a n^3 times algorithm which will slow your system definitely and crash your system eventually.

So there is a technique which is called MPTT — modified preordered tree traversal algorithm for multiple depth search or to search hierarchical data. Its a very simple technique where the table in our case the region table is ordered accordingly and serve before the fetch. This technique reminds me the concept of set while I was in grade 8.

 MPTT    Managing Hierarchical Data in Mysql

From the above image you can see

New York State has the range from 1 – 8
New York City has the range from 2 – 7
Manhattan has the range from 3 – 6
Midtown has the range from 4 – 5

Similarly if you have more city or town you need to add them up and whole range would be larger then, for instance if you add two other city Long Island and Columbia then the range should be

New York State has the range from 1 – 12
New York City has the range from 2 – 7
Manhattan has the range from 3 – 6
Midtown has the range from 4 – 5
Long Island has the range from 8 – 9
Columbia has the range from 10 – 11

And you have to make the data base like this. Here we will use the left and right column for the range. Like for new york city the left column should be 2 and the right is 7. Every other rows should have the same thing. Now as I have used symfony2 with doctrine 2.2 with dql I will show how you can generate the full tree

 public function crossJoin(){

 $q = $this->_em->createQuery("
SELECT node.id, node.regionId, node.regionName
FROM ViveZeaBundle:RegionRedefines AS node,
ViveZeaBundle:RegionRedefines AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.regionId
ORDER BY node.lft  ");
return $q->getResult();

 }

Now in my controller I call it and I have used jquery tree to show it.

$tree = $em->getRepository("ViveZeaBundle:RegionRedefines")->crossJoin();
$result = '';
foreach ($tree as $node) {
foreach($tree as $traceTree){
if($traceTree['hierarchyId'] == $node['regionId']){
$trace_last_node=$traceTree;
break;
}
else{
$trace_last_node="";
}
}
$node_depth = $node['depth'];
$node_name = $node['regionName'];
if ($node_depth == $current_depth) {
if ($counter > 0)
$result .= '';
}

elseif ($node_depth > $current_depth) {
$result .= ”, $current_depth – $node_depth) . ”;
$current_depth = $current_depth – ($current_depth – $node_depth);
}

$result .= ‘<li ‘; if($trace_last_node!=””){ if($catName!=””) $tit = $catName.”-in-”.$node['regionName']; else $tit = $node['regionName'];              $tit = preg_replace(“/[^a-zA-Z0-9\/ _-]/”, ”, $tit); $tit = str_replace(‘ ‘,’-’, ucwords($tit)); $tit = str_replace(‘–’,’-’,$tit); $result .= ‘class = “expandable”‘;

$result .=$basePath.’/’.$tit.’/’.$categoryId.’/’.$node['regionId'].’/0/ $node_name . ”;
}

else{
if($catName!=””)
$tit = $catName.”-in-”.$node['regionName'];
else
$tit = $node['regionName'];
$result .=$basePath.’/’.$tit.’/’.$categoryId.’/’.$node['regionId'].’/0/ $node_name . ”;
}
++$counter;
$temp = ” . $node_name . ‘ ‘;
}
$result .= str_repeat(”, $node_depth) . ”;
$result .= ”;

Now just return the result.

Install / setup Symfony 2 windows xp windows 7 windows 8

Symfony 2 install / setup for window xp, windows 7 or windows 8 is lot easier than installing the symfony 1.* especially 1.4 version. Follow the steps how to install or setup it.

Step 1. Go to http://symfony.com/download and download symfony 2.* version and unpack it. I used 7zip to unpack it. In my case I had to extract twice the file and got a folder named Symfony. Which I copied to my htdocs folder. in my case it is d:/xampp/htdocs

Step 2. Install Composer. I did it by the command

php -r "eval('?&gt;'.file_get_contents('https://getcomposer.org/installer'));"
for this you need to go to your command prompt and if the php environmental variable is not set then you have go to the php directory and run the above command. In my case it is d:/xampp/php. You might get error for 'https' if it arises just type the following command in commandprompt
<code>php -r "eval('?&gt;'.file_get_contents('http://getcomposer.org/installer'));"

Step 3. Its almost done the installing part. just for checking run php

app/check.php

for my case it is

D:\xampp\php&gt;php d:/xampp/htdocs/Symfony/app/check.php

. There might be some warnings but you can correct them later.

Getting data using doctrine in symfony by leftjoin

For leftjoin you must remember the relationship between classes in doctrine in schema file.

I have used schema.yml file for my database and its relationship with tables, for example I am give only two tables definition

sfGuardGroup:
actAs: [Timestampable]
columns:
name:
type: string(255)
unique: true
description: string(1000)
relations:
Users:
class: sfGuardUser
refClass: sfGuardUserGroup
local: group_id
foreign: user_id
foreignAlias: Groups
Permissions:
class: sfGuardPermission
local: group_id
foreign: permission_id
refClass: sfGuardGroupPermission
foreignAlias: Groups
 
sfGuardUserGroup:
options:
symfony:
form:   false
filter: false
actAs: [Timestampable]
columns:
user_id:
type: integer
primary: true
group_id:
type: integer
primary: true
relations:
User:
class: sfGuardUser
local: user_id
onDelete: CASCADE
Group:
class: sfGuardGroup
local: group_id
onDelete: CASCADE

Table look lie this ( I have added some data)

sf_guard_group

id   name   description
1     admin       –
2    vendor     –
3     user         –

sf_guard_user_group

user_id    group_id
1                      1
2                     3
3                     3
4                     3
5                     2
6                     2
7                     3

Now you want fetch group name by sf_guard_group table. If you look at relationship you will find that there is a relationship name Users

I have written a function in sfGuardGroupTable.class.php [ you will get it while you build the tables ]

public function getGroupName(){
$q = Doctrine_Query::create()
-&gt;select('sgg.name')
-&gt;from('sfGuardGroup sgg')
-&gt;leftJoin('sgg.Users gs');
$q-&gt;setHydrationMode(Doctrine_Core::HYDRATE_SCALAR);
 

 

 echo $q-&gt;getSQLQuery();
return $q-&gt;execute();
}

you will get the answers like this if you call in respective scope

$groupid = Doctrine_Core::getTable(‘sfGuardGroup’)->getGroupName();

echo “<pre>”;

$groupid = Doctrine_Core::getTable(‘sfGuardGroup’)->getGroupName();

print_r($groupid);

 

Array
(
[0] => Array
(
[sgg_name] => admin
)

[1] => Array
(
[sgg_name] => client
)

[2] => Array
(
[sgg_name] => client
)

[3] => Array
(
[sgg_name] => client
)

[4] => Array
(
[sgg_name] => client
)

[5] => Array
(
[sgg_name] => vendor
)

[6] => Array
(
[sgg_name] => vendor
)

)

Some useful url:

http://www.doctrine-project.org/documentation/manual/1_2/en/dql-doctrine-query-language#join-syntax:on-keyword

Starting with symfony framework in windows platform especialy with httpd

It is always exciting to learn new things. I have started to learn the  symfony framework. It was bit tough for me to setup the framework. But after a lot of pain I have successfully loaded it into my web server root. I want to share some of my problems and way to solve those problems.

You will find lots of books in the website for learning the framework. Though someone told me that it would be hard for me to learn this framework. Don’t really know about the learning curve of this framework.

learning book site : http://www.symfony-project.org/doc/1_4/

I am following the book:  http://www.symfony-project.org/jobeet/1_4/Doctrine/en/

Now few things,

1. You must set your php directory in your windows environment variable.

2. If your web root directory doesn’t support do it in your php.exe folder like: d:/xampp/php

3. While setting it up from command prompt remember you directory, the book might have some other directory

4. Its better to run the command from php.exe file containing folder

5. For getting help or know better from symfony installed components

d:\xampp\php> php lib\vendor\symfony\data\bin\symfony

6. I have changed my httpd.cof like this
 
# Be sure to only have this line once in your configuration

NameVirtualHost <a href="http://127.0.0.1:8080/" target="_blank">127.0.0.1:8080</a></div>
<div>&lt;VirtualHost <a href="http://127.0.0.1:8080/" target="_blank">127.0.0.1:8080</a>&gt;</div>
<div>DocumentRoot "D:\xampp\php\sfprojects\jobeet\web"
<div>DirectoryIndex index.php
&lt;Directory "D:/xampp/php/sfprojects/jobeet/web"&gt;</div>
<div>AllowOverride All
Allow from All
&lt;/Directory&gt;</div>
<div>Alias /sf "D:\xampp\php\sfprojects\jobeet\lib\vendor\symfony\data\web\sf"</div>
<div>&lt;Directory "D:/xampp/php/sfprojects/jobeet/lib/vendor/symfony/data/web/sf"&gt;
<div>AllowOverride All
Allow from All
&lt;/Directory&gt;
&lt;/VirtualHost&gt;</div>
7. Dont forget to restart your Mysql after changing httpd