PostgreSQL, Doctrine SQL syntax error; MySQL works

Question

Changing a working Symfony application from MySQL to PostgreSQL causes

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "WHERE" LINE 1: ...Id AND a4_.functionName = 'expiringAlertsAction') WHERE SUBS...

I am so far unable to determine why this occurs. With MySQL, the query returns the expected results. My understanding is that the error is somewhere immediately before the WHERE, but none of my trial changes has worked.

repository source code:

    public function expiringOppsSent($sent = true)
    {
        $nextMonth = date_add(new \DateTime(), new \DateInterval('P1M'));
        $expiryMonth = date_format($nextMonth, 'm');
        $expiryYear = date_format($nextMonth, 'Y');
        $expireDateCriterion = ($sent) ? "a.date IS NOT NULL" : "a.date IS NULL";
        $sqlString = 
<<<EOT
SELECT op 
FROM TruckeeVolunteerBundle:Opportunity op 
JOIN TruckeeVolunteerBundle:Organization o 
WITH op.organization = o 
JOIN TruckeeVolunteerBundle:Staff s WITH s.organization = o 
LEFT JOIN TruckeeVolunteerBundle:AdminOutbox a 
WITH op.id = a.oppId AND a.functionName = 'expiringAlertsAction'
WHERE SUBSTRING(op.expireDate, 6, 2) = '$expiryMonth' AND 
SUBSTRING(op.expireDate, 1, 4) = '$expiryYear' AND 
(op.expireDate <> a.date OR 
$expireDateCriterion) AND 
op.active = 1 AND o.active = 1 ORDER BY o.orgName
EOT;
        $sent = $this->getEntityManager()
                        ->createQuery($sqlString)->getResult();
        return $sent;

code as prepared by Doctrine:

SELECT 
  o0_.id AS id_0, 
  o0_.oppName AS oppName_1, 
  o0_.add_date AS add_date_2, 
  o0_.lastUpdate AS lastUpdate_3, 
  o0_.minAge AS minAge_4, 
  o0_.active AS active_5, 
  o0_.group_ok AS group_ok_6, 
  o0_.expireDate AS expireDate_7, 
  o0_.description AS description_8, 
  o0_.orgId AS orgId_9 
FROM 
  opportunity o0_ 
  INNER JOIN organization o1_ ON (o0_.orgId = o1_.id) 
  INNER JOIN staff s2_ 
  INNER JOIN person p3_ ON s2_.id = p3_.id 
  AND (s2_.orgId = o0_.orgId) 
  LEFT JOIN admin_outbox a4_ ON (
    o0_.id = a4_.oppId 
    AND a4_.function = 'expiringAlertsAction'
  ) 
WHERE 
  MONTH(o0_.expireDate) = '09' 
  AND YEAR(o0_.expireDate) = '2015' 
  AND (
    o0_.expireDate <> a4_.date 
    OR a4_.date IS NOT NULL
  ) 
  AND o0_.active = 1 
  AND o1_.active = 1 
ORDER BY 
  o1_.orgName ASC

Opoortunity entity

class Opportunity
{
    public function __construct()
    {
        $this->volunteers = new ArrayCollection();
        $this->email = new ArrayCollection();
        $this->searches = new ArrayCollection();
    }

    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="oppName", type="string", length=66, nullable=true)
     * @Assert\NotBlank(message = "Opportunity name is required")
     */
    private $oppName;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="add_date", type="date", nullable=true)
     */
    private $addDate;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="lastUpdate", type="datetime", nullable=true)
     */
    private $lastupdate;

    /**
     * @var integer
     *
     * @ORM\Column(name="minAge", type="integer", nullable=true)
     */
    private $minage;

    /**
     * @var boolean
     *
     * @ORM\Column(name="active", type="boolean", nullable=true)
     */
    private $active;

    /**
     * @var boolean
     *
     * @ORM\Column(name="group_ok", type="boolean", nullable=true)
     */
    private $groupOk;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="expireDate", type="date", nullable=true)
     * @Assert\Date()
     */
    private $expireDate;

    /**
     * @var string
     *
     * @ORM\Column(name="description", type="text", nullable=true)
     * @Assert\NotBlank(message="Description is required")
     */
    private $description;

    /**
     * @var \Truckee\VolunteerBundle\Entity\Organization
     *
     * @ORM\ManyToOne(targetEntity="Truckee\VolunteerBundle\Entity\Organization", inversedBy="opportunities", cascade={"persist","remove"})
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="orgId", referencedColumnName="id")
     * })
     */
    private $organization;

    /**
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\OneToMany(targetEntity="Truckee\VolunteerBundle\Entity\Search", mappedBy="opportunity", cascade={"persist","remove","merge"})
     */
    protected $searches;

    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set name
     *
     * @param string $name
     * @return Opportunity
     */
    public function setOppName($name)
    {
        $this->oppName = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string 
     */
    public function getOppName()
    {
        return $this->oppName;
    }

    /**
     * Set addDate
     *
     * @param \DateTime $addDate
     * @return Opportunity
     */
    public function setAddDate($addDate)
    {
        $this->addDate = $addDate;

        return $this;
    }

    /**
     * Get addDate
     *
     * @return \DateTime 
     */
    public function getAddDate()
    {
        return $this->addDate;
    }

    /**
     * Set lastupdate
     *
     * @param \DateTime $lastupdate
     * @return Opportunity
     */
    public function setLastupdate($lastupdate)
    {
        $this->lastupdate = $lastupdate;

        return $this;
    }

    /**
     * Get lastupdate
     *
     * @return \DateTime 
     */
    public function getLastupdate()
    {
        return $this->lastupdate;
    }

    /**
     * Set minage
     *
     * @param integer $minage
     * @return Opportunity
     */
    public function setMinage($minage)
    {
        $this->minage = $minage;

        return $this;
    }

    /**
     * Get minage
     *
     * @return integer 
     */
    public function getMinage()
    {
        return $this->minage;
    }

    /**
     * Set active
     *
     * @param boolean $active
     * @return Opportunity
     */
    public function setActive($active)
    {
        $this->active = $active;

        return $this;
    }

    /**
     * Get active
     *
     * @return boolean 
     */
    public function getActive()
    {
        return $this->active;
    }

    /**
     * Set group
     *
     * @param boolean $groupOk
     * @return Opportunity
     */
    public function setGroupOk($groupOk)
    {
        $this->groupOk = $groupOk;

        return $this;
    }

    /**
     * Get group
     *
     * @return boolean 
     */
    public function getGroupOk()
    {
        return $this->groupOk;
    }

    /**
     * Set expiredate
     *
     * @param \DateTime $expiredate
     * @return Opportunity
     */
    public function setExpireDate($expiredate)
    {
        $this->expireDate = $expiredate;

        return $this;
    }

    /**
     * Get expiredate
     *
     * @return \DateTime 
     */
    public function getExpireDate()
    {
        return $this->expireDate;
    }

    /**
     * Set description
     *
     * @param string $description
     * @return Opportunity
     */
    public function setDescription($description)
    {
        $this->description = $description;

        return $this;
    }

    /**
     * Get description
     *
     * @return string 
     */
    public function getDescription()
    {
        return $this->description;
    }

    /**
     * Set organization
     *
     * @param \Truckee\VolunteerBundle\Entity\Organization $organization
     * @return Opportunity
     */
    public function setOrganization(\Truckee\VolunteerBundle\Entity\Organization $organization = null)
    {
        $this->organization = $organization;

        return $this;
    }

    /**
     * Get organization
     *
     * @return \Truckee\VolunteerBundle\Entity\Organization 
     */
    public function getOrganization()
    {
        return $this->organization;
    }

    /**
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\ManyToMany(targetEntity="Skill", inversedBy="opportunities", cascade={"persist"})
     * @ORM\JoinTable(name="opp_skill",
     *      joinColumns={@ORM\JoinColumn(name="oppId", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="skillId", referencedColumnName="id")}
     *      ))
     * @Assert\NotNull(message="Please select at least one")
     */
    protected $skills;

    /**
     * Add skills
     *
     * @param \Truckee\VolunteerBundle\Entity\Skill $skills
     * @return Opportunity
     */
    public function addSkill(\Truckee\VolunteerBundle\Entity\Skill $skill)
    {
        $this->skills[] = $skill;

        return $this;
    }

    /**
     * Remove skills
     *
     * @param \Truckee\VolunteerBundle\Entity\Skill $skills
     */
    public function removeSkill(\Truckee\VolunteerBundle\Entity\Skill $skill)
    {
        $this->skills->removeElement($skill);
    }

    /**
     * Get skills
     *
     * @return \Doctrine\Common\Collections\Collection 
     */
    public function getSkills()
    {
        return $this->skills;

    }

    /**
     * Get search
     *
     * @return \Doctrine\Common\Collections\Collection 
     */
    public function getSearches()
    {
        return $this->searches;
    }
}

