Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

Not unique table/alias mySql exception with two through relations on another through relation #1588

Open
sieppl opened this Issue · 11 comments

7 participants

@sieppl

Let's say we have Author, Category, AuthorHasCategory. Each author has a main category he is working on. Therefore the table "AuthorHasCategory" has a boolean "is_main". Category has n:1 relations to Foo and Bar to further describe the Category.

Now we define following relations on Author:

public function relations() {
    return array(
            'authorHasCategories' => array(self::HAS_MANY, 'AuthorHasCategory', 'author_id'),
            'mainCategory'=>array(self::HAS_ONE,'Category',array('category_id'=>'id'),'through'=>'authorHasCategories', 'on' => 'is_main = 1'),
            'mainCategoryFoo' => array(self::HAS_ONE, 'Foo', array('foo_id' => 'id'), 'through' => 'mainCategory'),
            //BUG: adding following declaration will not work due to a duplicate table alias exception from MySql
            'mainCategoryBar' => array(self::HAS_ONE, 'Bar', array('bar_id' => 'id'), 'through' => 'mainCategory')
    );
}

When mainCategoryFoo and mainCategoryBar are added to "with" at the same time, an mysql exception occurs:

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'authorHasCategories'.

When checking the sql query you will find twice:

 LEFT OUTER JOIN `author_has_category` `authorHasCategories` ON (`authorHasCategories`.`author_id`=`t`.`id`)

The first time for the actual pivot table and another time it seems the whole left other join chain is added another time instead of reuse existing joins.

Sorry for the abstract example, I had to cut it out from our current project.

@creocoder

I'm working on this. Sintax will be:

public function relations() {
    return array(
        'rel'=>array(...),
        'foo'=>array(...,'through'=>array('rel'=>'unique_alias1')), // enh sintax here
        'bar'=>array(...,'through'=>array('rel'=>'unique_alias2')), // enh sintax here
        'baz'=>array(...,'through'=>'rel'), // current sintax
    );
}
@creocoder

Or another approach:

public function relations() {
    return array(
        'rel'=>array(...),
        'foo'=>array(...,'through'=>array('rel')),
        'bar'=>array(...,'through'=>array('rel')),
    );
}

can produce one JOIN with 'rel'.

@sieppl

Thank you very much for confirming the issue and working on this!

@resurtm
Collaborator

Seems duplicate of #1271.

@creocoder, btw, your proposal is very close to my suggestion (#1687). :smiley:

UPD: oh just noticed, that you have suggested it month ago. So this is a very obvious solution, since both of us proposed it separately. :)

@acorncom

@creocoder @resurtm Do you have any code I can help work on for this? I've got a client who is hitting the same issue. They'll pay me to help make the fix if you want me help work on it ...

@acorncom acorncom referenced this issue from a commit in acorncom/yii
@acorncom acorncom Fix for a through bug if we have two relations using 'through'
Syntax would be:
'through'=>array('Post'=>'post_1')

instead of:
'through'=>'Post'

Fixes Issue #1588
71a136d
@acorncom acorncom referenced this issue from a commit in acorncom/yii
@acorncom acorncom Fix for a through bug if we have two relations using 'through'
Syntax would be:
'through'=>array('Post'=>'post_1')

instead of:
'through'=>'Post'

Fixes Issue #1588
6efb039
@acorncom

Sorry about the double-post above.

@creocoder @resurtm This patch is super messy (I'll clean it up if you think this is the direction we want to go), but does fix the issue on my end. Thoughts? Is there a better way to handle this?

Seems like we'd want to open a full blown pull request, make unit tests, etc. if we want to patch this.

But I'm not very comfortable working in amongst CJoinElement / CActiveFinder, so if you guys would rather patch this totally differently, I'll stand by and enjoy watching :-)

@acorncom

Patch above passes all current framwork/db unit tests for master, which use through. So don't think I broke anything in that regard :-)

@PatchRanger

@acorncom Would you mind making a pull request with your fix to main Yii repository? I wish this issue was going to be fixed somehow :)

@cebe cebe added this to the 1.1.17 milestone
@cebe cebe self-assigned this
@gunzino

Hello, I have applied the patch provided by @acorncom

I have the relations defined this way:

                    'uzivatelZamestnanec' => array(self::HAS_ONE, 'UzivatelZamestnanec', 'uzivatel_id', 'on'=> 'UzivatelZamestnanec.datum_ukoncenia=0'),
                    'pobocka_z'=>array(self::BELONGS_TO,'Pobocka',array('pobocka_id'=>'id'),'through'=>array('uzivatelZamestnanec' =>'uziv_2')),
                    'typ_pracovneho_pomeru'=>array(self::BELONGS_TO,'PracovnyPomerTyp',array('typ_pracovneho_pomeru'=>'id'),'through'=>array('uzivatelZamestnanec' => 'uziv_1')),

But this seems not to work, it stops rendering on this query:
SELECT COUNT(DISTINCT t.id) FROM uzivatel t LEFT OUTER JOIN uzivatel_zamestnanec uziv_2 ON (uziv_2.uzivatel_id=t.id) AND (datum_ukoncenia=0) LEFT OUTER JOIN pobocka pobocka_z ON (pobocka_z.id=uziv_2.pobocka_id) LEFT OUTER JOIN uzivatel_zamestnanec uziv_2 ON (uziv_2.uzivatel_id=t.id) AND (datum_ukoncenia=0) LEFT OUTER JOIN pracovnyPomerTyp typ_pracovneho_pomeru ON (typ_pracovneho_pomeru.id=uziv_2.typ_pracovneho_pomeru)

I can't figure out where's the problem, the query does not use the unique aliases, it uses the first ('uziv_2') only. I would be really appriciated if someone could help me, thank you.

@PatchRanger

the query does not use the unique aliases, it uses the first ('uziv_2') only

Confirmed, it always takes the first alias - so the resulting select is not such as expected.

@PatchRanger PatchRanger referenced this issue from a commit in PatchRanger/yii
@PatchRanger PatchRanger Fix #1588: Added aliases for 'through' tables
Extended version of commit, provided by @acorncom.
3742d7f
@PatchRanger

Looks like I've debugged and fixed it.
See my piece of change - it is not very big, but it solves the issue for me. Now all aliases work as expected.
Here is related pull-request: #3850.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.