CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-ormdoctrine
dariogz.com
dariogz.com

Reputation: 132

How to select all columns with Doctrine QueryBuilder?

I have the entities Store, Owner and Town and i want to count all the stores that an owner has, categorized by their Town.

I have this Query in my Controller

$query = $this->getDoctrine()->getRepository('WebBundle:Store')
   ->createQueryBuilder('s')
     ->select('t.name, COUNT(s) as counter')
     ->groupBy('s.town')
     ->leftJoin('s.owner','o')
     ->leftJoin('s.town','t')
     ->where('s.owner = :id')
     ->orderBy('t.name','ASC')
     ->setParameter('id', $id)
  ->getQuery();

$list = $query->getResult();

Is there any way to select all columns from Town instead of declaring every column? Something like ->select('t.*, COUNT(s) as counter'). I can select the ones i need now, but for larger tables I will need other method.

I've tried ->select('t, COUNT(s) as counter') but I got an exception error.

For further information, in my twig template i want to show this:

{% for town in list %}
    <span>{{ town.name }}</b> [{{ town.counter }}]</span>
{% endfor %}

Thanks for all in advice!

Upvotes: 4

Views: 24593

Answers (2)

Poiz
Poiz

Reputation: 7617

You can select all by omitting the column name like or the match-all wild-card. So, instead of t.name or t.*, you can simply do t Like So: 

        $query = $this->getDoctrine()->getRepository('WebBundle:Store')
              ->createQueryBuilder('s')
              ->select('t, COUNT(s) AS counter')
              ->groupBy('s.town')
              ->leftJoin('s.owner','o')
              ->leftJoin('s.town','t')
              ->where('s.owner = :id')
              ->orderBy('t.name','ASC')
              ->setParameter('id', $id)
              ->getQuery();

            $list = $query->getResult();

Upvotes: 2

ceadreak
ceadreak

Reputation: 1684

I guess you have some relations in your entities.

Owner must have a 1-n relation with Store.

So, your Owner entity will be looks like this :

class Owner
{
    protected $stores;

    // ...

    public function __construct()
    {
        $this->stores = new ArrayCollection();
    }

    public function getStores()
    {
        return $this->stores;
    }

    public function setStores($stores)
    {
        $this->stores = new ArrayCollection();

        foreach ($stores as $store)
        {
            $this->stores->add($store);
        }

        return $this;
    }

    public function addStore(Store $store) // ... can use $this->store->add()

    public function removeStore(Store $store) // ... can use $this->store->removeElement()

    // etc ...

}

So now, you can use the Collection::count() Doctrine method !

$storesCnt = $user->getStores()->count();

You want to get all stores for a user and a town ? No problem ! Collection::filter() is your friend !

$storesForAUserAndAGivenTown = $user->getStores()->filter(function (Store $store) use ($town) {
    return ($store->getTown() === $town);
});

That's it.

Consider the first rule of Doctrine is Forget the database !, so use DQL or QueryBuilder only and only if it's necessary.

Hope it will help you.

Upvotes: 3

Related Questions