I'm trying to create a native query (the reason for this is that i need
ORDER BY RAND()) in which i join a table (words) on itself
This is my query:
SELECT
w.word_id, w.word,
wt.wordtype_id AS wordtypeid, wt.wordtype,
t.word_id AS translation_id, t.word AS translation
FROM words AS w
LEFT JOIN wordtypes wt ON (wt.wordtype_id=w.wordtype_id)
LEFT JOIN word_translations wtr ON (wtr.originalword_id=w.word_id)
LEFT JOIN words t ON (t.word_id=wtr.translationword_id)
ORDER BY RAND() LIMIT 1
What it does: it select a random word and also what type of word it is
(verb, noun, etc,) and it's translation. Since the translation is a word
itself i'm using the same table again aliased as 't'
When i try to map the results though, i'm bumping into problems:
$rsm = new ResultSetMappingBuilder($this->_em);
$rsm->addRootEntityFromClassMetadata('Application\Entity\Words', 'w');
$rsm->addJoinedEntityFromClassMetadata('Application\Entity\Wordtypes',
'wt', 'w', 'wordtype', array("wordtype_id"=>"wordtypeid"));
$rsm->addJoinedEntityFromClassMetadata('Application\Entity\WordTranslations'
, 'wtr', 'w', 'translationOriginals');
$rsm->addJoinedEntityFromClassMetadata('Application\Entity\Words', 't',
'wtr', 'translationword', array("word_id"=>"translation_id",
"word"=>"translation"));
According to the docs (
http://docs.doctrine-project.org/en/2.1/reference/native-sql.html#resultsetmappingbuilder)
the last parameter of addJoinedEntityFromClassMetadata() can be used to
avoid name name-clashes, This seems to be working for the wordtype table,
but not for the aliased Words entity -> even though i tell doctrine that
word_id should be translation_id, it still gives me the error *"The column
'word_id' conflicts with another column in the mapper."*
Am i doing something wrong, or is this simply not possible (or even a bug)?
If it's not possible/a bug : what would be a work-around?
I've tried manually mapping the fields like this:
$rsm = new ResultSetMapping;
$rsm->addEntityResult('Application\Entity\Words', 'w');
$rsm->addFieldResult('w', 'word_id', 'wordId');
$rsm->addFieldResult('w', 'word', 'word');
$rsm->addJoinedEntityResult('Application\Entity\Wordtypes' , 'wt', 'w',
'wordtype');
$rsm->addJoinedEntityResult('Application\Entity\WordTranslations' , 'wtr',
'w', 'translationOriginals');
$rsm->addJoinedEntityResult('Application\Entity\Words' , 't', 'wtr',
'translationword');
This works, but only gives me a Word entity and i also want the
translation. But as soon as i add this:
$rsm->addFieldResult('t', 'translation_id', 'word_id');
I get the error: Notice: Undefined index: word_id in
C:\wamp\www\fathertongue\vendor\doctrine\orm\lib\Doctrine\ORM\Internal\Hydration\AbstractHydrator.php
on line *250*
I even tried reversing the columns:
$rsm->addFieldResult('t', 'word_id', 'translation_id');
But that didnt work either: Notice: Undefined index: translation_id in
C:\wamp\www\fathertongue\vendor\doctrine\orm\lib\Doctrine\ORM\Internal\Hydration\AbstractHydrator.php
on line *250*
Btw: before i get into a discussion about the usage of ORDER BY RAND() :
ultimately the query will be much more complex with lots of filters (where
clauses) that should be taken into account while selecting a random row.
This makes it impossible to use a trick like selecting a random primary key
first to speed up performance. Also, i will be selecting much more data
then the few columns i have now.
--
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.
