symfony3, doctrine, leftJoin

Question

I searched the forum in every way and tried every solution which i could find. Unfortunately nothing worked. I need to use leftJoin in doctrine and can't menage. In php my admin everything works fine.

SELECT a.`offer_id`, b.* FROM `location_to_job_offer` a LEFT JOIN `job_offer` b ON a.`offer_id` = b.`offer_id` WHERE `region_id` = 9 

I tried to use annotation assotiations and every possible queryBuilder variations. Every time I got different error.

My entities (I ommited getters and setters):

AppBundle\Entity\JobOffer

<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="job_offer")
 * @ORM\Entity(repositoryClass="AppBundle\Entity\JobOfferRepository")
 */
class JobOffer
{

    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $offerId;  

    /**
     * @ORM\Column(name="reference_id", type="string", length=128)
     */
    private $referenceId;

    /**
     * @ORM\Column(name="create_date", type="integer", length=128)
     */
    private $createDate;

    /**
     * @ORM\Column(name="modify_date", type="integer", length=128)
     */
    private $modifyDate;

    /**
     * @ORM\Column(name="expiration_date", type="integer", length=128)
     */
    private $expirationDate;

    /**
     * @ORM\Column(name="offer_source", type="integer", length=2)
     */
    private $offerSource;

    /**
     * @ORM\Column(name="trade1", type="integer", length=3)
     */
    private $trade1;

    /**
     * @ORM\Column(name="trade2", type="integer", length=3)
     */
    private $trade2;

    /**
     * @ORM\Column(name="trade3", type="integer", length=3)
     */
    private $trade3;

    /**
     * @ORM\Column(name="subtrades", type="string", length=512)
     */
    private $subtrades;

    /**
     * @ORM\Column(name="is_anonymous", name="is_anonymous", type="boolean")
     */
    private $isAnonymous;

    /**
     * @ORM\Column(name="is_practice", type="boolean")
     */
    private $isPractice;

    /**
     * @ORM\Column(name="is_internship", type="boolean")
     */
    private $isInternship;

    /**
     * @ORM\Column(name="is_volunteering", type="boolean")
     */
    private $isVolunteering;

    /**
     * @ORM\Column(name="is_any_form_of_employment", type="boolean")
     */
    private $anyFormOfEmployment;

    /**
     * @ORM\Column(name="is_full_time", type="boolean")
     */
    private $isFullTime;

    /**
     * @ORM\Column(name="is_part_time", type="boolean")
     */
    private $isPartTime;

    /**
     * @ORM\Column(name="is_temporary_work", type="boolean")
     */
    private $isTemporaryWork;

    /**
     * @ORM\Column(name="is_contract", type="boolean")
     */
    private $isContract;

    /**
     * @ORM\Column(name="is_salary_scope_from", type="decimal", precision=10, scale=2)
     */
    private $salaryScopeFrom;

    /**
     * @ORM\Column(name="is_salary_scope_to", type="decimal", precision=10, scale=2)
     */
    private $salaryScopeTo;

    /**
     * @ORM\Column(name="salary_currency", type="integer", length=3)
     */
    private $salaryCurrency;

    /**
     * @ORM\Column(name="reference_number", type="string", length=128)
     */
    private $referenceNumber;

    /**
     * @ORM\Column(name="employer_name", type="string", length=246)
     */
    private $employerName;

    /**
     * @ORM\Column(name="job_title", type="string", length=246)
     */
    private $jobTitle;

    /**
     * @ORM\Column(name="company_desc", type="text")
     */
    private $companyDesc;

    /**
     * @ORM\Column(name="job_desc", type="text")
     */
    private $jobDesc;

    /**
     * @ORM\Column(name="job_needs", type="text")
     */
    private $jobNeeds;

    /**
     * @ORM\Column(name="job_company_offers", type="text")
     */
    private $jobCompanyOffers;

    /**
     * @ORM\Column(name="job_required_docs", type="text")
     */
    private $jobRequiredDocs;

    /**
     * @ORM\Column(name="job_clause", type="text")
     */
    private $jobClause;

    /**
     * @ORM\Column(name="job_extra", type="text")
     */
    private $jobExtra;

