Get only few records depend on condition in collection of forms in Symony 2+

Question

I have two table say Project and Project allocation.

If we create form of Project object we can also get all project allocation by default.

class projectType extends AbstractType {
......

->add('projectAllocation', 'collection',
                                array(
                                        'type' => new \ProjectBundle\Form\Type\projectAllocationType(),
                                        'allow_add' => true, 'allow_delete' => true,
                                        'prototype' => true,))

...

Simlarly we have projectAllocationType form.

Now in this case all project allocation records will be retrieved in the form.

Can we have something like project allocation record which satisfy few condition like between some date etc.

Basically it is like search and edit if we have lot of collection record

Any suggestion will be helpful to implement same.

Edited

$qb=$repository->createQueryBuilder("p")
            //->from('Project', 'p')
            ->leftJoin('p.billingItems', 'b')
            ->leftJoin('b.invoice', 'inv')
            ->where('p.projectId = ?1')
            ->orderBy('p.name', 'ASC')
            ->setParameter(1, $projectId);



 if($invoice){
                $qb->andWhere('inv.invoiceId='.$invoice);
            // $queryString .= "and inv.invoiceId=".$invoice;
        }

$query->getSql() query is proper.

SELECT p0_.project_id AS project_id0, p0_.name AS name1, b1_.billing_items_id
FROM project p0_
LEFT JOIN billing_items b1_ ON p0_.project_id = b1_.project_id
LEFT JOIN invoice i2_ ON b1_.invoice_id = i2_.invoice_id
WHERE p0_.project_id =171
AND i2_.invoice_id =3
ORDER BY p0_.name ASC
LIMIT 0 , 30

Here billing_items_id manually added while directly executing query on DB which displayed proper result.

But Doctrine query project object contains all billing items. I need only items which equal to invoice.

Make a query for your projects, left join your allocations and then apply your where conditions so you only get the allocations you want. Then pass projects to your form. The form itself does not have any filtering capability. — Cerad, Sep 17 '14 at 14:10
okay...so if we query before passing object to form works? let me try — stefun, Sep 17 '14 at 14:20
[http://stackoverflow.com/questions/19868332/better-to-pass-complete-object-or-just-a-required-value-in-php-symfony-2] Better to pass complete object or just a required value in PHP (Symfony 2) Suggest full object is better — stefun, Sep 19 '14 at 07:59
I don't think you really understand how relations work. Your posted link deals with partial objects and is irrelevant. Likewise, your posted query shows a lack of familiarity with Doctrine 2's query tools. And asking the same question twice will probably not help much. Consider going through http://symfony.com/doc/current/book/doctrine.html and http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html paying particular attention to joining your relations. — Cerad, Sep 19 '14 at 14:58
By the way, there is no particular reason that you need to use only one form to show all your product information. You can create a form that, for example, only shows allocation items. — Cerad, Sep 19 '14 at 14:59
Please check edited part. Now Little better performance since I’m including only required collection in form definition. Still I need to filter particular collection .In this example BillingItem — stefun, Sep 22 '14 at 11:27

score 0 · Answer 1 · answered Sep 23 '14 at 11:25

Adding ->select('p,b') solved my problem. To consider both entity data we have to include both entity reference.

$qb=$repository->createQueryBuilder("p")
                ->select('p,b')
                ->leftJoin('p.billingItems', 'b')
                ->leftJoin('b.invoice', 'inv')
                ->where('p.projectId = ?1')
                ->orderBy('p.name', 'ASC')
                ->setParameter(1, $projectId);
    if($invoice){
         $qb->andWhere('inv.invoiceId='.$invoice);
    }

Get only few records depend on condition in collection of forms in Symony 2+

1 Answers1