An issue with join doctrine

Question

I'm writing my CMS with my own frame, using doctrince 2.5+.

My database table like this:

Item table:

+---------------------+-------------+------+-----+---------+-------+
| Field               | Type        | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+-------+
| id                  | int(11)     | NO   | PRI | NULL    |       |
| owner_id            | int(11)     | NO   | MUL | NULL    |       |
| item_id             | int(7)      | NO   | MUL | NULL    |       |
| count               | bigint(20)  | NO   |     | NULL    |       |
| money               | varchar(32) | NO   |     | NULL    |       |
| name                | varchar(32) | NO   |     | NULL    |       |
+---------------------+-------------+------+-----+---------+-------+

Person table:

+------------------------+----------------------+------+-----+---------+-------+
| Field                  | Type                 | Null | Key | Default | Extra |
+------------------------+----------------------+------+-----+---------+-------+
| Id                     | int(11)              | NO   | PRI | 0       |       |
| name                   | varchar(35)          | NO   | UNI |         |       |
| age                    | int                  | NO   |     |         |       |
+------------------------+----------------------+------+-----+---------+-------+

I declared models like these: Item.php

class Item{
    /** @id @Column(type="integer", name="id")**/
    public $id;
    /** @Column(type="integer", name="owner_id")**/
    public $owner_id;
    /** @Column(type="integer", name = "item_id")**/
    public $item_id;
    /** @Column(type="bigint", name = "count")**/
    public $count;
    /** @Column(type="string", name = "money")**/
    public $money;
    /** @Column(type="string", name = "name")**/
    public $name;
}

Pesron.php

 class Person{
        /** @id @Column(type="integer", name="Id")**/
        public $id;
        /** @Column(type="string", name="name", unique=true)**/
        public $name;
        /** @Column(type="integer", name="age")**/
        public $age;
 }

The rule is : people could have many items. As above, i don't use foreign key in models of PHP even in mysql has. The reason's to reduce data get from MySql. But now i countered the SQL with JOIN like this.

SELECT i.*,p.name as Owner FROM Item i JOINT Person p on i.owner_id = p.Id

I take a look on Join query and when a question on stackoverflow and i come to this DQL:

$qb = $this->em->createQueryBuilder();  
            $qb->select('i')
            ->from('item','i')->join('a.person', 'p');

I know this DQL is wrong. Because i got no character property on item table. Please give me a hint about DQL. If couldn't then i think i would use pure SQL, which i don't want to do it. Please again. Thanks in advance.

Please re-word your question because it's not clear what you're asking - do you want to just translate that SQL query into DQL? What's `a.character` there (there's no column called `character` anywhere!)? The bits where you explain what your tables and entities look like is ok, but the text/question afterwards is very unclear (to me, anyway)... — MikO, Dec 14 '16 at 12:39

score 1 · Accepted Answer · answered Dec 14 '16 at 12:43

1

You wrote:

$qb->select('i')
            ->from('item','i')->join('a.character', 'u');

Why are you using 'a' and 'u' ? Do you at least understand what you are writing? You can't refer to random letters like that.

The alias 'i' is only used because you have defined i as alias for item in your from.

->leftJoin('character', 'cha', 'i.owner_id = cha.id')

In this exemple I defined an alias 'cha' for character, then you can used 'cha' to make the join

answered Dec 14 '16 at 12:43

goto

7,908
10
48
58

In [Doctrine document about query builder](http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html) at `15.2.1. High level API methods` they wrote : `// 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', 'g.id') public function innerJoin($join, $alias, $conditionType = null, $condition = null, $indexBy = null);` – Haruji Burke Dec 15 '16 at 02:34
I wrote as it but not work. I tried yours and work well. Why? Is there something wrong with Doctrine document? Is there any library as MSDN for doctrine? Could u please show me where it is in `vendor` folder? – Haruji Burke Dec 15 '16 at 02:37
Thank a lot for your help. – Haruji Burke Dec 15 '16 at 02:37

An issue with join doctrine

1 Answers1