    /**
     * @ORM\Column(name="job_company_contact_way", type="text")
     */
    private $jobCompanyContactWay;

    /**
     * @ORM\Column(name="job_apply_send_email", type="string", length=128)
     */
    private $jobApplySendEmail;

    /**
     * @ORM\Column(name="job_apply_external_url", type="string", length=128)
     */
    private $jobApplyExtUrl;

    /**
     * @ORM\Column(name="job_offer_status", type="boolean")
     */
    private $jobOfferStatus;

    /**
     * @ORM\Column(name="search_first_letter", type="string", length=1)
     */
    private $searchFirstLetter;

    /**
     * @ORM\Column(name="search_tags", type="text")
     */
    private $searchTags;

    /**
     * @ORM\Column(name="offer_url", type="text")
     */
    private $offerUrl;

    /**
     * @ORM\Column(name="countries_string", type="text")
     */
    private $countriesString;

    /**
     * @ORM\Column(name="regions_string", type="text")
     */
    private $regionsString;

    /**
     * Set referenceId
     *
     * @param string $referenceId
     *
     * @return JobOffer
     */        

AppBundle\Entity\LocationToJobOffer:

<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="location_to_job_offer")
 * @ORM\Entity(repositoryClass="AppBundle\Entity\LocationToJobOfferRepository")
 */
class LocationToJobOffer
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $locId;

    /**
     * @ORM\Column(name="offer_id", type="integer", length=128)
     */
    private $offerId;

    /**
     * @ORM\Column(name="country_id", type="integer", length=128)
     */
    private $countryId;

    /**
     * @ORM\Column(name="country_name", type="string", length=128)
     */
    private $countryName;

    /**
     * @ORM\Column(name="region_id", type="integer", length=128)
     */
    private $regionId;

    /**
     * @ORM\Column(name="region_name", type="string", length=128)
     */
    private $regionName;

    /**
     * @ORM\Column(name="city_id", type="integer", length=128)
     */
    private $cityId;

    /**
     * @ORM\Column(name="city_name", type="string", length=128)
     */
    private $cityName;

    /**
     * @ORM\Column(name="address", type="string", length=128)
     */
    private $address;

    /**
     * @ORM\Column(name="lat", type="float", length=128)
     */
    private $lat;

    /**
     * @ORM\Column(name="lng", type="float", length=128)
     */
    private $lng;

Can You point me in the right direction with this please? Many thanks in advance.

Answer 1

I found the answer for my problem.

AppBundle\Entity\JobOfferRepository

<?php

namespace AppBundle\Entity;

/**
 * JobOfferRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class JobOfferRepository extends \Doctrine\ORM\EntityRepository
{

    public function getPaginateOffersbyRegion($currentPage = 1, $quantity = 5, $regId)
    {
        $firstResult = ($currentPage * $quantity) - $quantity;

        return $this->createQueryBuilder('p')
                ->select('p')
                ->leftJoin('AppBundle:LocationToJobOffer', 'u', 'WITH', 'u.offerId = p.offerId')
                ->addSelect('u.cityName')
                ->where('u.regionId = :regId')
                ->setParameter('regId', $regId)
                ->addOrderBy('p.offerId', 'DESC')
                ->setFirstResult($firstResult)
                ->setMaxResults($quantity)
                ->getQuery()
                ->getArrayResult();
    }        
}

And everything works just fine now.

Answer 2

First of all, you must create association mapping.

Example:

...
class LocationToJobOffer
{
    /**
     * @ORM\ManyToOne(targetEntity="JobOffer")
     * @ORM\JoinColumn(name="offer_id", referencedColumnName="id")
     */
    private $jobOffers;
...
class JobOffer
{
    /**
     * @OneToMany(targetEntity="LocationToJobOffer", mappedBy="jobOffers")
     */
    private $locationToJobOffer;
...

After it, yours query will look like:

$query = $this->createQueryBuilder('LocationToJobOffer')
->select('LocationToJobOffer', 'JobOffers')
->leftJoint('LocationToJobOffer.jobOffers', 'JobOffers')
->where('LocationToJobOffer.regionId = 9')
->getQuery();