doctrine2,doctrine,doctrine-query , how to implement join to more than one table on same column in doctrine

how to implement join to more than one table on same column in doctrine


Tag: doctrine2,doctrine,doctrine-query

My scenario is like this.

item_master table

item_id (primary key) | item_name
      1               |     Keyboard
      2               |     Mouse

user_lp_requirement table

id1 |item_id | otherColumns1
1   |  1     | 1
1   |  2     | 3
item_id is foreign key reference to item_master

training_program table

id2 |item_id | otherColumns2
1   |  1     | 1
1   |  2     | 4
item_id is foreign key reference to item_master

edit: In entity I have user_lp_requirement.item_id mapped to item_master.item_id. So if i mention item_id in query builder join, it automatically joins user_lp_requirement to item_master

Now the problem is how to join user_lp_requirement to training_program on "item_id" using doctrine queryBuilder.

FYI->the query will be written in Application\Entity\Repository\UserLpRequirementRepository.

    ->select('u', 'i.item_name', 't.otherColumns2')
    ->from('user_lp_requirement', 'u')
    ->innerJoin('u', 'item_master', 'i', 'u.item_id = i.item_id')
    ->innerJoin('u', 'training_program', 't', 'u.item_id = t.item_id');

This assumes that you want the user table mapped to the item table AND the user table mapped to the training table rather than the user table mapped to a join of the training table and the item table.


create properly a ManyToMany Recursive / Reflexive relation with doctrine ORM (Symfony)

