Tag: Doctrine

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


MySQL “Where IN” value contained in list used with setParameter() requires value to be array if using placeholders

07/22/2015 Aaron Belchamber No Comments

In normal SQL queries, you can use “WHERE IN” like this: “WHERE id IN (1,5,10)” as in:

If the list 1,5,10 “contains” the value represented by the value in the “id” field.

However, if you define a comma delimited string, you can’t simply do this:

$idListString=”1,5,10”

$dql=”SELECT d FROM Your/Entity d WHERE id IN (:idList)”

// and in your $dql query ->setParameter(”idList”=>$idListString)

This will only return the first result!   However, if $idList was an array $idListArray=array(1,5,10) then ->setParameter(“idList”=>$idListArray) works as expected.

This actually makes sense, the issue I found is that the Doctrine version of MySQL’s “WHERE… IN”  where a value is contained in a list is not well documented.  So web devs, especially Symfony developers beware!  PHP, Symfony, Magento, WordPress and MySQL experts will run into different web development issues — even as experienced experts, we’re always learning and improving.  This is just an obscure time saver, obscure probably  because it is very difficult to find search results and help related to the “MySQL IN” filter because the two-letter word “IN” is obviously omnipresent on the web in so many different contexts.  Searching “MySQL Contains IN filter” or something like that will help you find more information about the MySQL “IN” filter, function, operator — however it’s categorized.


Changing getArrayResult() to getArray() fixes weird Symfony Doctrine gotcha

06/18/2015 Aaron Belchamber No Comments

A strange Symfony Doctrine issue got me the other day.  I guess it’s a lack of clear understanding of how Doctrine hydrates database query results when using getArrayResult() instead of getArray().  It seems that getArrayResult() returns just the first record from your query, no matter how many records the query actually returns but getArray() returns an multi-dimensional array containing all results from the same query.

So, I needed a list of all records to display on screen, not just the first one.  All I did was change getArrayResult() to getArray() and this fixed my problem.

But why?!!  Can anyone please provide the answer?!  It’s not on Doctrine’s website, it’s not on stack.  I’ve Googled it I’ve Binged it, if I had nothing better to do maybe I’ll Duckduckgo it.  I’m afraid when I find the answer it will be so obvious that’s why I don’t get it.  I’ve read some users say it depends on what you are selecting in the query, if it’s the record index or some other value or combination of values but have not found any documentation explaining the differences clearly.  Doctrine’s horribly convoluted documentation  says this about getArrayResults():

Query#getArrayResult(): Retrieves an array graph (a nested array) that is largely interchangeable with the object graph generated by Query#getResult() for read-only purposes.

There’s money to be made — make an ORM that’s straightforward and intuitive.


Laravel PHP Framework: Basic Explanation of Eloquent, Doctrine and Fluent

02/28/2015 Aaron Belchamber No Comments

Sometimes, a story shares more information and conveys more insights than just spoon feeding people information.  I use Doctrine ORM a lot but was curious what Symfony-based Laravel framework used as a database ORM or if it even used one at all.  After all, in Symfony you can use custom ORM packages but Doctrine works best.  A few commands on the command line can “wire up” so much of your database and basic website functions, entities and CRUD.  I’ve read and seen others use Propel in Symfony just fine, but how does Laravel work?  A good question in case you ever are pulled into a project where the team went Laravel instead of Symfony, perhaps.  It’s always healthy to stay curious in this quick changing industry.

But what about Eloquent — and what exactly is Fluent anyway?!  Well, here’s a great thread and discussion that will lead you to some answers to the these inquisitive questions by some smart, experienced Laravel experts.  Just in case you were wondering, perhaps you were just searching for “anything but procedural code systems.”  If your company is open source and you don’t use Symfony or some other PHP framework, in two or three years you probably will have to if you plan on expanding and growing your web and data infrastructures. Be warned!

– Aaron Belchamber


Skipper ORM Database Tool Useful and Intuitive for Symfony Framework & Doctrine Users

02/12/2015 Aaron Belchamber No Comments

I’ll have to admit when I first started learning Symfony probably my biggest stumbling block was to get the right data from form collections to persist or update to the database correctly.  It really shouldn’t be so complex, I mean, I spent a week learning the intricacies with the “let the framework do it” mentality the whole time knowing I had built my own database framework to handle this type of database management and querying — all I had to do was send this class I built with a command and the objects and it knew what to do — INSERT, UPDATE or DELETE.  It’s not rocket science.

