This is my first question ever so I hope I do everything how it should.
I have 2 doctrine entities with a many-to-many relationship and that works fine but I also would like to add an extra check on the many-to-many table. The problem is that in DQL I can not get to the right column.
I have thought of 2 possibilities: abstract parent class or native query. I will paste the native query that works below but it isn't what I was hoping for because we will have to copy and paste it.
So I want to make the relation + the type. so for this example I also need to check for type = 'Gallery'
gallery_object (Table)
id, tag, deleted
1, sponsors, 0
media_relations (Table)
id, mediaid, type, typeId
1, 37, gallery, 1
media_files
id, userid, filename, filepath, filesize, mime_type, date, deleted
37, 4, 533882_10151332524797037_1940030593_n_20.jpg, /resources/upload/www/, 82724, image/jpeg, 2013-01-25 15:04:46, 0
$rsm = new ResultSetMapping;
$rsm->addEntityResult(Helper::getNamespace('Gallery', 'Models/Entities') . "Object", 'o')
->addFieldResult('o', 'id', 'id')
->addFieldResult('o', 'tag', 'tag')
->addFieldResult('o', 'deleted', 'deleted')
->addJoinedEntityResult(ltrim(Helper::getNamespace('Media', 'Models/Entities') . "Media", '\\'), 'm', 'o', 'file')
->addFieldResult('o', 'file', 'id')
->addFieldResult('m', 'mid', 'id')
->addFieldResult('m', 'user', 'userid')
->addFieldResult('m', 'filename', 'filename')
->addFieldResult('m', 'filepath', 'filepath')
->addFieldResult('m', 'filesize', 'filesize')
->addFieldResult('m', 'mime_type', 'mime_type')
->addFieldResult('m', 'date', 'date')
->addFieldResult('m', 'mdeleted', 'deleted');
$sql = "SELECT o.id AS oid, m.id as file, o.tag, o.deleted, m.id AS mid, m.userid, m.filename, m.filepath, m.filesize, m.mime_type, m.date, m.deleted as mdeleted
FROM gallery_object as o
INNER JOIN media_relations mr ON (mr.typeid = o.id AND mr.type = 'Gallery')
INNER JOIN media_files m ON (mr.mediaid = m.id)";
$result = $this->_em->createNativeQuery($sql, $rsm)->getResult();
return $result;
The entities look like
/**
* Media entity
*
* @Entity(repositoryClass = "iTet\Application\Modules\Media\Models\Repositories\Media")
* @Table(name="media_files")
* @author Stephen Fenne
*/
class Media
{
/**
* @Id
* @Column(type="integer")
* @GeneratedValue
* @var int
*/
protected $id;
/**
* @ManyToOne(targetEntity="iTet\Application\Modules\Core\User\Models\Entities\User")
* @JoinColumn(name="userId", referencedColumnName="id")
* @var int
*/
protected $user;
/**
* @Column(length = 100)
* @var string
*/
protected $filename;
/**
* @Column
* @var string
*/
protected $filepath;
/**
* @Column(type = "integer")
* @var int
*/
protected $filesize;
/**
* @Column
* @var string
*/
protected $mime_type;
/**
* @Column(type = "datetime", nullable=true)
* @var \DateTime
*/
protected $date;
/**
* @Column(type = "integer")
* @var int
*/
protected $deleted = false;
--
/**
*
* @Entity(repositoryClass="iTet\Application\Modules\Gallery\Models\Repositories\Object")
* @Table(name="gallery_object")
* @author Ward Peeters <ward@coding-tech.com>
* @package
*/
class Object
{
/** @Id
* @Column(type="integer")
* @GeneratedValue
* @var int */
protected $id;
/** @ManyToMany(targetEntity="iTet\Application\Modules\Media\Models\Entities\Media")
* @JoinTable(name="media_relations",
* joinColumns={@JoinColumn(name="typeid", referencedColumnName="id")},
* inverseJoinColumns={@JoinColumn(name="mediaid", referencedColumnName="id")}
* )
* @var Media */
protected $file;
/** @Column
* @var string */
protected $tag;
/** @Column(type="integer")
* @var bool */
protected $deleted = false;
From what I can gather, your options are the following:
media_relations table, plus the appropriate associations with other tables.In both cases you'd then expose the underlying table to Doctrine such that you can use DQL to get at the data you need.
HTH