Datagrid with query builder and bind parameters

Hello guys,

I need to display a custom entity datagrid in the customer datagrid.

I used the query_builders parameters in datagrid configuration

YAML

customer-reward-point-grid: source: type: orm query_builder: '@reward_point.customer_reward_point.repository->findByCustomerId' bind_parameters: - customer_id columns: type: label: Type rate: label: Rate frontend_type: number description: label: Description

1 2 3 4 5 6 7 8 9 10 11 12 13 14

customer-reward-point-grid:         source:             type: orm             query_builder: '@reward_point.customer_reward_point.repository->findByCustomerId'             bind_parameters:                - customer_id         columns:             type:                 label: Type             rate:                 label: Rate                 frontend_type: number             description:                 label: Description

I tried to link a request parameter to it (Id of current customer) to pass it to the request but when I dump this parameter in the query I obtain the code of the datagrid(customer-reward-point-grid) and not the value of my request:

PHP

/** * @param $customerId * * @return \Doctrine\ORM\QueryBuilder */ public function findByCustomerId($customerId) { $queryBuilder = $this->createQueryBuilder('crp') ->select('crp.earnedPoints, crp.spentPoints, rp.type, rp.rate, rp.description') ->where('c.id = :customer_id') ->join('crp.customer', 'c') ->join('crp.rewardPoint', 'rp') ->setParameter('customer_id', $customerId); dump($customerId); return $queryBuilder; }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

/**      * @param $customerId      *      * @return \Doctrine\ORM\QueryBuilder      */     public function findByCustomerId($customerId)     {         $queryBuilder = $this->createQueryBuilder('crp')             ->select('crp.earnedPoints, crp.spentPoints, rp.type, rp.rate, rp.description')             ->where('c.id = :customer_id')             ->join('crp.customer', 'c')             ->join('crp.rewardPoint', 'rp')             ->setParameter('customer_id', $customerId);         dump($customerId);         return $queryBuilder;     }

Can someone help me?

Thanks and have a nice day!

Topic

[BenjaminParticipant]

I solve it: no need to specify the condition and the parameter in the query when using bind_parameters.

PHP

/** * @return \Doctrine\ORM\QueryBuilder */ public function findByCustomerId() { $queryBuilder = $this->createQueryBuilder('crp') ->select('crp.earnedPoints, crp.spentPoints, rp.type, rp.rate, rp.description') ->join('crp.customer', 'c') ->join('crp.rewardPoint', 'rp'); return $queryBuilder; }

1 2 3 4 5 6 7 8 9 10 11 12

/**      * @return \Doctrine\ORM\QueryBuilder      */     public function findByCustomerId()     {         $queryBuilder = $this->createQueryBuilder('crp')             ->select('crp.earnedPoints, crp.spentPoints, rp.type, rp.rate, rp.description')             ->join('crp.customer', 'c')             ->join('crp.rewardPoint', 'rp');           return $queryBuilder;     }

But now I have another error:

Getting grid data failed.
Context: { “exception”: “Exception(Doctrine\\ORM\\Query\\QueryException): [Semantical Error] line 0, col 11 near ‘customer, crp.rewardPoint’: Error: Invalid PathExpression. Must be a StateFieldPathExpression.” }

Anyone have an idea? I already had this error when using the query from the datagrid.yml (without the query_builders parameter).

[dzakharovParticipant]

Hi! Are you sure its valid name customer_id maybe when you rendering grid you use another name? For example try to use like:

PHP

bind_parameters: customerId: customer_id

1 2	bind_parameters:     customerId: customer_id

More information about parameter binding you can read in https://github.com/oroinc/platform/blob/master/src/Oro/Bundle/DataGridBundle/Resources/doc/backend/parameter_binding.md

[BenjaminParticipant]

Hello dzakharov and thanks for the fast reply!

I solved my parameter binding issue in my first post but now I have the error raised in my second post I don’t think it is related to the first one.

[dzakharovParticipant]

Also if you want to use datagrids without query_builders, you can try write like:

PHP

customer-reward-point-grid: source: type: orm query: select: [crp.earnedPoints, crp.spentPoints, rp.type, rp.rate, rp.description] from: - { table: YoursBundle:Entity, alias: crp } join: inner: - { join: crp.customer, alias: c } - { join: crp.rewardPoint, alias: rp } where: and: - c.id = :customer_id bind_parameters: - customer_id

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

