How to join table doctrine symfony2

Question

I have two table in my database and i want to join this 2 table to show data in my view but i didn't find a solution.

This is my first entity given below

/** * @ORM\Entity */ class classified { /** * @ORM\Id * @ORM\Column(type="integer") * @ORM\GeneratedValue(strategy="IDENTITY") */ protected $classified_id; /** * @ORM\Column(type="integer") */ protected $user_id=0; /** * @ORM\Column(type="string") */ protected $firstname="null"; /** * @ORM\Column(type="integer") */ protected $region_id="null";

The second entity :

class regions { /** * @ORM\Id * @ORM\Column(type="integer") */ protected $region_id; /** * @ORM\Column(type="string") */ protected $regionname; /** * @ORM\Column(type="integer") */ protected $country_id=107; }

In my controller i would like to join the table to get information.

$em = $this->getDoctrine() ->getEntityManager(); $classified = $em->createQueryBuilder() ->select('b') ->from('BlogBundle:classified', 'b') ->addOrderBy('b.classifiedaddeddate', 'DESC') ->getQuery() ->getResult(); return $this->render('BlogBundle:Page:index.html.twig', array( 'classified' => $classified ));

Any solution please?

Your schema is incorrect. You should never ever write somethig_id in an entity. You bind objects togather by reference and not database ids. Please read through the documentation carefully. — 1ed
– 1ed, Commented Jan 6, 2014 at 16:28

Ahmed Siouani · Accepted Answer · 2014-01-06 17:21:30Z

Write beautiful & clean code,

Before answering, I would suggest you fix the following issues in your code,

Class name should be CamelCase & singular (Classified instead of classified and Region instead of regions)
Try to find a cleaner way to set the country_id (as protected $country_id=107; doesn't make sens. Same for user_id)

So, in order to get your classified entity with its related region, you've to,

First, change (in your Classified class)

/** * @ORM\Column(type="integer") */ protected $region_id="null";

to

/** * @ORM\ManyToOne(targetEntity="Region", inversedBy="classifieds") * @ORM\JoinColumn(name="region_id", referencedColumnName="region_id") */ protected $region;

and add to your Region entity,

/** * @ORM\OneToMany(targetEntity="Classified", mappedBy="region") */ protected $classifieds;

Take a deeper look at the Entity Relationships/Associations part of the Databases and Doctrine chapter of the documentation to understand how to define entities associations using Doctrine.

You maybe meant OneToMany(targetEntity="Classified", mappedBy="region")
sorry i didn't explain well my problem i have the table region full of information so i have to extract this information when i combine the two table (for exemple i like to get the variable $regionname when classified.region_id=region.region_id )
Yes, if your entities associations are well defined (as I suggested) getting regionname is as easy as doing, $q = Doctrine_Query::create() ->select('c.*, r.regionname') ->from('classified c') ->leftJoin('c.region r') -> ... Take your time to go deeper into the documentation in order to understand all the possible uses of doctrine associations.

Collectives™ on Stack Overflow

How to join table doctrine symfony2

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related