CODEWITHSUNDEEP
mysqlsymfonydoctrine-ormdql
Cawa
Cawa

Reputation: 1299

Doctrine DQL to QueryBuilder

maybe someone can help me to transorm

I'm trying to get QuestionContent that are not in relation with theFaqPageQuestionContent by page_id and view it in the select box

SELECT q FROM VswSystemCmsBundle:QuestionContent q WHERE q.id NOT IN
        (SELECT fq.questioncontent_id FROM VswSystemCmsBundle:FaqPageQuestionContent fq WHERE fq.faqcontentpage_id = :page_id)

into QueryBuilder form to use it in the Sf2 forms.

Upvotes: 1

Views: 1194

Answers (3)

Jason Roman
Jason Roman

Reputation: 8276

Now that you clarified what you want to do, I would suggest using an 'entity' field type on your form that connects to your QuestionContent entity.

// don't forget the use statement for your repository up top
use Your\VswSystemCmsBundle\Repository\QuestionContentRepository;

// in buildForm() (this assumes you have $pageId set properly)
$builder
    ->add('questionContent', 'entity', array(
        'class'         => 'VswSystemCmsBundle:QuestionContent',
        'property'      => 'questionForForm',
        'query_builder' => function(QuestionContentRepository $repo) use ($pageId) { 
            return $repo->findNotAttachedQuestions($pageId);
        },
    ))
;

EDIT: Put your QueryBuilder in that entity's Repository and call it from there.

// this is Your\VswSystemCmsBundle\Repository\QuestionContentRepository class
public function findNotAttachedQuestions($pageId)
{
    $subQuery = $this->createQuery("
            SELECT  fq.questioncontent_id 
            FROM    VswSystemCmsBundle:FaqPageQuestionContent fq
            WHERE   fq.faqcontentpage_id = :page_id
        ")
        ->setParameter('page_id', $pageId)
    ;

    return $this->createQueryBuilder('q')
        ->where($qb->expr()->notIn('q.id', $subQuery))
    ;
}

Notice how I defined 'property' above to be questionForForm? We need to add a getter function to your QuestionContent entity that returns the first 30 characters of the question.

// this is Your\VswSystemCmsBundle\Entity\QuestionContent class
public function getQuestionForForm()
{
    return substr($this->getQuestion(), 0, 30);
}

Now everything is separated in the right place. You don't have to worry about converting your DQL to a Doctrine QueryBuilder instance, because you have it in the Repository and call it form there. You don't have to make a custom array to return your data for the select, and you now have a reusable function that returns the first 30 characters of the question, straight from the Entity.

Upvotes: 2

Cawa
Cawa

Reputation: 1299

Hm, I think I'll better will use DQL + something like this in form class and call if form 'choices' => $this->getNotAttachedQuestions($builder->getData()->getId()),:

public function getNotAttachedQuestions($pageId)
{
    $questions = $this->getQuestionRepo()->getNotAttachedQuestions($pageId);
    $select = [];
    foreach ($questions as $question) {
        $select[$question->getId()] = substr($question->getQuestion(),0,30);
    }

    return $select;
}

Upvotes: 1

xurshid29
xurshid29

Reputation: 4210

Something like this (not tested, may be field names are wrong, correct them):

$subQueryBuilder = $this->getEntityManager()->createQueryBuilder();
$subQuery = $subQueryBuilder
    ->select(['fq.questioncontentId'])
    ->from('VswSystemCmsBundle:FaqPageQuestionContent', 'fq')
    ->where('fq.faq = :faq')
    ->setParameter('faq', $faq)
;

$queryBuilder = $this->getEntityManager()->createQueryBuilder();
$query = $queryBuilder
    ->select(['q'])
    ->from('VswSystemCmsBundle:QuestionContent', 'q')
    ->where($queryBuilder->expr()->notIn('q.id', $subQuery->getDQL()))
    ->getQuery()
;

return $query->getResult();

Upvotes: 2

Related Questions