how to make a dql request with table database are not an entity

Question

I would like to understand why my DQL request doesn't work. I've to entity : books(ouvrages) and authors(auteurs), there is many to many relation, so in my database a join table is create books_authors (ouvrages_auteurs) with reference "id" of each other. I would like to use this join table for have all informations of my two entity books and authors.

I've make many request in my BooksRepository but it's doesn't work :

public function getOuvrageTradeByAuteur(array $autor){
    $query = $this->getEntityManager()->createQuery("SELECT o,a FROM SBMainBundle:Ouvrages o JOIN ouvrages_auteurs oa JOIN SBMainBundle:Auteurs a WHERE o.is_trade = true  AND o.id = oa.ouvrages_id AND oa.auteurs_id = a.id AND a.nom_auteur = :autor")
                    ->setParameter('autor',$autor);
    return $query->getResult();
}

public function getBooksTradeByAutor(array $autor){
    //requete DQL pour un many to many
    $query = $this->createQueryBuilder('o');
    $query->join('o.auteur','auteurs')
            ->where($query->expr()->in('auteurs.nomAuteur',$autor))
            ->andWhere('o.isTrade = true')
            ->orderBy('o.id','desc')
            ->addSelect('o,auteurs');
    return $query->getQuery()->getResult();
}

public function getBooksSellByAutor(array $autor){
    //requete DQL pour un many to many
    $query = $this->createQueryBuilder('o');
    $query->join('o.auteur','auteurs')
        ->where($query->expr()->in('auteurs.nomAuteur',$autor))
        ->andWhere('o.isSell = true')
        ->orderBy('o.id','desc')
        ->addSelect('o,auteurs');
    return $query->getQuery()->getResult();
}

public function getOuvragesEchangesByAutor($auteurs){
    //requete en DQL 2eme partie
    $query = $this->createQueryBuilder('o')->where('o.auteur = :autor')
                                            ->andWhere('o.isTrade = true')
                                            ->join('o.auteur','a')
                                            ->setParameter('autor',$auteurs)
                                            ->orderBy('o.id','desc')
                                            ->addSelect('a');
    return $query->getQuery()->getResult();
}

public function getOuvragesVentesByAutor($auteurs){
    //requete en DQL 2eme partie
    $query = $this->createQueryBuilder('o')->where('o.auteur = :autor')
                                            ->andWhere('o.isSell = true')
                                            ->join('o.auteur','a')
                                            ->setParameter('autor',$auteurs)
                                            ->orderBy('o.id','desc')
                                            ->addSelect('a');
    return $query->getQuery()->getResult();
}

Edit for error message :

[Semantical Error] line 0, col 45 near 'ouvrages_auteurs': Error: Class 'ouvrages_auteurs' is not defined.

500 Internal Server Error - QueryException 1 linked Exception: QueryException »

[2/2] QueryException: [Semantical Error] line 0, col 45 near 'ouvrages_auteurs': Error: Class 'ouvrages_auteurs' is not defined.

[1/2] QueryException: SELECT o,a FROM SBMainBundle:Ouvrages o JOIN ouvrages_auteurs oa JOIN SBMainBundle:Auteurs a WHERE o.is_trade = true AND o.id = oa.ouvrages_id AND oa.auteurs_id = a.id AND a.nom_auteur = :autor

Edit for the Nnew issue :

[Semantical Error] line 0, col 79 near 'auteur = :autor': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.

500 Internal Server Error - QueryException 1 linked Exception: QueryException »

[2/2] QueryException: [Semantical Error] line 0, col 79 near 'auteur = :autor': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.

[1/2] QueryException: SELECT o, a FROM SB\MainBundle\Entity\Ouvrages o INNER JOIN o.auteur a WHERE o.auteur = :autor AND o.isSell = true ORDER BY o.id desc

I've use this request :

    public function getOuvragesEchangesByAutor($auteurs){
    //requete en DQL 2eme partie
    $query = $this->createQueryBuilder('o')->where('o.auteur = :autor')
                                            ->andWhere('o.isTrade = true')
                                            ->join('o.auteur','a')
                                            ->setParameter('autor',$auteurs)
                                            ->orderBy('o.id','desc')
                                            ->addSelect('a');
    return $query->getQuery()->getResult();
}

Thanks for your help ! :D

Answer 1

That error informs that the problem probably is in your entity definition. ouvrages_auteurs must exist like an association property in your Ouvrages entity, but it is more simple call it as auteurs like this:

 // Ouvrage Entity definitions...

 /**
 * @ORM\ManyToMany(targetEntity="Auteurs", inversedBy="ouvrages")
 * @ORM\JoinTable(name="ouvrages_auteurs",
 *      joinColumns={@ORM\JoinColumn(name="ouvrage_id", referencedColumnName="id")},
 *      inverseJoinColumns={@ORM\JoinColumn(name="auteur_id", referencedColumnName="id")}
 *      )
 */
private $auteurs;

/*
 * Constructor
 */
public function __construct()
{
    $this->auteurs =  new \Doctrine\Common\Collections\ArrayCollection();
}

Later in your query you should create dql JOIN clauses like this type:

SELECT o, a
FROM SBMainBundle:Ouvrages o 
JOIN o.auteurs a 
WHERE o.is_trade = true  

all related auteurs with selected ouvrages will be returned in result, clause: o.id = oa.ouvrages_id is not necessary.

More info about Doctrine mapping here: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/association-mapping.html#many-to-many-unidirectional

EDIT for new issue:

I think problem is that if autors info are in auteurs then:

$query = $this->createQueryBuilder('o')->where('a.id = :autor')
                                        ->andWhere('o.isTrade = true')
                                        ->join('o.auteur','a')
                                        ->setParameter('autor',$auteurs)
                                        ->orderBy('o.id','desc')
                                        ->addSelect('a');