Category: Symfony, Twig, Doctrine & ORMs

Using raw PDO MySQL queries with safe placeholders in Symfony

09/10/2020 Aaron Belchamber No Comments

This comes in handy as Doctrine doesn’t always support all native MySQL commands and functions.  Strangely, certain common time and date-related ones like YEAR(), MONTH() and DAY().

The $paramsArr is an array for all placeholders in your standard PDO query.

// Symfony raw queries with placeholders.
// Example query:
$query = "SELECT * FROM users WHERE status=:status
              GROUP BY last_name ORDER BY last_name ASC";
$paramsArr=array('status'=>'Active');
$stmt = $this->em->getConnection()->prepare($query);
$stmt->execute($paramsArr);
$resultsArr=$stmt->fetchAll();

 
 
Symfony & Twig: Simple month and year form selection widget
  01/26/2016   Aaron Belchamber No Comments
This is a very useful Twig that I use in a lot of different forms.  I attach a listener and when they click “Update” it will redirect the user by appending the URL with the /year/month to hook nice and cleanly in built-in routes to pass the year and month into different controllers.
<form id="yearMonthWidget" name="newDate1" method="get" action="">

   <select name="month">
      {% for mo in 1..12 %}
         <option value="{{ mo }}">{{ ('2012-' ~mo~'-01')|date("M") }}</option>
      {% endfor %}

   </select>

   <select name="year">
      {% for yr in "now"|date("Y")+1..2010 %}
         <option value="{{ yr }}">{{ yr }}</option>
      {% endfor %}
   </select>
   <input type="submit" value="Update"/>
</form>

 
 
 
Symfony & Doctrine:  Using table joins with unassociated entities
  01/25/2016   Aaron Belchamber No Comments
In Symfony using Doctrine, if no association is available for two entities, you can still join them and treat them like they have a relationship.  To do so, you have to use the “Join: WITH” method.  Here’s an example query to illustrate, notice the “a.user = u.id”.
public function getHistory($users) {

    $qb = $this->entityManager->createQueryBuilder();
    $qb ->select('a', 'u')
        ->from('Credit\Entity\UserHistory', 'a')
        ->leftJoin(
            'User\Entity\User',
            'u',
            \Doctrine\ORM\Query\Expr\Join::WITH,
            'a.user = u.id'
        )
        ->where('u = :user')
        ->setParameter('user', $users)
        ->orderBy('a.created_at', 'DESC');

    return $qb->getQuery()->getResult();
}
Some other helpful links on Stack Overflow:
http://stackoverflow.com/questions/15087933/how-to-do-left-join-in-doctrine
http://stackoverflow.com/questions/19185587/left-join-on-condition-and-other-condition-syntax-in-doctrine/19189240#19189240
Here’s a great reference for advanced Doctrine queries:
http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html#high-level-api-methods
 
 
PHP Symfony Events: Developer tips to easily extend user administration and management within your code
  11/30/2015   Aaron Belchamber No Comments
A special thanks to James Halsall for some excellent tips in tapping into Symfony events through kernel listeners.
His article explains how to create a service to trigger on certain events, this is related to the FOS User Bundle but the same pattern applies to most events in your Symfony project.  Click here to read the entire article with explanations and example code.
 
 
In Symfony, moving some code from one bundle to another a simple way to modularize your code, reuse and scale.  Oh yeah!
  08/02/2015   Aaron Belchamber No Comments
I recently ran into an issue where I found I was creating a new user profile system for different systems and then it occurred to my:  DRY.  DON’T REPEAT YOURSELF.  Yet, in the bustle of activity I realized there was too much in common with these user profiles to rewrite the code.
So why not extract the user-related code and put it all in its own bundle for easier maintainability?  After all, in Symfony, it’s a breeze working between bundles, from one bundle you can call code from other bundles.  It was a “duh” moment, to say the least.
So how do you move some code you wrote that works to its own bundle?  Well, it took me less than 5 minutes to move 6 entities, 7 controllers and their supporting code like routing and update to the new bundle.
Step #1:  Go to your command line and type:    “app/console  generate:bundle”
I went through the Symfony tool and called it “UserBundle”.  Symfony will automatically create all the base and template files and folder structure for you new User Bundle.
Step #2:  Now, find all code that you already wrote and move it all to your new bundle following the same folder structure.
Step #3:  Do a search and replace all paths to your old bundle where the “namespace” and “use” declarations are and replace with the path to your new User Bundle.
Step #4:  Update your routes with the new paths, where applicable to make sure they are pointing to your new bundle’s controllers.
Step #5:  Clear your cache with “app/console ca:cl –env=prod”  then “app/console ca:cl”
Step #6:  Type in the URL that matches the path that will execute the controller code with the “app_dev.php” debug in the URL and test for errors.
Step #7:  Congratulate yourself for having the foresight to using a PHP framework, imagine the logistics if you wanted to move your code from some home-grown system what pitfalls you could have encountered.  Aren’t Symfony web developers just luckier than the plain old PHP developer variety?  Well, we’re luckier because there are tools and helpers already waiting for us so we can focus on writing code that creates solutions, not writing the code that helps us write the code to create solutions.  All you companies with your own Web Development department — are you getting this yet?!  If not, your loss.  Literally….