joinSub bug with table prefix

[kndol]

• Laravel Version: 5.8.15
• PHP Version: 7.3.1p1
• Database Driver & Version: mariadb 10.1.13

Description:

joinSub, leftJoinSub, and rightJoinSub functions have a bug with a table prefix.

Steps To Reproduce:

Set the table prefix as 'prefix_'.

        $selectedQuest = DB::table('quest_items')
            ->select('quest_item_id', 'content_no',
                'content_type', 'subject', 'content')
            ->where('quest_set_id', $questSetId)
            ->orderBy('quest_item_id');
       
        $questPlayerData = DB::table('quest_play_items')
            ->leftJoinSub($selectedQuest, 'selectedQuest', function ($join)
            {
                $join->on('quest_play_items.quest_item_id', 'selectedQuest.quest_item_id');
            })
            ->where('quest_play_items.quest_play_set_id', $questPlaySetId)->get();

Then error has occurred:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'prefix_selectedQuest.quest_item_id' in 'on clause' (SQL: select * from prefix_quest_play_items left join (select quest_item_id, content_no, content_type, subject, content from prefix_quest_items where quest_set_id = 1 order by quest_item_id asc) as selectedQuest on prefix_quest_play_items.quest_item_id = prefix_selectedQuest.quest_item_id where prefix_quest_play_items.quest_play_set_id = 28) {"exception":"[object] (Illuminate\Database\QueryException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'prefix_selectedQuest.quest_item_id' in 'on clause' (SQL: select * from prefix_quest_play_items left join (select quest_item_id, content_no, content_type, subject, content from prefix_quest_items where quest_set_id = 1 order by quest_item_id asc) as selectedQuest on prefix_quest_play_items.quest_item_id = prefix_selectedQuest.quest_item_id where prefix_quest_play_items.quest_play_set_id = 28) at /vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDOException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'prefix_selectedQuest.quest_item_id' in 'on clause' at /vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:63, PDOException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'prefix_selectedQuest.quest_item_id' in 'on clause' at /vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:61)

So we need to explicitly add a prefix to the alias to make the function work properly:

        $selectedQuest = DB::table('quest_items')
            ->select('quest_item_id', 'content_no',
                'content_type', 'subject', 'content')
            ->where('quest_set_id', $questSetId)
            ->orderBy('quest_item_id');
       
        $questPlayerData = DB::table('quest_play_items')
            ->leftJoinSub($selectedQuest, '**prefix_**selectedQuest', function ($join)
            {
                $join->on('quest_play_items.quest_item_id', 'selectedQuest.quest_item_id');
            })
            ->where('quest_play_items.quest_play_set_id', $questPlaySetId)->get();

[staudenmeir]

I'll fix it.

[driesvints]

@staudenmeir thanks for looking into this 👍