customer-reward-point-grid:         source:             type: orm             query:                select: [crp.earnedPoints, crp.spentPoints, rp.type, rp.rate, rp.description]                from:                   - { table: YoursBundle:Entity, alias: crp }                join:                   inner:                      - { join: crp.customer, alias: c }                      - { join: crp.rewardPoint, alias: rp }                where:                   and:                       - c.id = :customer_id             bind_parameters:                - customer_id

Documentation with examples how use datagrids: https://github.com/oroinc/platform/blob/master/src/Oro/Bundle/DataGridBundle/Resources/doc/backend/datagrid.md

[BenjaminParticipant]

Hello dzakharov,

I read the data grids doc, at my first attempt I even applied the method in your last comment with datagrid without query_builders. I obtained exactly the same result as the error in my second post :

Getting grid data failed.
Context: { “exception”: “Exception(Doctrine\\ORM\\Query\\QueryException): [Semantical Error] line 0, col 11 near ‘customer, crp.rewardPoint’: Error: Invalid PathExpression. Must be a StateFieldPathExpression.” }

So I tried to build my grid with the query_builders parameters…but now I have this error too with this configuration and I don’t know how to solve it.

Thanks for your reply,

Regards

[jderdziakParticipant]

Hello Benjamin,

Are you able to provide full example that causing this error for you (so all Entities, current Repository and current grid configuration)?

Regards
Jakub

[BenjaminParticipant]

Hello jderziak,

Thanks for your reply.
Here it is:

RewardPoint Entity:

PHP

