Set select values by code

I wanted to use a 'select' column but the list of values and labels didn't come from source. It came from external parameters.
Looking in the documentation, it seems to be possible to defined the 'values' only in entity annotations.
To do it by code, I found in SelectColumn class a getValues() method but not setvalues().
So I created it

public function setValues($values)
{
    return $this->values = $values;
}

and modified the setData() to

public function setData($data)
{
    if ((is_string($data) || is_integer($data)) && $data != $this::BLANK)
    {
        $this->data = $data;
    }

    return $this;
}

and caled it by

$grid->getColumns()->getColumnById('status')->setValues($values);

and it run fine.

But doing this could disturb others using. Can somebody confirm it is or not not problematic ?
If no maybe it would be interesting to add it in next commit.

Why do you have to delete this part of this condition in the setData method?

&& key_exists($data, $this->values)

You can create a new class like the SourceSelectColumn class too.

I deleted it because my setdata() call is done after the setSource(). And it is in the setSource() that column values are initialized. If I don't delete the '&& key_exists($data, $this->values)', my filter doesn't work anymore (but column rendering is good).

Of course I can create a new class if it won't be included in a next commit.

Ok, I understand your case. Oki you can do it.

I suppose SourceSelectColumn uses a doctrine source to populate de values of the select field.
Can you give us a sample ? I didn't find anything in the documentation.

Thanks

You only have to force the type of your column in the annotation:

@GRID\Column(type="sourceselect")

and implements the method findDistinctByField in your entity repository:

Your entity:

<?php
/**
  * @ORM\Entity(repositoryClass="MyProject\MyBundle\Entity\Repository\MemberRepository")
  */
class Member
{
...
}

Your entity Repository:

<?php
use Doctrine\ORM\EntityRepository;
use Sorien\DataGridBundle\Grid\Source\DistinctFieldRepositoryInterface;

class MemberRepository extends EntityRepository implements DistinctFieldRepositoryInterface
{
    public function findDistinctByField($field)
    {
        $alias = '_a';
        return $this->getEntityManager()->createQueryBuilder()
        ->Select("$alias.$field")
        ->distinct()
        ->from($this->getEntityName(), $alias)
        ->orderBy("$alias.$field", 'ASC')
        ->getQuery()
        ->getResult();
    }

And change this query for mapping fields, you have to deal with a join table, etc...

And yes it uses the doctrine source to populate the values of the select field.

We'll try later to implement it on the vector source.

Merci, ça fonctionne parfaitement

Par contre il ne fonctionne pas avec les entités ManyToOne du type