Organization entity

class Organization
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    protected $id;

    /**
     * @var string
     *
     * @ORM\Column(name="orgName", type="string", length=65, nullable=true)
     * @Assert\NotBlank(message="Organization name is required")
     */
    protected $orgName;

    /**
     * @var string
     *
     * @ORM\Column(name="address", type="string", length=50, nullable=true)
     */
    protected $address;

    /**
     * @var string
     *
     * @ORM\Column(name="city", type="string", length=50, nullable=true)
     */
    protected $city;

    /**
     * @var string
     *
     * @ORM\Column(name="state", type="string", length=50, nullable=true)
     */
    protected $state;

    /**
     * @var string
     *
     * @ORM\Column(name="zip", type="string", length=10, nullable=true)
     */
    protected $zip;

    /**
     * @var string
     *
     * @ORM\Column(name="phone", type="string", length=50, nullable=true)
     * @V2Assert\PhoneNumber
     */
    protected $phone;

    /**
     * @var string
     *
     * @ORM\Column(name="website", type="string", length=50, nullable=true)
     */
    protected $website;

    /**
     * @var boolean
     *
     * @ORM\Column(name="active", type="boolean", nullable=true)
     */
    protected $active;

    /**
     * @var boolean
     *
     * @ORM\Column(name="temp", type="boolean", nullable=false)
     */
    protected $temp;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="add_date", type="datetime", nullable=true)
     */
    protected $addDate;

    /**
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\OneToMany(targetEntity="Truckee\VolunteerBundle\Entity\Opportunity", mappedBy="organization", cascade={"persist","remove"})
     */
    protected $opportunities;

    /**
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\OneToMany(targetEntity="Truckee\VolunteerBundle\Entity\Search", mappedBy="organization", cascade={"persist","remove","merge"})
     */
    protected $searches;

    /**
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\OneToMany(targetEntity="Truckee\VolunteerBundle\Entity\Staff", mappedBy="organization", cascade={"persist","remove","merge"})
     */
    protected $staff;

    /**
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\ManyToMany(targetEntity="Focus", inversedBy="organizations", cascade={"persist"})
     * @ORM\JoinTable(name="org_focus",
     *      joinColumns={@ORM\JoinColumn(name="orgId", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="focusId", referencedColumnName="id")}
     *      ))
     * @Assert\NotNull(message="Please select at least one")
     */
    protected $focuses;

    /**
     *  @ORM\Column(name="background", type="boolean", nullable=true)
     */
    protected $background;

    /**
     * Constructor
     */
    public function __construct()
    {
        $this->opportunities = new ArrayCollection();
        $this->staff = new ArrayCollection();
        $this->searches = new ArrayCollection();
        $this->focuses = new ArrayCollection();
        $this->active = true;
    }


    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set organization
     *
     * @param string $organization
     * @return Organization
     */
    public function setOrgName($name)
    {
        $this->orgName = $name;

        return $this;
    }

    /**
     * Get organization
     *
     * @return string 
     */
    public function getOrgName()
    {
        return $this->orgName;
    }

    /**
     * Set address
     *
     * @param string $address
     * @return Organization
     */
    public function setAddress($address)
    {
        $this->address = $address;

        return $this;
    }

    /**
     * Get address
     *
     * @return string 
     */
    public function getAddress()
    {
        return $this->address;
    }

    /**
     * Set city
     *
     * @param string $city
     * @return Organization
     */
    public function setCity($city)
    {
        $this->city = $city;

        return $this;
    }

    /**
     * Get city
     *
     * @return string 
     */
    public function getCity()
    {
        return $this->city;
    }

    /**
     * Set state
     *
     * @param string $state
     * @return Organization
     */
    public function setState($state)
    {
        $this->state = $state;

        return $this;
    }

    /**
     * Get state
     *
     * @return string 
     */
    public function getState()
    {
        return $this->state;
    }

    /**
     * Set zip
     *
     * @param string $zip
     * @return Organization
     */
    public function setZip($zip)
    {
        $this->zip = $zip;

        return $this;
    }

    /**
     * Get zip
     *
     * @return string 
     */
    public function getZip()
    {
        return $this->zip;
    }

    /**
     * Set phone
     *
     * @param string $phone
     * @return Organization
     */
    public function setPhone($phone)
    {
        $this->phone = $phone;

        return $this;
    }

    /**
     * Get phone
     *
     * @return string 
     */
    public function getPhone()
    {
        return $this->phone;
    }

    /**
     * Set website
     *
     * @param string $website
     * @return Organization
     */
    public function setWebsite($website)
    {
        $this->website = $website;

        return $this;
    }

    /**
     * Get website
     *
     * @return string 
     */
    public function getWebsite()
    {
        return $this->website;
    }

    /**
     * Set active
     *
     * @param boolean $active
     * @return Organization
     */
    public function setActive($active)
    {
        $this->active = $active;

        return $this;
    }

    /**
     * Get active
     *
     * @return boolean 
     */
    public function getActive()
    {
        return $this->active;
    }

    /**
     * Set temp
     *
     * @param boolean $temp
     * @return Organization
     */
    public function setTemp($temp)
    {
        $this->temp = $temp;

    }

    /**
     * Get temp
     *
     * @return boolean 
     */
    public function getTemp()
    {
        return $this->temp;
    }

    /**
     * Set addDate
     *
     * @param \DateTime $addDate
     * @return Organization
     */
    public function setAddDate($addDate)
    {
        $this->addDate = $addDate;

        return $this;
    }

    /**
     * Get addDate
     *
     * @return \DateTime 
     */
    public function getAddDate()
    {
        return $this->addDate;
    }

    /**
     * Add opportunities
     *
     * @param \Truckee\VolunteerBundle\Entity\Opportunity $opportunities
     * @return Organization
     */
    public function addOpportunity(\Truckee\VolunteerBundle\Entity\Opportunity $opportunity)
    {
        $this->opportunities[] = $opportunity;

        return $this;
    }

    /**
     * Remove opportunities
     *
     * @param \Truckee\VolunteerBundle\Entity\Opportunity $opportunities
     */
    public function removeOpportunity(\Truckee\VolunteerBundle\Entity\Opportunity $opportunity)
    {
        $this->opportunities->removeElement($opportunity);
    }

    /**
     * Get opportunities
     *
     * @return \Doctrine\Common\Collections\Collection 
     */
    public function getOpportunities()
    {
        return $this->opportunities;
    }