DBALs will manage the interactions with the database, just trust in it.  Let go, Luke….

I get the whole wisdom behind using a database abstraction layer (DBAL) to manage the boring parts of code and standardize the way to get data in and out, but good golly, to get a form collection of 6 tables that were related was quite a bear.  It just wasn’t clear to me what the heck was going on under the hood with Symfony whispering to Mr. Doctrine, so data submitted from forms that were supposed to update instead inserted new records, etc.  It was a big pain, mostly because I just didn’t trust the framework, which I’ve learned from trial and error to really come to appreciate.

So I ran into two issues in my learning process:

  1. Trust the framework to connect the dots, you know, the typical insert customer’s account record then with that ID put it in with their address record, their sales records, etc.  Yeah, you don’t have to write any code to do that, it’s all about making sure your form collections all set up with relationships properly.  The joke is Symfony developers spend as much time trying to get their config files to work as they do actually writing code.  That means every table is an object, every object is represented as an entity and every entity has a Doctrine ORM mapping file.  You have to make sure that the owning entity of other objects can add array collections or you won’t get too far with setting sub-objects.
  2. YML mapping files and those pesky “one-to-one”, “one-to-many”, and “many-to-one” relationships.  These are where the “wiring” happens and where Symfony tells Doctrine that those certain entities are related.  It can become dizzying setting up these relationships properly.  Why can’t it just be straight forward and easy?!  Well, it makes a lot more sense to me for the most part, but I’d still like to ask Mr. Potencier for those two weeks of hell where all I was trying to do was get the form data to go into the database correctly!

Then I found Skipper to help with those entity mappings!

The joke is Symfony developers spend as much time trying to get their config files to work as they do actually writing code.  Skipper is a full database design tool with similar functions to MySQL Workbench or SQL Developer.  The one thing that set Skipper apart when I downloaded a fully-functioning trial version was how it helped me visualize and understand those menacing ORM relationships.  One to many, many to one, blah blah blah.  Can’t the framework just know based on the field names?!  I mean, “contact_id” and “customer_id” can both own each other, but if the “Customer” table has “contact_id” for a field and the “Contact” table doesn’t, what more information does it need?!  Well, if you have a development team new to Symfony and you’re going to use Symfony, you can cut back on the time, energy and hair they pull from their heads working on those ORM mappings if you have them design the database inside Skipper and export the database, entities and mappings from there.

I don’t mean to beat Symfony up, I’ve learned to really appreciate its RAD implications and how much faster I can get a fully functioning, complex website up and running, but man-alive this was a struggle.  I know I’m not the smartest bulb in the tool box (purposefully confused analogy to accentuate the fact how dumb I felt) but this should’ve been easier!  Anyway, using Skipper and bringing in my database design into Skipper helped me understand and clear up what was just fogging up my brain.  Then it clicked.  I got it, didn’t even need Skipper after I had that “aha” moment where it just all started making sense.

Disclaimer:  I didn’t actually go and buy Skipper…. yet.  I have tackled a few large and complex projects since it taught me and showed me the “Doctrine Way” and for that I am very grateful.  The first sign I think I’m going to need to speed up the process, I’m definitely going to go and buy their newest version, I just can’t justify the expense at the moment.  At $395, I don’t think that’s particularly steep for the tools it gives you and the time and frustration it will save you and your development team later, it’s there if I need it and that’s a comforting thought especially since everything I’ve delved into Symfony it’s still how it interacts with the database and updates data that I sometimes don’t trust fully.

Check out this article where I complain more about this with Symfony — all you expert Symfony users out there, please spare me the “noob” comments!  🙂  Perhaps it’s the need to “merge” and having to divert code in the controller and sometimes clearing the entity manager at seemingly arbitrary times to get that part to work, or it’s just me, still needing more practice to learn just in time for them to deprecate the procedures they’ve been touting to a simplified work flow that hopefully makes sense like most of the rest of Symfony’s components.  We’ll just have to wait and see, 3.0 is coming from Sensio Labs very soon….

 

– Aaron Belchamber