php - Doctrine Query Builder Error on Join: [Syntax Error] line 0, col 87: Error: Expected Literal, got 'JOIN' -
i building shipping system ecommerce site using doctrine. have appropriate shipping methods , prices based on product , region data in checkout.
i using following code querybuilder:
$shippingpricereccords = $this->em->createquerybuilder() ->select('price') ->from('ordershippingprice', 'price') ->innerjoin('ordershippingmethod', 'method', 'price.fkordershippingmethod = method.id') ->innerjoin('ordershippingmethodregionmapping', 'map', 'map.fkordershippingmethod = method.id') ->where('price.fkproducttype = :fkproducttype') ->andwhere('price.fkbrand = :fkbrand') ->andwhere('map.fkregion = :fkregion') ->setparameters([ 'fkproducttype' => $fkproducttype, 'fkbrand' => $fkbrand, 'fkregion' => $regionid, ]) ->getquery() ->setfetchmode("ordercart", "address", \doctrine\orm\mapping\classmetadata::fetch_eager) ->getresult(); but failing , giving me error:
[syntax error] line 0, col 87: error: expected literal, got 'join' the dql above query:
select price ordershippingprice price inner join ordershippingmethod method inner join ordershippingmethodregionmapping map price.fkproducttype = :fkproducttype , price.fkbrand = :fkbrand , map.fkregion = :fkregion ordershippingprice contains:
<?php use doctrine\orm\mapping orm; use doctrine\common\collections\collection; use doctrine\common\collections\arraycollection; /** * ordershippingprice * * @orm\table(name="ordershippingprice") * @orm\entity */ class ordershippingprice { /** * * normal string / int object data * */ /** * @var int * * @orm\column(name="id", type="integer", nullable=false) * @orm\id * @orm\generatedvalue(strategy="identity") */ private $id; /** * @var int * * @orm\column(name="fkordershippingmethod", type="integer", nullable=true) */ private $fkordershippingmethod = '0'; /** * @var float * * @orm\column(name="price", type="float", precision=7, scale=2, nullable=false) */ private $price = '0.00'; /** * @var int * * @orm\column(name="fkproducttype", type="integer", nullable=true) */ private $fkproducttype = '0'; /** * @var int * * @orm\column(name="fkbrand", type="integer", nullable=true) */ private $fkbrand = '0'; /** * @var string * * @orm\column(name="isexpeditable", type="string", length=16, nullable=false) */ private $isexpeditable = '0'; /** * @var string * * @orm\column(name="expeditedescription", type="text", length=65535, nullable=false) */ private $expeditedescription = ''; /** * @var string * * @orm\column(name="showliftgateoption", type="string", length=16, nullable=false) */ private $showliftgateoption = '0'; /** * @var string * * @orm\column(name="showdestinationoption", type="string", length=16, nullable=false) */ private $showdestinationoption = '0'; /** * @var string * * @orm\column(name="productnotavailable", type="string", length=16, nullable=false) */ private $productnotavailable = '0'; /** * * relationship managment propperties * */ /** * many ordershippingprices have 1 ordershippingmethod. * @orm\manytoone(targetentity="ordershippingmethod", fetch="eager") * @orm\joincolumn(name="fkordershippingmethod", referencedcolumnname="id") **/ private $ordershippingmethod; /** * orderordershippingmethod * * @return ordershippingmethod */ public function getordershippingmethod() { return $this->ordershippingmethod; } /** * * normal string / int getters , setters * */ /** * id * * @return int */ public function getid() { return $this->id; } /** * set fkordershippingmethod * * @param string $fkordershippingmethod * * @return ordercart */ public function setfkordershippingmethod($fkordershippingmethod) { $this->fkordershippingmethod = $fkordershippingmethod; return $this; } /** * fkordershippingmethod * * @return string */ public function getfkordershippingmethod() { return $this->fkordershippingmethod; } /** * set price * * @param string $price * * @return ordercart */ public function setprice($price) { $this->price = $price; return $this; } /** * price * * @return string */ public function getprice() { return $this->price; } /** * set fkproducttype * * @param string $fkproducttype * * @return ordercart */ public function setfkproducttype($fkproducttype) { $this->fkproducttype = $fkproducttype; return $this; } /** * fkproducttype * * @return string */ public function getfkproducttype() { return $this->fkproducttype; } /** * set fkbrand * * @param string $fkbrand * * @return ordercart */ public function setfkbrand($fkbrand) { $this->fkbrand = $fkbrand; return $this; } /** * fkbrand * * @return string */ public function getfkbrand() { return $this->fkbrand; } /** * set isexpeditable * * @param string $isexpeditable * * @return ordercart */ public function setisexpeditable($isexpeditable) { $this->isexpeditable = $isexpeditable; return $this; } /** * isexpeditable * * @return string */ public function getisexpeditable() { return $this->isexpeditable; } /** * set expeditedescription * * @param string $expeditedescription * * @return ordercart */ public function setexpeditedescription($expeditedescription) { $this->expeditedescription = $expeditedescription; return $this; } /** * expeditedescription * * @return string */ public function getexpeditedescription() { return $this->expeditedescription; } /** * set showliftgateoption * * @param string $showliftgateoption * * @return ordercart */ public function setshowliftgateoption($showliftgateoption) { $this->showliftgateoption = $showliftgateoption; return $this; } /** * showliftgateoption * * @return string */ public function getshowliftgateoption() { return $this->showliftgateoption; } /** * set showdestinationoption * * @param string $showdestinationoption * * @return ordercart */ public function setshowdestinationoption($showdestinationoption) { $this->showdestinationoption = $showdestinationoption; return $this; } /** * showdestinationoption * * @return string */ public function getshowdestinationoption() { return $this->showdestinationoption; } /** * set productnotavailable * * @param string $productnotavailable * * @return ordercart */ public function setproductnotavailable($productnotavailable) { $this->productnotavailable = $productnotavailable; return $this; } /** * productnotavailable * * @return string */ public function getproductnotavailable() { return $this->productnotavailable; } } ordershippingmethod contains:
use doctrine\orm\mapping orm; use doctrine\common\collections\collection; use doctrine\common\collections\arraycollection; /** * ordershippingmethod * * @orm\table(name="ordershippingmethod") * @orm\entity */ class ordershippingmethod { /** * * normal string / int object data * */ /** * @var int * * @orm\column(name="id", type="integer", nullable=false) * @orm\id * @orm\generatedvalue(strategy="identity") */ private $id; /** * @var string * * @orm\column(name="name", type="text", length=255, nullable=false) */ private $name = ''; /** * @var string * * @orm\column(name="carrier", type="string", length=32, nullable=false) */ private $carrier = ''; /** * @var string * * @orm\column(name="bvcode", type="string", length=32, nullable=false) */ private $bvcode = ''; /** * @var string * * @orm\column(name="trackingurl", type="string", length=255, nullable=true) */ private $trackingurl = ''; /** * @var int * * @orm\column(name="minimumleadtime", type="integer", nullable=false) */ private $minimumleadtime = '0'; /** * @var int * * @orm\column(name="maximumleadtime", type="integer", nullable=false) */ private $maximumleadtime = '0'; /** * * relationship managment propperties * */ /** * @orm\onetomany(targetentity="ordershippingmethodregionmapping", mappedby="ordershippingmethod", cascade={"persist", "remove"}, orphanremoval=true, fetch="eager") * @var ordershippingmethodregionmappings[] **/ public $ordershippingmethodregionmappings; /** * constructor * * create array collection of ordershippingmethodregionmappings */ public function __construct() { $this->ordershippingmethodregionmappings = new arraycollection(); } /** * ordershippingmethodregionmapping(s) * * @return array */ public function getordershippingmethodregionmappings() { return $this->ordershippingmethodregionmappings; } /** * * normal string / int getters , setters * */ /** * id * * @return int */ public function getid() { return $this->id; } /** * set name * * @param int $name * * @return ordercart */ public function setname($name) { $this->name = $name; return $this; } /** * name * * @return int */ public function getname() { return $this->name; } /** * set carrier * * @param string $carrier * * @return ordercart */ public function setcarrier($carrier) { $this->carrier = $carrier; return $this; } /** * carrier * * @return string */ public function getcarrier() { return $this->carrier; } /** * set bvcode * * @param string $bvcode * * @return ordercart */ public function setbvcode($bvcode) { $this->bvcode = $bvcode; return $this; } /** * bvcode * * @return string */ public function getbvcode() { return $this->bvcode; } /** * set trackingurl * * @param string $trackingurl * * @return ordercart */ public function settrackingurl($trackingurl) { $this->trackingurl = $trackingurl; return $this; } /** * trackingurl * * @return string */ public function gettrackingurl() { return $this->trackingurl; } /** * set minimumleadtime * * @param string $minimumleadtime * * @return ordercart */ public function setminimumleadtime($minimumleadtime) { $this->minimumleadtime = $minimumleadtime; return $this; } /** * minimumleadtime * * @return string */ public function getminimumleadtime() { return $this->minimumleadtime; } /** * set maximumleadtime * * @param string $maximumleadtime * * @return ordercart */ public function setmaximumleadtime($maximumleadtime) { $this->maximumleadtime = $maximumleadtime; return $this; } /** * maximumleadtime * * @return string */ public function getmaximumleadtime() { return $this->maximumleadtime; } } ordershippingmethodregionmapping contains:
<?php use doctrine\orm\mapping orm; use doctrine\common\collections\collection; use doctrine\common\collections\arraycollection; /** * ordershippingmethodregionmapping * * @orm\table(name="ordershippingmethodregionmapping") * @orm\entity */ class ordershippingmethodregionmapping { /** * * normal string / int object data * */ /** * @var int * * @orm\column(name="id", type="integer", nullable=false) * @orm\id * @orm\generatedvalue(strategy="identity") */ private $id; /** * @var int * * @orm\column(name="fkordershippingmethod", type="integer") */ private $fkordershippingmethod = '0'; /** * @var int * * @orm\column(name="fkorderregion", type="integer") */ private $fkorderregion = '0'; /** * * relationship managment propperties * */ /** * many ordershippingprices have 1 ordershippingmethod. * @orm\manytoone(targetentity="ordershippingmethod", inversedby="ordershippingmethodregionmappings") * @orm\joincolumn(name="fkordershippingmethod", referencedcolumnname="id") **/ private $ordershippingmethod; /** * sets new ordershippingmethod , cleans previous 1 if set * @param ordershippingmethod */ public function setordershippingmethod(ordershippingmethod $ordershippingmethod) { $this->ordershippingmethod = $ordershippingmethod; } /** * orderordershippingmethod * * @return ordershippingmethod */ public function getordershippingmethod() { return $this->ordershippingmethod; } /** * * normal string / int getters , setters * */ /** * id * * @return int */ public function getid() { return $this->id; } /** * set fkordershippingmethod * * @param int $fkordershippingmethod * * @return ordercart */ public function setfkordershippingmethod($fkordershippingmethod) { $this->fkordershippingmethod = $fkordershippingmethod; return $this; } /** * fkordershippingmethod * * @return int */ public function getfkordershippingmethod() { return $this->fkordershippingmethod; } /** * set fkorderregion * * @param int $fkorderregion * * @return ordercart */ public function setfkorderregion($fkorderregion) { $this->fkorderregion = $fkorderregion; return $this; } /** * fkorderregion * * @return int */ public function getfkorderregion() { return $this->fkorderregion; } } can tell me doing wrong? help.
you missing argument in innerjoin method call. have :
$shippingpricereccords = $this->em->createquerybuilder() ->select('price') ->from('ordershippingprice', 'price') ->innerjoin('ordershippingmethod', 'method', 'with', 'price.fkordershippingmethod = method.id') ->innerjoin('ordershippingmethodregionmapping', 'map', 'with', 'map.fkordershippingmethod = method.id') ->where('price.fkproducttype = :fkproducttype') ->andwhere('price.fkbrand = :fkbrand') ->andwhere('map.fkregion = :fkregion') ->setparameters([ 'fkproducttype' => $fkproducttype, 'fkbrand' => $fkbrand, 'fkregion' => $regionid, ]) ->getquery() ->setfetchmode("ordercart", "address", \doctrine\orm\mapping\classmetadata::fetch_eager) ->getresult(); see documentation :
// example - $qb->innerjoin('u.group', 'g', expr\join::with, $qb->expr()->eq('u.status_id', '?1')) // example - $qb->innerjoin('u.group', 'g', 'with', 'u.status = ?1') // example - $qb->innerjoin('u.group', 'g', 'with', 'u.status = ?1', 'g.id') public function innerjoin($join, $alias, $conditiontype = null, $condition = null, $indexby = null);
Comments
Post a Comment