//    /**
//     * Add staff
//     *
//     * @param \Truckee\VolunteerBundle\Entity\Staff $staff
//     * @return Organization
//     */
//    public function addStaff(\Truckee\VolunteerBundle\Entity\Staff $staff)
//    {
//        echo "We found it!";die;
//        $this->staff[] = $staff;
//    
//        return $this;
//    }

//    /**
//     * Remove staff
//     *
//     * @param \Truckee\VolunteerBundle\Entity\Staff $staff
//     */
//    public function removeStaff(\Truckee\VolunteerBundle\Entity\Staff $staff)
//    {
//        $this->staff->removeElement($staff);
//    }

    /**
     * Get staff
     *
     * @return \Doctrine\Common\Collections\Collection 
     */
    public function getStaff()
    {
        return $this->staff;
    }

    /**
     * Add search
     *
     * @param \Truckee\VolunteerBundle\Entity\Search $search
     * @return Organization
     */
    public function addSearch(\Truckee\VolunteerBundle\Entity\Search $search)
    {
        $this->searches[] = $search;

        return $this;
    }

    /**
     * Remove search
     *
     * @param \Truckee\VolunteerBundle\Entity\Search $search
     */
    public function removeSearch(\Truckee\VolunteerBundle\Entity\Search $search)
    {
        $this->searches->removeElement($search);
    }

    /**
     * Get search
     *
     * @return \Doctrine\Common\Collections\Collection 
     */
    public function getSearch()
    {
        return $this->search;
    }

    /**
     * Add focuses
     *
     * @param \Truckee\VolunteerBundle\Entity\Focus $focuses
     * @return Organization
     */
    public function addFocus(\Truckee\VolunteerBundle\Entity\Focus $focus)
    {
        $this->focuses[] = $focus;

        return $this;
    }

    /**
     * Remove focuses
     *
     * @param \Truckee\VolunteerBundle\Entity\Focus $focuses
     */
    public function removeFocus(\Truckee\VolunteerBundle\Entity\Focus $focus)
    {
        $this->focuses->removeElement($focus);
    }

    /**
     * Get focuses
     *
     * @return \Doctrine\Common\Collections\Collection 
     */
    public function getFocuses()
    {
        return $this->focuses;
    }

    /**
     * @var boolean
     *
     * @ORM\Column(name="email", type="string", nullable=true)
     */
    protected $email;

    /**
     * Set email
     *
     * @param boolean $email
     * @return email
     */
    public function setEmail($email) {
        $this->email = $email;

        return $this;
    }

    /**
     * Get email
     *
     * @return boolean 
     */
    public function getEmail() {
        return $this->email;
    }

    /**
     * @var boolean
     *
     * @ORM\Column(name="areacode", type="integer", nullable=true)
     * @V2Assert\AreaCode
     */
    protected $areacode;

    /**
     * Set areacode
     *
     * @param $areacode
     * @return areacode
     */
    public function setAreacode($areacode) {
        $this->areacode = $areacode;

        return $this;
    }

    /**
     * Get areacode
     *
     * @return boolean 
     */
    public function getAreacode() {
        return $this->areacode;
    }

    /**
     * Set background
     *
     * @param $background
     * @return background
     */
    public function setBackground($background) {
        $this->background = $background;

        return $this;
    }

    /**
     * Get background
     *
     * @return boolean 
     */
    public function getBackground() {
        return $this->background;
    }
}

