Symfony Doctrine : get field value in foreach loop on fetchAll()

Question

I'm trying to display some fields of a table 'Post' using a raw query :

$connection = $em->getConnection();
    $statement = $connection->prepare("SELECT * FROM Post WHERE category_id = 1");
    $statement->execute();
    $posts = $statement->fetchAll();

...

foreach($posts as $post) {
        $xml .= $post->getId();
        $xml .= $post->getTitle();
        $xml .= $post->getContent();
    }

I've got an error "FatalErrorException: Error: Call to a member function getId() on a non-object in ..." All those getters are right in my Post entity. Any suggestion about what I'm doing wrong ?

[EDIT]

$em = $this->getDoctrine()->getManager();

$post_repository = $em->getRepository('MyBundle:Post'); 
$posts = $post_repository->findBy(array('category_id' => 1));

foreach($posts as $post) {
        $xml .= $post->getTitle();
    }

Returns me "Unrecognized field: category_id".

My Post class :

class Post
{
/**
* @ORM\ManyToOne(targetEntity="MyBundle\Entity\Category", inversedBy="post")
* @ORM\JoinColumns({
*  @ORM\JoinColumn(name="category_id", referencedColumnName="id")
* })
*/
private $category;
/**
* Set category
*
@param MyBundle\Entity\Category $category
*/
public function setCategory(\MyBundle\Entity\Category $category)
{
    $this->category = $category;
}
/**
* Get category
*
@return MyBundle\Entity\Category
*/
public function getCategory()
{
    return $this->category;
}


/**
 * @var integer
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var string
 *
 * @ORM\Column(name="title", type="string", length=255)
 */
private $title;

....

Presumably your actual sql is (or will be) more complex than the code you have given here, as with symfony/doctrine raw queries are only rarely used when the higher level mechanisms are insufficient. A simple repository->findBy() would suffice for this sql — redbirdo, Nov 04 '13 at 11:20

score 2 · Accepted Answer · answered Nov 04 '13 at 11:13

2

Why using directly your connection here? You should consider using the entity repository for your "posts" class. For example :

$posts = $em->getRepository('YourBundle:Post')->findBy(array('category_id' => 1));

this should work, just replace the YourBundle:Post with the proper bundle and class names. Same for the category_id, I can't guess without your implementation if it's the class property or the mapping name.

I suggest you to read more on the official Doctrine documentation to improve your knowledge on the subject.

answered Nov 04 '13 at 11:13

Yoann Chambonnet

1,313
16
22

This might be indeed better ! Now I have an error "Unrecognized field: category_id ". This field is the foreign key in reference to my Category class (many-to-one). – Nov 04 '13 at 12:12
Then, can you add in your original post the copy/paste of your "Post" class please Static Wizard? – Yoann Chambonnet Nov 04 '13 at 12:39
Oh, ok, now I get it... category_id is the database field, but what I have to use is "category" mapped in my class... At least I've learnt from my mistake ! Thank you for your advice ! – Nov 04 '13 at 12:48
That's exactly it! My bad for not seeing it previously! Glad it helped :) – Yoann Chambonnet Nov 04 '13 at 12:56
this saved my day – saggzz Aug 02 '19 at 08:03

score 1 · Answer 2 · answered Nov 04 '13 at 11:15

When you execute a raw query using Doctrine's DBAL layer the results come back as an array of field names mapped to values rather than as entities.

Therefore you need something like this:

foreach($posts as $post) {
    $xml .= $post['id'];
    $xml .= $post['title'];
    $xml .= $post['content';
}

Symfony Doctrine : get field value in foreach loop on fetchAll()

2 Answers2