How to map and use a DB View from Doctrine2

2019-05-26 23:10发布

I have a view on nomencladores schema called obtenerPaisesPorFabricanteProductoSolicitud. This is the content for the view:

SELECT
    ps.id AS psid,
    ps.nombre,
    fps.id AS fpsid
FROM
    (
        (
            nomencladores.pais ps
            JOIN nomencladores.pais_fabricante_producto_solicitud pfps ON ((pfps.pais_id = ps.id))
        )
        JOIN negocio.fabricante_producto_solicitud fps ON (
            (
                pfps.fabricante_producto_solicitud_id = fps.id
            )
        )
    );

I'm trying to map the view as follow:

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="nomencladores.obtenerPaisesPorFabricanteProductoSolicitud", schema="nomencladores")
 */
class ObtenerPaisesPorFabricanteProductoSolicitud
{
    /**
     * @ORM\Id
     * @ORM\Column(name="psid", type="integer", nullable=false, unique=true)
     */
    protected $ps;

    /**
     * @ORM\Column(name="fpsid", type="integer")
     */
    protected $fps;

    /**
     * @ORM\Column(name="nombre", type="string")
     */
    protected $nombre;

    public function getPs()
    {
        return $this->ps;
    }

    public function getFps()
    {
        return $this->fps;
    }

    public function getNombre()
    {
        return $this->nombre;
    }
}

But any time I run this code on it:

$ent = $em->getRepository("AppBundle:ObtenerPaisesPorFabricanteProductoSolicitud")->findBy(
    array(
        "fps" => $entF->getId()
    )
);

I got this result:

An exception occurred while executing 'SELECT t0.psid AS psid1, t0.fpsid AS fpsid2, t0.nombre AS nombre3 FROM nomencladores.obtenerPaisesPorFabricanteProductoSolicitud t0 WHERE t0.fpsid = ?' with params [22]: SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "nomencladores.obtenerpaisesporfabricanteproductosolicitud" does not exist LINE 1: ...d1, t0.fpsid AS fpsid2, t0.nombre AS nombre3 FROM nomenclado...

If I remove the annotations then the error transform on this:

"Class "AppBundle\Entity\ObtenerPaisesPorFabricanteProductoSolicitud" is not a valid entity or mapped super class."

Why Doctrine2 or Symfony tries to execute the query instead go through the view? How I can execute the view from Symfony2/Doctrine2 side?

EDIT

As a side note I'm using PostgreSQL as DB and it haves several squemas, in this case I wrote the view at nomencladores schemas but I tried putting also in public schema and none works, apparently Doctrine doesn't find the view on the schema

0条回答
登录 后发表回答