Zikula Community

• phaidon
  
  

  • Rank: Freshman
  • Registered: Jul 28, 2010
  • Last visit:
  • Posts: 98

  Posted: Sep 21, 2011 - 10:04 PM

  Does somebody know how joins are working in Doctrine2.
  
  Here are the tables:
  https://github.com/phaidon/Wikula/blob/master/src/modules/Wikula/lib/Wikula/Entity/Pages.php
  https://github.com/phaidon/Wikula/blob/master/src/modules/Wikula/lib/Wikula/Entity/Links.php
  
  Here is the doctrine 1 query:
  

  Code

  $q = Doctrine_Query::create()
   ->from('Wikula_Model_Pages a')
   ->select('a.tag, b.to_tag')
   ->leftJoin('a.Wikula_Model_Links b')
   ->where('a.latest = ?', array('Y'));

  
  
  The join is on Wikula_Entity_Pages.tag = Wikula_Entity_Links.from_tag
  
  Thanks a lot for your answers
  
  
  
  Edited by phaidon on Sep 21, 2011 - 11:40 PM.
  
  --
  https://github.com/phaidon/

  • 
  •  
• craigh
  
  

  • Rank: Developer
  • Registered: Jun 16, 2003
  • Last visit: May 29, 2010
  • Posts: 1966

  Posted: Sep 22, 2011 - 03:31 AM

  Drak or others may have a different perspective but as I see it, D2 is a completely different paradigm (thought process) than D1. With D1, you think about tables and joins and stuff - in other words, you *have* to consider the database-layer.
  
  in D2, you are encouraged to STOP thinking about the database layer and only think about objects. This is especially difficult if you are refactoring an existing module which is likely not object-based. In D2, you make your objects have relationships (one to many, many to one, etc) with other objects and then you ignore the database layer. This is a very different way to approach a problem than in the past where you were forced to consider joins and so on.
  
  So the real answer to your question is that you should rebuild your objects so you don't have to think about joins. Your object should have a property which is a collection of sub-objects with a specific relationship (likely 'one to many' if you are considering a left join). I suggest you study the D2 docs thoroughly to get a better perspective on the solution. My Tag module does make use of this paradigm, but it is a bit sketchy there because it is a hook module, so it 'pretends' to model an object for another module, which makes it a bit confusing.

  • 
  •  
• phaidon
  
  

  • Rank: Freshman
  • Registered: Jul 28, 2010
  • Last visit:
  • Posts: 98

  Posted: Sep 22, 2011 - 04:21 AM

  I already checked your tag module and the zikula core doctrine example module but i did not find a way. The online D2 doc is in this point not very helpful But I will try it again.
  
  
  
  Edited by phaidon on Sep 22, 2011 - 04:22 AM.
  
  --
  https://github.com/phaidon/

  • 
  •  
• craigh
  
  

  • Rank: Developer
  • Registered: Jun 16, 2003
  • Last visit: May 29, 2010
  • Posts: 1966

  Posted: Sep 22, 2011 - 04:35 AM

  OK - let me be more specific, but I really can't give you too much info.
  
  your Pages entity needs a Links property that has a relationship to the Links entity of one to many (probably).
  
  in the Tag module, the 'Object' entity (a bad name, I know), has a property called 'tags'
  
  https://github.com/craigh/Tag/blob/master/src/modules/Tag/lib/Tag/Entity/Object.php
  
  this is a collection of tag objects that are associated with each 'Object' object. (lol), notice in this case that it has a ManyToMany relationship with the Tag entity.
  
  you would need to do something similar, although a OneToMany relationship is simpler.

  • 
  •  
• craigh
  
  

  • Rank: Developer
  • Registered: Jun 16, 2003
  • Last visit: May 29, 2010
  • Posts: 1966

  Posted: Sep 22, 2011 - 04:37 AM

  http://www.doctrine-project.org/docs/orm/2.1/en/reference/association-mapping.html
  
  is the page of docs that explains this.

  • 
  •  
