fetch records from two tables relating to each other - symfony

Question

I have two table in symfony. The images table has a foreign key to the users table. I want to retrieve records where user id is equal to the argument passed. Here is my attempt

private function serializeUsers(Users $usr) {
        return array(
            'username' => $usr->getUsername(),
            'email' => $usr->getEmail(),
            'id' => $usr->getId(),
//I have tried using getter method to retrieve image value here but I cannot because image is related to users
            );
        }

Here is the controller codes

$restresults = $this->getDoctrine()
                ->getRepository('xxxBundle:Users')
                    ->findBy(['id' => $id]);

            array_push($data, $this->serializeUsers($restresult));

As you can see from my above attempts, I am unable to retrieve data for the image table having a foreign key to the users table. Is my design wrong or I have not yet figured out the logic or a better way to approach this

Look at the http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/association-mapping.html#one-to-many-bidirectional One-To-Many relationship. When you define it you can use `$user->getImages()` to get the collection of the images — Ziumin, Mar 20 '18 at 04:41
I have already defined the one-to-many relationship in my model and in this case images references users — net, Mar 20 '18 at 08:13

Gregoire Ducharme · Accepted Answer · 2018-03-20T11:04:41.260

EDIT:

You could use the entity Repository:

Add

* @ORM\Entity(repositoryClass=UsersRepository::class)
* @ORM\Table(name="Users")"

On your Users entity annotation And you'll be able to use it, it's way cleaner than writing the needed query in your controller

$restresults = $this->getDoctrine()
            ->getRepository('xxxBundle:Users')
                ->findBy(['id' => $id]);

Write instead:

$restresults = $this->getDoctrine()
            ->getRepository('xxxBundle:Users')
                ->findWithImages($id);

And in your UsersRepository wirte a function getting the images:

public function findWithImages($id)
{
    $queryBuilder = $this->createQueryBuilder('u')
        ->join('u.images', 'i')
        ->where('u.id = :id')
        ->setParameter('id', $id);
    return $queryBuilder->getQuery()->getResult();
}

END OF EDIT

Have you reversed your relation ?

$user->images should be an private ArrayCollection initialized in your class constructor and accessible via a $user->getImages();

This variable does not correspond to a database column but allow you to access the image even from the user side (not being the owner side in the case).

Also you might want to have a look at What is the difference between fetch="EAGER" and fetch="LAZY" in doctrine

For some more details on how your data are accessed.

Also check this: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/association-mapping.html#one-to-many-bidirectional

Right here you are normalizing your data more than serializing it. Have a look at: https://symfony.com/doc/current/components/serializer.html

Undefined method 'findWithImages'. The method name must start with either findBy or findOneBy! (500 Internal Server Error) — net, Mar 20 '18 at 11:19
Have you added the correct annotation on your Users entity ? `@ORM\Entity(repositoryClass=UsersRepository::class)` ? — Gregoire Ducharme, Mar 20 '18 at 11:21
I got no clue then. It must be one small mistake somewhere. Have you updatedt your database schema and everything ? — Gregoire Ducharme, Mar 20 '18 at 15:12

fetch records from two tables relating to each other - symfony

1 Answers1