Staff entity

class Staff extends Person
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    protected $id;

    /**
     * @var \Truckee\VolunteerBundle\Entity\Organization
     *
     * @ORM\ManyToOne(targetEntity="Truckee\VolunteerBundle\Entity\Organization", inversedBy="staff", cascade={"persist","remove"})
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="orgId", referencedColumnName="id")
     * })
     */
    protected $organization;

    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set organization
     *
     * @param \Truckee\VolunteerBundle\Entity\Organization $organization
     * @return Staff
     */
    public function setOrganization(\Truckee\VolunteerBundle\Entity\Organization $organization = null)
    {
        $this->organization = $organization;

        return $this;
    }

    /**
     * Get organization
     *
     * @return \Truckee\VolunteerBundle\Entity\Organization 
     */
    public function getOrganization()
    {
        return $this->organization;
    }

}

Would it be an option to change it to use the query builder? — mickadoo
– mickadoo, Commented Aug 8, 2015 at 18:46
Why using string functions (substring()) on a date? expireDate looks like a date to me. Just extract the year and month from this date, or use a between construction to select the correct month. The database can now also use an index, when available. — Frank Heikens
– Frank Heikens, Commented Aug 9, 2015 at 8:12
@FrankHeikens Substring was a (unnecessary) hack to avoid adding a Doctrine extension. Since posting this I've learned that PosstgreSQL does not have a month() date function so your suggestion, while it works for MySQL, does not help with PostgreSQL. — geoB
– geoB, Commented Aug 9, 2015 at 12:54
@mickadoo I've confirmed that creating a QueryBuilder expression does not change the fundamental behavior of Doctrine - the join criterion for the Staff entity is still dropped. So while an option it does not help. — geoB
– geoB, Commented Aug 9, 2015 at 17:39
@FrankHeikens It turns out there exists a Doctrine extention that purports to add a month() function for both MySQL and PostgreSQL. I've added it and confirmed that it works for MySQL. For now I will assume that it also works with PostgreSQL; I remain stuck on the Doctrine issue. Next step is to see if there's a simpler way to get the result I need. — geoB
– geoB, Commented Aug 9, 2015 at 17:44

