Here are those two tables: mysql> explain dgmTypeAttributes; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | typeID | int(11) | NO | PRI | NULL | | | attributeID | smallint(6) | NO | PRI | NULL | | | valueInt | int(11) | YES | | NULL | | | valueFloat | double | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
mysql> explain dgmAttributeTypes; +---------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------------+------+-----+---------+-------+ | attributeID | smallint(6) | NO | PRI | NULL | | | attributeName | varchar(100) | YES | | NULL | | | description | varchar(1000) | YES | | NULL | | | iconID | smallint(6) | YES | MUL | NULL | | | defaultValue | double | YES | | NULL | | | published | tinyint(1) | YES | | NULL | | | displayName | varchar(100) | YES | | NULL | | | unitID | tinyint(3) unsigned | YES | MUL | NULL | | | stackable | tinyint(1) | YES | | NULL | | | highIsGood | tinyint(1) | YES | | NULL | | | categoryID | tinyint(3) unsigned | YES | MUL | NULL | | +---------------+---------------------+------+-----+---------+-------+ 11 rows in set (0.00 sec) So it is one-to-many relation. Generated query seems to be correct, because it returns correct data when i run it directly at mysql command line. I know i could use php associations, and access attributetype thru typeattribute->typeID. But later I will need many more associated objects (tables), so I am trying to load them this way. It is same as with article comments. Where you have Article, Comment and User objects. Now i have article id and trying to find all comments with their users (authors) for it, without loading article object (just using its id). And it returns just first object. Marco said it is correct behavior. So i am looking for another way to do it. I hope I explained it correctly and clearly now. Thanks, Miroslav Misek Dne pátek, 27. prosince 2013 11:48:10 UTC+1 Herman Peeren napsal(a): > > *First: * > your model is not clear to me. You have types and attributes and you want > a many-to-many relationship between those two? So: a type can have multiple > attributes and an attribute can have multiple types? Then you should make a > Type entity and an Attribute entity with a many-to-many relation between > them. > > I didn't see your mapping-definition, but at the moment it looks like you > are trying to make a one-to-many relationship between your attributes and > types. Because you join over the attributeID column it would be one > attribute per type (and consequently possibly many types per attribute). > But I have the feeling that is not exactly what you want to model. However, > I will use this one-to-many relationship in an example below to explain > something else. > > *Second* (using a one-to-many relationship between types and attributes): > It is more than just a naming-matter: your query goes wrong because of > mixing the object paradigm and the relational paradigm. > > *relational paradigm* > You know this: you have tables that are linked by foreign keys. In your > case you have a table with attributes(dgmTypeAttributes) and a table with > types (dgmAttributeTypes) and they are linked by the attributeID key. SQL > is the language to query those tables. > > *object paradigm* > Forget about foreign keys for a moment; this is not relevant when working > with the attributes and types as objects. You habe a Type entity and a > Attribute entity. An type can have 1 attribute, so an type has a > attribute-field. That attribute field doesn't contain an ID (that is > thinking in the relational paradigm), but it contains an Attribute entity. > The other way around: an attribute can contain a collection of types. So > there is an types-field in Attribute. It is not containing type-IDs, but is > a collection of Type entities. DQL is the language to query those objects. > > *ORM* > Object-Relational Mapping is a mapping layer between the database tables > (the relational paradigm) and the entities (object paradigm). In the > mapping layer you not only define which tables/columns are related to which > entities/fields, but also how those objects that are contained in another > object (for instance: a attribute-object that is a field in an type-object) > are mapped to their respective tables and columns by means of foreign keys > (those attributeID columns). Because you define the columns used in a join > (those foreign keys) in this mapping, you don't specify those in the DQL > JOIN anymore. > > Hope this helps to understand what is wrong and how it should be done. In > your case I'd start with the first part: get clear what relationship is > between types and attributes. Get both the tables, the entities and the > mapping right and then the DQL query is easy. > > Best wishes everybody for 2014! May all your objects be nicely mapped! > Herman > > > On Thursday, 26 December 2013 23:52:22 UTC+1, [email protected] wrote: >> >> Hi, >> >> I have DQL query: >> >> $query = $qb->select('ta', 'at') >> ->from('EveStatic\Entity\DgmTypeAttribute', 'ta') >> ->innerJoin('ta.attributeID', 'at') >> ->where('ta.typeID = :typeID') >> ->setParameter('typeID', $this->typeID) >> ->getQuery(); >> >> getSQL() returns: >> >> SELECT d0_.typeID AS typeID0, d0_.valueInt AS valueInt1, d0_.valueFloat AS >> valueFloat2, d1_.attributeID AS attributeID3, d1_.attributeName AS >> attributeName4, d1_.description AS description5, d1_.iconID AS iconID6, >> d1_.defaultValue AS defaultValue7, d1_.published AS published8, >> d1_.displayName AS displayName9, d1_.unitID AS unitID10, d1_.stackable AS >> stackable11, d1_.highIsGood AS highIsGood12, d0_.attributeID AS >> attributeID13, d1_.categoryID AS categoryID14 FROM dgmTypeAttributes d0_ >> INNER JOIN dgmAttributeTypes d1_ ON d0_.attributeID = d1_.attributeID WHERE >> d0_.typeID = ? >> >> >> Directly at mysql it returns 8 rows, but getResult() or getArrayResult() >> returns only first row with relations loaded. It should return 8 unique rows >> with relations loaded I think. >> >> >> Can anyone help me please and tell me, what I am doing wrong? >> >> >> Thanks, >> >> Miroslav Misek >> >> -- You received this message because you are subscribed to the Google Groups "doctrine-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/doctrine-user. For more options, visit https://groups.google.com/groups/opt_out.
