1

The following query in Symfony2.6 works with getArrayResult(); BUT doesn't work with getResult(); or getOneOrNullResult();. This means that I can't fetch an object but only an array.
If I use this query, all I get is a white/blank page (not even the symfony debug toolbar). Note that in my twig template I just do a {{ dump() }} of the query result.

The table structure is easy (it's a list of books read by the users):
id, user, book, status, vote, review
(user, book and status are foreign keys)

public function selectOneRecordBy2Ids($user_id, $book_id)
{
    /* @var $qb QueryBuilder */
    $qb = $this->createQueryBuilder('l');
    $qb->select('l');
    $qb = $qb->Where($qb->expr()->eq('l.user', ':first'));
    $qb = $qb->andWhere($qb->expr()->eq('l.book', ':second'));
    $qb = $qb->setParameters(array('first' => $user_id, 'second' => $book_id));
    return $qb->getQuery()->getOneOrNullResult();



}
Improve this question
3
  • blank page means you there is some PHP error, so check your error.log files first. Commented Feb 11, 2015 at 7:34
  • Have you try to force the idratation mode calling return $qb->getQuery()->getOneOrNullResult(Query::HYDRATE_OBJECT); Commented Feb 11, 2015 at 7:41
  • 1
    Thank for your help... please read my comments under the first answer.. it seems the error is not in the query but in the result dump with twig {{ dump(var) }}. Commented Feb 11, 2015 at 9:17

2 Answers 2

Reset to default
1

I've noticed a few bad practices here, so let me correct them:

public function selectOneRecordBy2Ids(User $user, Book $book)
{
    /* @var $qb QueryBuilder */
    $qb = $this->createQueryBuilder('l');
    $qb
        ->andWhere($qb->expr()->eq('l.user', ':first'))
        ->andWhere($qb->expr()->eq('l.book', ':second'))
        ->setParameters(array('first' => $user, 'second' => $book));
    return $qb->getQuery()->getResult();
}

Select is not necessary if you work only with one entity and you don't fetch any relations. QB returns $this so you can chain the method calls.

Try to use entities as parameters instead of primitive types (if it is possible). If not, then you have to use primitive types as primitives in QB. In this case you'll need a few joins:

->select('l')
->join('l.user', 'u')
->join('l.book', 'b')
->andWhere($qb->expr()->eq('u.id', ':first'))
->andWhere($qb->expr()->eq('b.id', ':second'))
->setParameters(array('first' => $user_id, 'second' => $book_id));

If you want to fetch only one record, then you may have to limit the results by setting the max results:

->setMaxResults(1)

I hope this helps and solves your original problem as well.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

First of all, thank you very much for the good advices. I tried the second method but it did not work (probably my fault). P.s. Are you sure that I should use u.id and b.id instead of l.user and l.book?
As l.user and l.book indicates entities and not primitives types I believe joining the tables and referencing their id-s is the best practice here. Can you post your entity in your question? The problem might be there as I cannot see, why your query fails.
I just discovered something... if in another twig file I use {{ dump(query_result) }} with a query that works fine I get the same error. So, I think that the problem is NOT the query itself but the var_dump twig function. For example see this mossco.co.uk/symfony-2/how-to-fix-twig-whitescreen-on-dump-var . The strange thing is that this does not happen with getArrayResult
Which is the best way to inspect the data retrieved by a query like the above ones (with getResult or getOneOrNullResult)? {{ dump(var) }} doesn't work for me, and php var_dump prints something not very easy to read.
Usually I use xdebug in such cases. Here is a tutorial for setting up xdebug and debugging with PhpStorm: jetbrains.com/phpstorm/help/configuring-xdebug.html but of course you can configure it to debug with Netbeans as well.
|
0

getOneOrNullResult() does not return the record, but tells you if any record in database is found, or not. You might want to use getSingleResult()

Improve this answer

1 Comment

Actually it returns a row if there is any result. The difference between the two methods is if there is no result, then getSingleResult() throws and exception whereas getOneOrNullResult() simply returns null.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.