 @GRID\Column(field="techcenter.company", type='sourceselect", title="Company", groups="repair")

J'obtiens un message du genre

[Syntax Error] Expected PlainValue, got ''' at position 119 in property ...

C'est pour ça que je disais que pour faire un sourceselect sur un champs en jointure, mon exemple ne suffit pas. De même pour un champs avec une fonction d'aggrégation DQL.

Dans ton cas, quelque chose comme ça devrait faire affaire :

<?php
public function findDistinctByField($field)
{
    $field_ = explode('.', $field);
    $field = end($field_);

    return $this->getEntityManager()->createQueryBuilder()
        ->Select("_t.$field")
        ->distinct()
        ->from($this->getEntityName(), '_a')
        ->leftJoin('_a.techcenter', '_t')
        ->orderBy("_t.$field", 'ASC')
        ->getQuery()
        ->getResult();
}

Bref, ce n'est pas optimiser pour tous les cas mais les exemples donnés te permettront de faire ce que tu veux.

Pour info, il faut renvoyer un résultat avec le nom du champ complet.

une bidouille du style peut résoudre le pb mais c'est pas très pro

public function findDistinctByField($field)
{
    if ($field == 'techcenter.company') {
        $results =  $this->createQueryBuilder("r")->select("t.company")->distinct() ->join('r.techcenter', 't')->orderBy("t.company")->getQuery()->getResult();
        foreach($results as $key => $result) $results[$key]['techcenter.company'] = $results[$key]['company'];
        return $results;
     }...
}

La question est, mettre en place un système qui ne nécessite pas l'ajout d'une fonction de recherche par champs distinct ?

Abhoryo: I like you solution. The only problem is that it can't make joins automatically. May be this could lead to feature suggestion to doctrine development.

I would like to have as many joins as possible in distinctQuery

for example, when selecting from table a, column b.c.d.e
it would be splitted like

select distinct _d.e from A _a join _a.b _b join _b.c _c join _c.d _d
etc.

<?php
 public function findDistinctByField($field) {
        $alias = '_a';
        $parts = explode('.', $field);
        $c = count($parts);
        if( $c > 1 ) {
            $fname = '_' . $parts[$c-2] . '.' . $parts[$c-1];
        }
        else {
            $fname = "$alias.$field";
        }

        $query =  $this->getEntityManager()->createQueryBuilder()
            ->Select($fname)
            ->distinct()
            ->from($this->getEntityName(), $alias);

        // join all tables in the chain
        if( $c > 1) {
            for( $i = 0; $i < $c - 1; $i++) {
                $newAlias = '_' . $parts[$i];
                $query->innerJoin($alias . '.' . $parts[$i], $newAlias);
                $alias = $newAlias;
            }
        }

        $query->orderBy("$fname", 'ASC');

        $results = $query->getQuery()->getResult();

        // Parse results back into what SourceSelectColumn expects
        $mapped = array();
        foreach( $results as $result) {
            $mapped[] = array($field => $result[$parts[$c-1]]);
        }
        return $mapped;
    }
?>

I'll try your code.

I code DGB 2.0 and I've implemented a new dynamic select column which pick values from the query itself.

I have problems with dql functions.

It try this query
select count(*) as scrl1 from TrucMuche Where scrl1 = 12;
instead of
select count(*) as scrl1 from TrucMuche having count(*) = 12;

@Abhoryo

Is it possible to use this today? @Abhoryo

I have for example this column.

/**
 * @var Message[]
 * @GRID\Column(field="messages.text:last", title="Last logged message")
 *
 * @ORM\OneToMany(targetEntity="Message", mappedBy="Log", cascade={"all"}, orphanRemoval=true)
 */
private $messages;

But there is no ":last" function => also I get an error.

At the moment I use this function to render the cell.
But I want to sort and filter 👿

$lastMessage = new TextColumn(array('id' => 'lastLoggedMessage', 'title' => 'Last logged message'));
$lastMessage->manipulateRenderCell(function($value, $row, $router)
{
    /** @var $row \APY\DataGridBundle\Grid\Row */
    /** @var $router \Symfony\Bundle\FrameworkBundle\Routing\Router */
    try{return $row->getEntity()->getMessages()->last()->getMessage();}
    catch(Exception $ex) { return 'No message'; }
});
$grid->addColumn($lastState);

With that I can't order or search/filter the field...

Thanks for your help 👍

You should have a creation date for each message so you can use a DQL function to sort this column.

/**
 * @var Message[]
 * @GRID\Column(field="messages.add_date:max", title="Last logged message")
 *
 * @ORM\OneToMany(targetEntity="Message", mappedBy="Log", cascade={"all"}, orphanRemoval=true)
 */
private $messages;

@Abhoryo

@Abhoryo And how i could display the "message" field of the :max message?

like: messages:last.message
^^

Navigation Menu

Repository navigation

Set select values by code #113

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Set select values by code #113

Description

Activity

Abhoryo commented on Apr 23, 2012

olivier3400 commented on Apr 23, 2012

Abhoryo commented on Apr 23, 2012

olivier3400 commented on May 12, 2012

Abhoryo commented on May 12, 2012

olivier3400 commented on May 13, 2012

olivier3400 commented on May 13, 2012

Abhoryo commented on May 13, 2012

olivier3400 commented on May 14, 2012

Abhoryo commented on May 14, 2012

rfojta commented on May 29, 2012

rfojta commented on May 29, 2012

Abhoryo commented on May 29, 2012

Abhoryo commented on Jun 6, 2012

Bup3 commented on Mar 14, 2014

Abhoryo commented on Mar 14, 2014

Bup3 commented on Mar 14, 2014

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions