If you have a many-to-one relationship between dgmTypeAttributes and
dgmAttributeTypes via the attributeID column and
if you make two entities from those two tables called DgmTypeAttribute and
DgmAttributeType (I think the names could be more clear),
then you would have an attributeType-field in your DgmTypeAttribute-entity,
which holds a DgmAttributeType-entity.
With annotations:
/** @Entity **/
class DgmTypeAttribute
{
// ...
/**
* @ManyToOne(targetEntity="DgmAttributeType",
inversedBy="typeAttributes")
* @JoinColumn(name="attributeID", referencedColumnName="attributeID")
**/
private $attributeType;
// ...
}
The DgmAttributeType-entity would (if it is a bi-directional mapped
relationship) then contain a typeAttributes-field (note the plural!), which
contains a collection of DgmTypeAttribute-entities.
With annotations:
/** @Entity **/
class DgmAttributeType
{
// ...
/**
* @OneToMany(targetEntity="DgmTypeAttribute", mappedBy="attributeType")
**/
private $typeAttributes;
// ...
public function __construct()
{
$this->typeAttributes = new
\Doctrine\Common\Collections\ArrayCollection();
}
}
Note: no attributeID-field in the entities.
Now in your DQL you can do:
$query = $qb->select('ta', 'at')
->from('EveStatic\Entity\DgmTypeAttribute', 'ta')
->Join('*ta.attributeType*', 'at')
->where('ta.typeID = :typeID')
->setParameter('typeID', $this->typeID)
->getQuery();
This would be the same with ArticleID instead of TypeID, Comment instead of
DgmTypeAttribute and User instead of DgmAttributeType.
Hope this helps.Have to leave now; back tomorrow.
Herman
On Friday, 27 December 2013 12:10:15 UTC+1, [email protected] wrote:
>
> 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.