I have this entity named Pointscomptage.php: class Pointscomptage { /** * @var integer * * @ORM\Column(name="id", type="integer", nullable=false) * @ORM\Id * @ORM\GeneratedValue(strategy="IDENTITY") */ private $id; /** * @var string * * @ORM\Column(name="invariant", type="string", length=150, nullable=false) */ private $invariant; /** * @var string * * @ORM\Column(name="nom", type="string", length=150, nullable=false) */ private...

Entity MetaData for Doctrine and multiple Entity Managers

I'm trying to setup an Sql Server connection for Doctrine in Symfony2.However I can not figure out how to setup some Entity Foo to be managed by the new Entity Manager. I have seen this post in regards to managing Entities with multiple Entity Managers, however I don't know how...

Error while generating doctrine entities with FOSUser Bundle in Symfony2

I'm using FOSUser bundle and everything went ok until I tried to create the database entities, I get this error: It has nothing to do with the bug in Symfony 2.5, I'm using version 2.7 Case mismatch between loaded and declared class names: App\UserBundle\Entity\user vs App\UserBundle\Entity\User This is my config...

Doctrine persist entity with inverse relation not work

I'm trying to save two entities linked. Product entity may have any or many entities ProviderRate. When I try to save the product entity, it tells me that ProviderRate related entity has not assigned one of their required fields. I need to save a product with no need to assign...

Symfony 2 - Class “Mingle\StandardBundle\Entity\Product” is not a valid entity or mapped superclass

I created a Product.php file in my bundle Mingle\StandardBundle\Entity like this: <?php namespace Mingle\StandardBundle\Entity; use Doctrine\ORM\Mapping as ORM; /* * @ORM\Entity * @ORM\Table(name="product") */ class Product { /* * @ORM\Column(type="integer") * @ORM\ID * @ORM\GeneratedValue(strategy="AUTO") */ protected $id; /* * @ORM\Column(type="string",length=100) */ protected $name; /* * @ORM\Column(type="decimal",scale=2) */ protected $price; /*...

With a OneToMany relation between entities, how create the right queryBuilder with Doctrine (on the Inversed Side entity)

I have these 3 entities in my symfony2.6 project: Compteurs.php class Compteurs { /** * @var \PointsComptage * * @ORM\ManyToOne(targetEntity="PointsComptage", inversedBy="compteurs") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="pointscomptage_id", referencedColumnName="id") * }) */ private $pointsComptage; /** * @var \Doctrine\Common\Collections\Collection * * @ORM\OneToMany(targetEntity="ParametresMesure", mappedBy="compteurs") */ private $parametresMesure; ParametresMesure.php: class ParametresMesure { /** * @var Compteurs...

Doctrine OneByOne's and Date

I am using Symfony 2.7.1 and I seem to have a problem while using my News Entity. I am trying to use the published_at in my twig template. I tried using {{ news_item.published_at|date("m/d/Y") }} but that seems to follow up by a fatal error: Method "published_at" for object "AppBundle\Entity\News" does...

Search from comma separated values in column

I have a table with 2 coloums - id and pets pets column contain abbreviated pet names separated by , [comma] as shown below +----+-------------+ | id | pets | +----+-------------+ | 1 | CAT,DOG | +----+-------------+ | 2 | CAT,DOG,TIG | +----+-------------+ | 3 | ZEB,MOU | +----+-------------+ Now...

Symfony one-to-one, unidirectional relation

I have two entities. User and Contact. User and contact have the same property phone_number. I would like to get all contacts with user object if its exists. It is also possible that the same phone number exists in contact table but it doesn't exist in user table. Below is...

Wrong datatype for referenced entity on Doctrine ObjectSelect

I have a form which uses Doctrine's ObjectSelect to make a dropdown $this->add(array( 'name' => 'category', 'type' => 'DoctrineModule\Form\Element\ObjectSelect', 'options' => array( 'label' => 'Category', 'object_manager' => $em, 'target_class' => 'Blog\Entity\Category', 'property' => 'name' ), 'attributes' => array( 'required' => true ) )); the issue I have, is that this...

How can i view Result of Database from doctrine in twig

Edit: change findOneBy to findBy In symfony I am using the FOS-UserBundle. I have three tables. fos_user customer customer_user This is the customerUser.orm.xml <?xml version="1.0" encoding="utf-8"?> <doctrine-mapping xmlns="" xmlns:xsi="" xsi:schemaLocation=""> <entity...

Is it a bad practice to move all Doctrine2 Entities into one single Bundle?

Here is my issue; Lets say you create a relationship from one bundle to another (In this case, one user => many notifications) (UserBundle => NotificationsBundle) /** * @ORM\OneToMany(targetEntity="Gabriel\NotificationsBundle\Entity\Notification", mappedBy="notificationOwner") */ private $notifications; The Problem: When the Notificationsbundle gets removed from the AppKernel, the class above gets lost, and the...

Symfony ManyToMany delete record

I was trying to update a many to many relation between "book * <- -> * tag". For that, I first wanted to delete all current related tags of a book - to add afterwards again the changed /entered tags. Deleting all tags of a book is working, but when...

Add brands through company, it's possible? How?

I have this two tables (see pics below) mapped as follow: class Brand { ... /** * @var Company * * @ORM\ManyToOne(targetEntity="Company") * @ORM\JoinColumn(name="companies_id", referencedColumnName="id") */ protected $company; } class Company { ... } I need to add support for add a new Brand from Company but I have not...

Join between ManyToMany properties at Doctrine2 entities

I have this tables relationship: This is how my entities for messages and emails looks like (I've just added the important info): class Message { /** * @var Brand * * @ORM\ManyToOne(targetEntity="Brand") * @ORM\JoinColumn(name="brands_id", referencedColumnName="id") */ protected $brand; ... } class Email { /** * @ORM\ManyToMany(targetEntity="Message", inversedBy="messageXrefMail", cascade={"persist", "remove"}) *...

Doctrine batch inserting uses 2GB of Ram

I am trying to insert approximately 200k of records with Symfony2 and Doctrine via Doctrine Fixtures Bundle. I am using flush and clear but at the end script uses 1.8 GB of RAM. This is the class that loads SmartMeter entities into database: <?php namespace HTEC\SmartMeteringAPIBundle\DataFixtures\ORM; use Doctrine\Common\DataFixtures\AbstractFixture; use Doctrine\Common\DataFixtures\FixtureInterface;...

Doctrine fixtures won't get loaded in given order, instead, they get loaded in alphabetical order

As far as I know, fixtures will be loaded in given order, based on the value returned in getOrder() method of each fixture files. Sharing Objects between Fixtures. For some reason, doctrine tries to load fixtures below in alphabetical order (class name) instead. In example below, it has to load...

Jms serializer @JMS\Inline() annotation overrides an actual id

I have product and product_detail tables in database. Every product has a product detail. class Product { /** * @var ProductDetail * @JMS\ReadOnly() * @ORM\OneToOne(targetEntity="ApiBundle\Entity\ProductDetail", mappedBy="product", cascade={"persist"}) * @JMS\Inline() */ private $detail; } I use @JMS\Inline() annotation to show only detail information of product_detail table. But when I serialize a...

Symfony2 creating and persisting entity relationships

I have two entities Skin and Email. I want Email to be a part of the Skin entity, however I can't use the console to update schema automatically right now, and that is probably why I can't get the relationship to work. So I want to store all the Emails...

doctrine/migrations incompatible with symfony 2.2.*

long story short: Im using Symfony 2.2.* (yes, its old) and a DEV-Enviroment using a deploy script. This script runs "composer update". Every thing works fine until today. Now I recive this error: - Conclusion: remove symfony/symfony v2.2.11 - Conclusion: don't install symfony/symfony v2.2.11 - Conclusion: don't install symfony/symfony v2.2.10...

How to select all rows that don't have one-to-many entity with certain value using Doctrine

I have two entities: Cage and Bird. Cage has Birds inside so their relationship is one-to-many. Bird has a field name. How can I select all Cages where there's no Bird with name eagle inside. I was trying to do this: $cages = $this->createQueryBuilder("c") ->leftJoin("c.birds", "b") ->where(" != :name") ->setParameter("name",...

Store entity from session doesn't work => $em->persist()

at first: Sorry for my poor english :-) A beginner need help! I have 3 entities/tables: Tables: contact_person id, title, firstname, surname, adress_id address id, street, zip, city, country_id country (fix values) id, name, code 1, Austria, AT 2, Germany, DE ... Entities: /** * ContactPerson * * @ORM\Table(name="contact_person") *...

Unique Entity Error message

I have a user entity which have some unique fields. The code bellow shows you how I defined it. /** * @UniqueEntity(fields={"login"}, message="UNIQUE ERROR MESSAGE") */ ...... /** * @var string * * @ORM\Column(name="login", type="string", length=255, unique=true) */ private $login; Developping an API, I would like to be able to...

Getting Objects from database in Symfony2

I had read somewhere some day that symfony2/Doctrine2 has a method(i don't remember the method name now) that fetches all "like" objects that we specify.. For example, I have User entity that has userName , password, name, state and city as properties.. For getting all Users who has name =...

RowAction in a Vector source APYDataGridBundle Symfony 2

Im stuck with this issue for a few days, i currently have grid filled with results from a Stored Procedure, i run a nativequery with the SP, get the results in an Array and use the Vector class from ApyDataGrid to display the results. All of the above is working...

Persisting other entities inside preUpdate of Doctrine Entity Listener

For clarity I continue here the discussion started here. Inside a Doctrine Entity Listener, in the preUpdate method (where I have access to both the old and new value of any field of the entity) I'm trying to persist an entity unrelated to the focal one. Basically I have entity...

AppBundle's Best Practice and how-to go further

According to this page on the documentation website of symfony they explain how to address your application logic and the best practices. The one thing I am missing in this documentation is the way to store and use your entities. There is a small section that covers the usage of...

Remove automaticaly entity in BD when choice value not selected (or null selected)

I would like to know if symfony/doctrine can manage automaticaly the fact that instead of setting the value of my entity to null it could symply remove it. (by removing it I mean the records where the value equal null) exemple: I have a PICTURE entity linked to a VOTE...

Multiple level of different kind of inheritance

For my project, I'm trying to use the inheritance feature of Doctrine. I need to represent medias (through different tables : one table for uploaded documents, one for linked videos, ... and so on). But, the videos can vary from provider to provider (such as Youtube, Dailymotion, you name it)....

Symfony Array Collection as Entity Property

Is there a way to associate an ArrayCollection to a database column created by an Entity? For example, I have two entities: Household and Pet Type. Household current has a property for Pet Type, but it expects the Pet Type object, so only one can chosen at the moment. I'd...

Why tree is invalid?

I have a entity that use doctrine extension, tree behaviuor, i found problems in tree and don't know it's reason. my entity: MyEntity: type: entity gedmo: tree: type: nested id: id: type: integer generator: strategy: AUTO fields: # ... lft: type: integer gedmo: - treeLeft rgt: type: integer gedmo: -...

Sorting a collection in doctrine2

I've written the following query (that may or may not be efficient, I'm still a newbie): $collection = $this->dm->getConnection()->selectCollection('db_name', 'collection_name'); $query = array('array_name' => new \MongoId(id)); $cursor = $collection->find($query)->limit(9)->sort('r', 'desc'); I'm trying to sort by an r value that looks like this in the document: "r": 0.58325652219355106354 but it isn't...

Symfony2 Functional Testing: Is a database required or not?

I have to write functional tests for a controller that registers a user in the application. The controller has two methods (and routes): registrationAction (route=registration) endAction (route=registration/end) Now I would like to write functional tests for those two methods. My question is: as the controller creates and persists a new...

Class inheritance in Symfony2

I'm a bit confused about an error that I'm getting: Undefined method 'getAsArray'. The method name must start with either findBy or findOneBy! getAsArray() is a method in my repository class, it's called in PostsController.php like so: $categoriesList = $this->getDoctrine()->getRepository('AirBlogBundle:Category')->getAsArray(); CategoryRepository.php is defined like this: namespace Air\BlogBundle\Repository; class CategoryRepository extends...

Symfony2 Doctrine: ContextErrorException: Catchable Fatal Error: Object of class DateTime could not be converted to string

In my Symfony2 project I have the following query in my ProductRepository.php: $dateNow = new \DateTime(); $query = $this->createQueryBuilder('p') ->update('MyBundle\Products', 'p') ->set('p.published', $dateNow) ->getQuery(); $query->execute(); ProductEntity.php: /** * Set published * * @param \DateTime $published * @return Product */ public function setPublished($published) { $this->published = $published; return $this; } ERROR:...

“Unable to open database file” error when trying to load Doctrine Data Fixtures in Symfony

I'm using pdo_mysql for prod, stag and dev environments and pdo_sqlite for test environment. The problem is that loading Doctrine Data Fixtures generates unable to open database file error as shown below. I assigned the permissions to cache folder as defined in symfony website and also tried with 777 but...

Symfony time field not using the same date as the corresponding entity property

I've got an entity with a duration property, declared as time type: /* @ORM\Column(type="time") */ private $duration; In my Symfony form, this duration property maps to a time field: $builder->add( 'duration', 'time', [ 'input' => 'datetime', 'widget' => 'text' ] ) As the time type does not exist in PHP,...

Symfony Doctrine findBy and then map

Basically I want to execute this mysql query with doctrine: select distinct user_id from work_hour where project_id = ?; But I don't know how I can do this with pretty Doctrine code. Is it possible to make it look like the following pseudo code or do I have to use...

Converting query string to an associative array in symfony2

How to convert query string to an associative array in symfony2? Im new to symfony2. I googled it for couple of hours but there is no straightforward answer (example). Can anyone tell me how to do this?...

Symfony Entity query builder that checks for free spots

First off all: I know the title isn't helping, but it's the best I can think of. So I've got two entities in Symfony one is called Team and the other one Slot the Slot entity has a name, a start and end date and all that stuff. Besides that...

How to query in Doctrine2 WHERE = 'value from a related Entity'

The relationship is as easy as Many posts -> one User // Acme\AppBundle\Entity\UploadPlugin\Post /** * @ORM\ManyToOne(targetEntity="Acme\AppBundle\Entity\AuthBundle\LiveUser") * @ORM\JoinColumn(name="posts", referencedColumnName="id") **/ private $postOwner; So when I create a new Post like this : $image = new Post(); $image->setName($imagetitle); $image->setPostowner($this->getUser()); //(...) set further stuff $em = $this->getDoctrine()->getManager(); $em->persist($image); $em->flush(); I'd just return...

doctrine does not hydration when select custom fields

i have tried select fields with doctrine query buidler. $queryBuilder = $entityManager->createQueryBuilder(); $queryBuilder->select(', au.firstName') ->from('Api\V1\Entity\AgencyUser', 'au') ->orderBy('au.firstName', 'asc'); $queryBuilder->andWhere(' = :agencyId') ->setParameter('agencyId', $agency->getId()); print_r($queryBuilder->getQuery()->getResult(Query::HYDRATE_OBJECT));exit; result : Array ( [0] => Array ( [id] => 1 [firstName] => agency ) ) why this is an array ? i want to hydrated...