• phaidon
  
  

  • Rank: Freshman
  • Registered: Jul 28, 2010
  • Last visit:
  • Posts: 98

  Posted: Sep 22, 2011 - 08:11 AM

  I added the following lines to Wikula_Entity_Pages:
  
  

  Code

  /**
  * links
  *
  * @ORM\ManyToMany(targetEntity="Wikula_Entity_Links")
  */
  private $links = null;

  
  
  Then I tried:
  
  

  Code

  $em = $this->getService('doctrine.entitymanager');
  $dql = "SELECT o FROM Wikula_Entity_Pages o JOIN o.links t";
  $query = $em->createQuery($dql);
  $orphanedPages = $query->getArrayResult();

  
  
  I got the following the error
  
  NOTICE (5): E_NOTICE: Undefined index: id in /var/www/z/plugins/Doctrine/lib/vendor/Doctrine/ORM/Query/SqlWalker.php line 836
  NOTICE (5): E_NOTICE: Undefined index: in /var/www/z/plugins/Doctrine/lib/vendor/Doctrine/ORM/Mapping/ClassMetadataInfo.php line 1904
  Error on dev@wuertz
  The 'wikula' module returned an error in 'main'.
  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= w2_.wikula_entity_links_id' at line 1
  
  I read that the problem is related with the fact that the primary column has another name then id.
  
  I tired to solve the problem with:
  
  

  Code

  * @ORM\JoinColumn(name="from_tag", referencedColumnName="id")

  
  
  But there was no effect.
  
  --
  https://github.com/phaidon/

  • 
  •  
• phaidon
  
  

  • Rank: Freshman
  • Registered: Jul 28, 2010
  • Last visit:
  • Posts: 98

  Posted: Sep 22, 2011 - 05:07 PM

  After renaming the column to 'id' *(what is just a workaround) I get the following error:
  
  SQLSTATE[42S02]: Base table or view not found: 1146 Table 'z13.wikula_entity_pages_wikula_entity_links' doesn't exist
  
  By the way thanks a lot for your help craigh.
  
  --
  https://github.com/phaidon/

  • 
  •  
• craigh
  
  

  • Rank: Developer
  • Registered: Jun 16, 2003
  • Last visit: May 29, 2010
  • Posts: 1966

  Posted: Sep 22, 2011 - 06:21 PM

  you must set up $links as a collection in the constructor. see my example.

  • 
  •  
• phaidon
  
  

  • Rank: Freshman
  • Registered: Jul 28, 2010
  • Last visit:
  • Posts: 98

  Posted: Sep 22, 2011 - 08:38 PM

  I already did that:
  
  public function __construct()
  {
  $this->links = new ArrayCollection();
  }
  
  I commit the current state:
  
  https://github.com/phaidon/Wikula/i
  
  --
  https://github.com/phaidon/

  • 
  •  
• craigh
  
  

  • Rank: Developer
  • Registered: Jun 16, 2003
  • Last visit: May 29, 2010
  • Posts: 1966

  Posted: Sep 23, 2011 - 03:43 AM

  since you are doing ManyToMany, you'll have to force Doctrine to generate it's relation table - which it probably hasn't done, hence the error.
  
  Are you sure you need ManyToMany - it's actually kind of rare. Consider trying OneToMany to start with and see if it works better. I don't think Doctrine will need to generate a table in this case.

  • 
  •  
• Guite
  
  

  • Rank: Team Member
  • Registered: Jan 19, 2003
  • Last visit: Apr 06, 2010
  • Posts: 442

  Posted: Sep 23, 2011 - 03:34 PM

  D2 uses a relation table for unidirectional OneToMany (as this is implemented as a ManyToMany relation having a unique key on one side). A bidirectional OneToMany relation doesn't need a relation table, but uses a simple foreign key on the target side.
  
  --
  Guite | ModuleStudio

  • 
  •  
• phaidon
  
  

  • Rank: Freshman
  • Registered: Jul 28, 2010
  • Last visit:
  • Posts: 98

  Posted: Sep 29, 2011 - 05:42 PM

  Thanks a lot for your help. But for the moment I give up. Even all trays on serval days and a lot of manual reading I am not able to realize an easy join:
  
  wikula_pages:
  id: integer, primary
  tag: string
  
  wikula_links:
  from_tag: string, primary
  to_tag: string, primary
  
  SELECT *
  FROM `wikula_pages`
  LEFT JOIN `wikula_links`
  ON wikula_links.from_tag=wikula_pages.tag
  
  
  
  Edited by phaidon on Sep 29, 2011 - 07:06 PM.
  
  --
  https://github.com/phaidon/

  • 
  •  
• phaidon
  
  

  • Rank: Freshman
  • Registered: Jul 28, 2010
  • Last visit:
  • Posts: 98

  Posted: Oct 01, 2011 - 06:18 PM

  After the help of a friend I found the solution.
  
  We should improve the doctrine example module, otherwise the begining is very frustating.
  
  --
  https://github.com/phaidon/

  • 
  •  
• craigh
  
  

  • Rank: Developer
  • Registered: Jun 16, 2003
  • Last visit: May 29, 2010
  • Posts: 1966

  Posted: Oct 02, 2011 - 12:49 AM

  feel free to submit suggestions to the example as a pull request.

  • 
  •  
•  
•  

Users on-line

• 0 users

This list is based on users active over the last 60 minutes.