Francesco Abeni · Accepted Answer · 2015-08-08 18:48:15Z

3

AFAIK the error is because you have a missing criteria for on of your joins. This is an extract of the code prepared by Doctrine:

FROM opportunity o0_ 
    INNER JOIN organization o1_ 
        ON (o0_.orgId = o1_.id) 
    INNER JOIN staff s2_ <-- MISSING CRITERIA HERE 
    INNER JOIN person p3_ 
        ON s2_.id = p3_.id AND (s2_.orgId = o1_.id) 
    LEFT JOIN admin_outbox a4_ 
        ON (o0_.id = a4_.oppId AND a4_.functionName = 'expiringAlertsAction')

You should double check your entity definitions.

answered Aug 8, 2015 at 18:48

Francesco Abeni

4,2691 gold badge21 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Chris Over a year ago

My guess is that MySQL can figure out the join column by looking at the foreign key constraints and PostgreSQL maybe can't.

geoB Over a year ago

@FrancescoAbeni: Note that the source code includes a join criterion ...WITH s.organization = o because the Staff entity is in a ManyToOne relationship with the Organization entity. Doctrine removes the criterion, most likely because Staff & Opportunity entities have the same relationship to Organization. No change appears possible to Doctrine's behavior.

geoB Over a year ago

@FrancescoAbeni: It is true, though, that the statement runs (with a different error) if I add a criterion in pgAmin's Query. Doesn't help my app, though.

