CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-ormdoctrine
JSGarcia
JSGarcia

Reputation: 566

Doctrine join a table with two

Good morning, as seen in the image below, I have some tables linked.

EER

Using Doctrine (in Symfony2) I'm trying to get an array of Objects Issue which itself contains all IssueMessages and IssueStatusChanged objects but can not.

I have no idea how I can do to join two tables (IssueMessage and IssueStatusChanged) to through their identifiers.

The most we've done is get all Issue with an account of the messages that have:

$dql = 'SELECT x, COUNT(im.id) FROM PanelBundle:Issue x LEFT JOIN PanelBundle:IssueMessages im WITH x.id = im.idIssue';

Does anyone could give me a hand?

THANKS!

Upvotes: 1

Views: 502

Answers (3)

Atul Pandya
Atul Pandya

Reputation: 333

If you using yml format for schema orm files than
first you need to write schema and mention oneToMany, manyToOne relationship with table fields & generate entity, repository class.

Than you can use join with two or more tables as below example:
Example of repository class file function:
----------------------------------------------------
public function getReportInfo($idUserDetail)
{
    $query = $this->createQueryBuilder('UR')
        ->select("UR.report_period_start_date, UR.report_period_end_date")
        ->leftJoin('UR.UserReportDetail', 'URD')
        ->andWhere('UR.id_user_detail = :id')
        ->setParameter('id', $id)
        ->orderBy('UR.report_year', 'DESC')         
        ->addOrderBy('UR.report_month', 'DESC')
        ->setMaxResults(1);

    $resultArray = $query->getQuery()->getArrayResult();

    return $resultArray;
}

You can call this function from controller action as below:
-------------------------------------------------------------
public function getUserDetailAction($idUserDetail)
{
    $em = $this->getDoctrine()->getManager();
    $userDetail = $em->getRepository(
        'DemoBundle:UserDetail')
        ->getReportInfo($idUserDetail);

    return $userDetail;
}

I hope this would be useful to you.

Upvotes: 1

giosh94mhz
giosh94mhz

Reputation: 3066

I think the problem reside in the DQL syntax (+ missing inverse relation?).

By writing this:

SELECT x, COUNT(im.id) FROM PanelBundle:Issue x
LEFT JOIN PanelBundle:IssueMessages im WITH x.id = im.idIssue

you are joining two "random" table based on the condition provided in the WITH clause. This should usually be ok, but it may confuse the Hydrator component.

In your case you should configure the OneToMany side of the relation in Issue entity, then write something like this:

SELECT x, COUNT(im.id) FROM PanelBundle:Issue x
LEFT JOIN x.issueMessages im

Hope it helps!

Upvotes: 0

Egg
Egg

Reputation: 1769

You want to use assication mapping; this will have Doctrine manage all the joins for you.

Once in place, $issue will always have the other associated models available automatically without you having to worry about joins.

For the example below (assuming you use annotation), to get messages for an issue just get the issue objects and then use $issue->getMessages();.

<?php

/** @Entity */
class issue
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    // ...

    /**
     * @OneToMany(targetEntity="issueMessages", mappedBy="issue")
     */
    private $messages;

    // ...

    public function __construct()
    {
        $this->messages = new Doctrine\Common\Collections\ArrayCollection();
    }
}

/** @Entity */
class issueMessages
{
    // ...

    /**
     * @ManyToOne(targetEntity="issue", inversedBy="messages")
     * @JoinColumn(name="issue_id", referencedColumnName="id")
     */
    private $issue;

    // ...
}

Upvotes: 1

Related Questions