/** * @ORM\Entity() * @ORM\Table(name="reward_point") * * Class RewardPoint */ class RewardPoint { /** * @ORM\Id() * @ORM\GeneratedValue(strategy="AUTO") * @ORM\Column(type="integer") * * @var integer $id */ private $id; /** * @ORM\Column(type="float") * * @var float $rate */ private $rate; /** * @ORM\Column(type="string") * * @var string $type */ private $type; /** * @ORM\Column(type="text") * * @var string $description */ private $description; /** * @ORM\Column(type="datetime", name="start_date", nullable=true) * * @var \DateTime $startDate */ private $startDate; /** * @ORM\Column(type="datetime", name="end_date", nullable=true) * * @var \DateTime $endDate */ private $endDate; /** * @return int */ public function getId() { return $this->id; } /** * @return float */ public function getRate() { return $this->rate; } /** * @param float $rate */ public function setRate($rate) { $this->rate = $rate; } /** * @return string */ public function getType() { return $this->type; } /** * @param string $type */ public function setType($type) { $this->type = $type; } /** * @return string */ public function getDescription() { return $this->description; } /** * @param string $description */ public function setDescription($description) { $this->description = $description; } /** * @return \DateTime */ public function getStartDate() { return $this->startDate; } /** * @param \DateTime $startDate */ public function setStartDate($startDate) { $this->startDate = $startDate; } /** * @return \DateTime */ public function getEndDate() { return $this->endDate; } /** * @param \DateTime $endDate */ public function setEndDate($endDate) { $this->endDate = $endDate; } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136

/** * @ORM\Entity() * @ORM\Table(name="reward_point") * * Class RewardPoint */ class RewardPoint {       /**      * @ORM\Id()      * @ORM\GeneratedValue(strategy="AUTO")      * @ORM\Column(type="integer")      *      * @var integer $id      */     private $id;     /**      * @ORM\Column(type="float")      *      * @var float $rate      */     private $rate;     /**      * @ORM\Column(type="string")      *      * @var string $type      */     private $type;     /**      * @ORM\Column(type="text")      *      * @var string $description      */     private $description;     /**      * @ORM\Column(type="datetime", name="start_date", nullable=true)      *      * @var \DateTime $startDate      */     private $startDate;     /**      * @ORM\Column(type="datetime", name="end_date", nullable=true)      *      * @var \DateTime $endDate      */     private $endDate;       /**      * @return int      */     public function getId()     {         return $this->id;     }       /**      * @return float      */     public function getRate()     {         return $this->rate;     }       /**      * @param float $rate      */     public function setRate($rate)     {         $this->rate = $rate;     }       /**      * @return string      */     public function getType()     {         return $this->type;     }       /**      * @param string $type      */     public function setType($type)     {         $this->type = $type;     }       /**      * @return string      */     public function getDescription()     {         return $this->description;     }       /**      * @param string $description      */     public function setDescription($description)     {         $this->description = $description;     }       /**      * @return \DateTime      */     public function getStartDate()     {         return $this->startDate;     }       /**      * @param \DateTime $startDate      */     public function setStartDate($startDate)     {         $this->startDate = $startDate;     }       /**      * @return \DateTime      */     public function getEndDate()     {         return $this->endDate;     }       /**      * @param \DateTime $endDate      */     public function setEndDate($endDate)     {         $this->endDate = $endDate;     } }

RewardPointCustomer Entity:

PHP

/** * @ORM\Entity(repositoryClass="Acme\Bundle\RewardPointsBundle\Repository\CustomerRewardPointRepository") * @ORM\Table(name="reward_point_customer") * * Class RewardPointCustomer */ class RewardPointCustomer { /** * @var integer $customer * * @ORM\Id() * @ORM\ManyToOne(targetEntity="Oro\Bundle\CustomerBundle\Entity\Customer") */ private $customer; /** * @var integer $rewardPoint * * @ORM\Id() * @ORM\ManyToOne(targetEntity="Acme\Bundle\RewardPointsBundle\Entity\RewardPoint") */ private $rewardPoint; /** * @ORM\Column(type="integer", name="earned_points", nullable=true) * * @var integer $earnedPoints */ private $earnedPoints; /** * @ORM\Column(type="integer", name="spent_points", nullable=true) * * @var integer $spentPoints */ private $spentPoints; /** * @return int */ public function getCustomer() { return $this->customer; } /** * @param int $customer */ public function setCustomer($customer) { $this->customer = $customer; } /** * @return int */ public function getRewardPoint() { return $this->rewardPoint; } /** * @param int $rewardPoint */ public function setRewardPoint($rewardPoint) { $this->rewardPoint = $rewardPoint; } /** * @return mixed */ public function getEarnedPoints() { return $this->earnedPoints; } /** * @param mixed $earnedPoints */ public function setEarnedPoints($earnedPoints) { $this->earnedPoints = $earnedPoints; } /** * @return int */ public function getSpentPoints() { return $this->spentPoints; } /** * @param int $spentPoints */ public function setSpentPoints($spentPoints) { $this->spentPoints = $spentPoints; } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102

/** * @ORM\Entity(repositoryClass="Acme\Bundle\RewardPointsBundle\Repository\CustomerRewardPointRepository") * @ORM\Table(name="reward_point_customer") * * Class RewardPointCustomer */ class RewardPointCustomer {       /**      * @var integer $customer      *      * @ORM\Id()      * @ORM\ManyToOne(targetEntity="Oro\Bundle\CustomerBundle\Entity\Customer")      */     private $customer;     /**      * @var integer $rewardPoint      *      * @ORM\Id()      * @ORM\ManyToOne(targetEntity="Acme\Bundle\RewardPointsBundle\Entity\RewardPoint")      */     private $rewardPoint;     /**      * @ORM\Column(type="integer", name="earned_points", nullable=true)      *      * @var integer $earnedPoints      */     private $earnedPoints;     /**      * @ORM\Column(type="integer", name="spent_points", nullable=true)      *      * @var integer $spentPoints      */     private $spentPoints;       /**      * @return int      */     public function getCustomer()     {         return $this->customer;     }       /**      * @param int $customer      */     public function setCustomer($customer)     {         $this->customer = $customer;     }           /**      * @return int      */     public function getRewardPoint()     {         return $this->rewardPoint;     }       /**      * @param int $rewardPoint      */     public function setRewardPoint($rewardPoint)     {         $this->rewardPoint = $rewardPoint;     }       /**      * @return mixed      */     public function getEarnedPoints()     {         return $this->earnedPoints;     }       /**      * @param mixed $earnedPoints      */     public function setEarnedPoints($earnedPoints)     {         $this->earnedPoints = $earnedPoints;     }       /**      * @return int      */     public function getSpentPoints()     {         return $this->spentPoints;     }       /**      * @param int $spentPoints      */     public function setSpentPoints($spentPoints)     {         $this->spentPoints = $spentPoints;     } }

Datagrid:

YAML

customer-reward-point-grid: source: type: orm query_builder: '@reward_point.customer_reward_point.repository->findByCustomerId' bind_parameters: - customer_id columns: type: label: Type rate: label: Rate frontend_type: number description: label: Description

1 2 3 4 5 6 7 8 9 10 11 12 13 14

customer-reward-point-grid:         source:             type: orm             query_builder: '@reward_point.customer_reward_point.repository->findByCustomerId'             bind_parameters:                - customer_id         columns:             type:                 label: Type             rate:                 label: Rate                 frontend_type: number             description:                 label: Description

CustomerRewardPointRepository used:

PHP

/** * @return \Doctrine\ORM\QueryBuilder */ public function findByCustomerId() { $queryBuilder = $this->createQueryBuilder('crp') ->select('crp.earnedPoints, crp.spentPoints, rp.type, rp.rate, rp.description') ->join('crp.customer', 'c') ->join('crp.rewardPoint', 'rp'); return $queryBuilder; }

1 2 3 4 5 6 7 8 9 10 11 12

/**      * @return \Doctrine\ORM\QueryBuilder      */     public function findByCustomerId()     {         $queryBuilder = $this->createQueryBuilder('crp')             ->select('crp.earnedPoints, crp.spentPoints, rp.type, rp.rate, rp.description')             ->join('crp.customer', 'c')             ->join('crp.rewardPoint', 'rp');           return $queryBuilder;     }

I suspect a misconfiguration in my entity in the relation with oro customer entity.

Again thanks for taking your time for this!

[jderdziakParticipant]

Hello Benjamin,

Looks like your entity primary key is causing error:

PHP

/** * @var integer $customer * * @ORM\Id() * @ORM\ManyToOne(targetEntity="Oro\Bundle\CustomerBundle\Entity\Customer") */ private $customer; /** * @var integer $rewardPoint * * @ORM\Id() * @ORM\ManyToOne(targetEntity="Acme\Bundle\RewardPointsBundle\Entity\RewardPoint") */ private $rewardPoint;

1 2 3 4 5 6 7 8 9 10 11 12 13 14

/**      * @var integer $customer      *      * @ORM\Id()      * @ORM\ManyToOne(targetEntity="Oro\Bundle\CustomerBundle\Entity\Customer")      */     private $customer;     /**      * @var integer $rewardPoint      *      * @ORM\Id()      * @ORM\ManyToOne(targetEntity="Acme\Bundle\RewardPointsBundle\Entity\RewardPoint")      */     private $rewardPoint;

As a walkaround you may remove this primary key (use UNIQUE instead) and create normal “id” field.

PHP

/** * @ORM\Id() * @ORM\GeneratedValue(strategy="AUTO") * @ORM\Column(type="integer") * * @var integer $id */ private $id; /** * @var \Oro\Bundle\CustomerBundle\Entity\Customer $customer * * @ORM\ManyToOne(targetEntity="Oro\Bundle\CustomerBundle\Entity\Customer") * @ORM\JoinColumn(name="customer_id", referencedColumnName="id", onDelete="CASCADE") */ private $customer; /** * @var \AcmeBundle\Entity\RewardPoint $rewardPoint * * @ORM\ManyToOne(targetEntity="AcmeBundle\Entity\RewardPoint") * @ORM\JoinColumn(name="rewardPoint_id", referencedColumnName="id", onDelete="CASCADE") */ private $rewardPoint;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

/**      * @ORM\Id()      * @ORM\GeneratedValue(strategy="AUTO")      * @ORM\Column(type="integer")      *      * @var integer $id      */     private $id;       /**      * @var \Oro\Bundle\CustomerBundle\Entity\Customer $customer      *      * @ORM\ManyToOne(targetEntity="Oro\Bundle\CustomerBundle\Entity\Customer")      * @ORM\JoinColumn(name="customer_id", referencedColumnName="id", onDelete="CASCADE")      */     private $customer;     /**      * @var \AcmeBundle\Entity\RewardPoint $rewardPoint      *      * @ORM\ManyToOne(targetEntity="AcmeBundle\Entity\RewardPoint")      * @ORM\JoinColumn(name="rewardPoint_id", referencedColumnName="id", onDelete="CASCADE")      */     private $rewardPoint;

In the meantime I’ll try to find out if it’s a correct behavior.

[jderdziakParticipant]

Hello again!

I’ve confirmed this is a bug, but since it’s not recommended to create Primary Key from reference columns, it has quite low priority and I’m not able to predict how fast the fix will be implemented.

Regards
Jakub

[BenjaminParticipant]

Hello jderdziak!

Thanks for your help!

I will create a normal Id field then!
I will open an issue but I agree with you it should be a low priority since it’s not the best practice to do it.

Have a great week!

Regards

[Author]

Replies