Francesco Abeni Over a year ago

@geoB yes, I only pointed out what the actual error is, to point you in the right direction. I am unable to tell what the underlying problem is. You may want to edit the question and add the relevant entity definitions.

geoB Over a year ago

@FrancescoAbeni Entities have been added. It will be interesting to see if there is some mechanism that causes Doctrine to retain join criteria.

|

geoB · Accepted Answer · 2015-08-09 21:12:40Z

Special thanks to @FrancescoAbeni for setting me on the right track. The original expiringOppsSent() reflected logic that had been replaced elsewhere. I've rewritten code to create the following which allowed Doctrine to retain join criteria.

It's worth noting that for this application I also needed to add orocrm/doctrine-extensions in order to have MySQL & PostgreSQL month() and year() functions. Also, I had to CREATE EXTENSION fuzzystrmatch in pgAdmin Query.

/**
 * Get opportunities where AdminOutbox function = expiringAlertsAction and month & year of now()
 * @return type
 */
public function expiringOppsSent()
{
    //find oppId in AdminOutbox where function = expiringAlertsAction and month & year of now()
    $month = date_format(new \DateTime(), 'm');
    $year = date_format(new \DateTime(), 'Y');
    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb->select('o')
        ->from('TruckeeVolunteerBundle:Opportunity', 'o')
        ->join('TruckeeVolunteerBundle:AdminOutbox', 'a', 'WITH', $qb->expr()->eq('a.oppId', 'o'))
        ->andWhere($qb->expr()->eq('month(a.date)', ':month'))
        ->andWhere($qb->expr()->eq('year(a.date)', ':year'))
        ->andWhere($qb->expr()->eq('a.function', ':function'))
        ->setParameter(':month', $month)
        ->setParameter(':year', $year)
        ->setParameter(':function', 'expiringAlertsAction')
    ;
    $sent = $qb->getQuery()->getResult();

    return $sent;
}

/**
 * Get opportunites expiring next month where there are no related entries in AdminOutbox
 */
public function expiringOppsNotSent()
{
    $nextMonth = date_add(new \DateTime(), new \DateInterval('P1M'));
    $expiryMonth = date_format($nextMonth, 'm');
    $expiryYear = date_format($nextMonth, 'Y');
    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb->select('o')
        ->from('TruckeeVolunteerBundle:Opportunity', 'o')
        ->leftJoin('TruckeeVolunteerBundle:AdminOutbox', 'a', 'WITH', $qb->expr()->eq('a.oppId', 'o'))
        ->andWhere($qb->expr()->eq('month(o.expireDate)', ':month'))
        ->andWhere($qb->expr()->eq('year(o.expireDate)', ':year'))
        ->andWhere('a.id is NULL')
        ->setParameter(':month', $expiryMonth)
        ->setParameter(':year', $expiryYear)
        ;
    $notSent = $qb->getQuery()->getResult();

    return $notSent;
}

Collectives™ on Stack Overflow

PostgreSQL, Doctrine SQL syntax error; MySQL works

repository source code:

code as prepared by Doctrine:

Opoortunity entity

Organization entity

Staff entity

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

repository source code:

code as prepared by Doctrine:

Opoortunity entity

Organization entity

Staff entity

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related