We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Query Build Join Fails

This is only returning one table, with no joins. I've tried doing the namespaced.* thing for each table, but that doesn't work

    $namespaced_table = 'Plc\\'.ucfirst('reports').'\\'.\Phalcon\Text::camelize('report_tools');
    $namespaced_table1 = 'Plc\\'.ucfirst('reports').'\\'.\Phalcon\Text::camelize('tools');
    $namespaced_table2 = 'Plc\\'.ucfirst('reports').'\\'.\Phalcon\Text::camelize('tool_content');
    $namespaced_table3 = 'Plc\\'.ucfirst('reports').'\\'.\Phalcon\Text::camelize('tool_scripts');

    $query_object = $this->modelsManager->createBuilder()
    ->from($namespaced_table)
    ->leftJoin($namespaced_table1, $namespaced_table.'.tool_id=t.id', 't')
    ->leftJoin($namespaced_table2, 'tc.tool_id=t.id', 'tc')
    ->leftJoin($namespaced_table3, 'ts.tool_id=t.id', 'ts')
    ->where($namespaced_table.'.report_id = 209');

SQL output:

SELECT `report_tools` FROM `reports_alpha`.`report_tools` LEFT JOIN `reports_alpha`.`tools` AS `t` ON `report_tools`.`tool_id` = `t`.`id`  LEFT JOIN    `reports_alpha`.`tool_content` AS `tc` ON `tc`.`tool_id` = `t`.`id`  LEFT JOIN `reports_alpha`.`tool_scripts` AS `ts` ON `ts`.`tool_id` = `t`.`id`  WHERE `report_tools`.`report_id` = 209

report_tools replaced with * is valid SQL

When I alter to (columns, multi-table *)

    $query_object = $this->modelsManager->createBuilder()
    ->from($namespaced_table)
    ->columns(array($namespaced_table.'.*',$namespaced_table2.'.*'))
    ->leftJoin($namespaced_table1, $namespaced_table.'.tool_id=t.id', 't')
    ->leftJoin($namespaced_table2, 'tc.tool_id=t.id', 'tc')
    ->leftJoin($namespaced_table3, 'ts.tool_id=t.id', 'ts')
    ->where($namespaced_table.'.report_id = 209');

I get

Fatal error: Uncaught exception 'Phalcon\Mvc\Model\Exception' with message 'Unknown model or alias 'Plc\Reports\Tools' (2), when preparing: SELECT...

I did a successful stand alone SELECT on 'Plc\Reports\Tools', so I know that the model etc is good.

Suggestions?



98.9k

I think this is fixed in 1.3.2, could you try please with that version?

edited Apr '14

We're having trouble with the install of that branch:

git clone --depth=1 git://github.com/phalcon/cphalcon.git
git checkout -b 1.3.2
cd build
sudo install
apache restart

phpinfo() still reports 1.3.1 after going through the compile process, and restart

Phalcon Framework   enabled
Phalcon Version 1.3.1

Directive   Local Value Master Value
phalcon.db.escape_identifiers   On  On
phalcon.orm.column_renaming On  On
phalcon.orm.enable_literals On  On
phalcon.orm.events  On  On
phalcon.orm.exception_on_failed_save    Off Off
phalcon.orm.not_null_validations    On  On
phalcon.orm.virtual_foreign_keys    On  On
phalcon.register_psr3_classes   Off Off
edited Apr '14

The install issue was taken care of by

git pull origin 1.3.2

But there is still a join issue: the current test errors with:

Plc\Reports\ToolContent , tc.tool_id=t.id , tc , Plc\Reports\Tools , Plc\Reports\ReportTools.tool_id=t.id , t , Plc\Reports\ToolScripts , ts.tool_id=t.id , ts , report_id = 209 ,

Fatal error: Uncaught exception 'Phalcon\Mvc\Model\Exception' with message 'Unknown model or alias 'Plc\Reports\ReportTools' (1), when preparing: SELECT id, report_id, tool_id FROM [Plc\Reports\Tools] LEFT JOIN [Plc\Reports\ToolContent] AS [tc] ON tc.tool_id=t.id LEFT JOIN [Plc\Reports\Tools] AS [t] ON Plc\Reports\ReportTools.tool_id=t.id LEFT JOIN [Plc\Reports\ToolScripts] AS [ts] ON ts.tool_id=t.id WHERE report_id = 209' in /usr/local/apache2/htdocs/jh-plci/apps/backend/controllers/VroutingController.php:93

Stack trace:

0 [internal function]: Phalcon\Mvc\Model\Query->_getQualified(Array)

1 [internal function]: Phalcon\Mvc\Model\Query->_getExpression(Array, true)

2 [internal function]: Phalcon\Mvc\Model\Query->_getExpression(Array)

3 [internal function]: Phalcon\Mvc\Model\Query->_getJoins(Array)

4 [internal function]: Phalcon\Mvc\Model\Query->_prepareSelect()

5 [internal function]: Phalcon\Mvc\Model\Query->parse()

6 /usr/local/apache2/htdocs/jh-plci/apps/backend/controllers/VroutingController.php(93): Phalcon\Mvc\ in /usr/local/apache2/htdocs/jh-plci/apps/backend/controllers/VroutingController.php on line 93

Finally got things resolved.

Quirk discovered - tables had to be aliased in order to be found. Via addFrom etc

I have the same problem on 1.3.0

How did you aliased table names?



60

I have the same problem on 1.3.4



60

could you share the solution for us?

i have this problem too in 1.3.4

$shows = $this->modelsManager->createBuilder()->from('Model\Stat\Content\Show')
            ->where('content_id = :contentId:', ['contentId' => $content->getId()])
            ->join('Model\Device\Device', 'Model\Device\Device.id = Model\Stat\Content\Show.device_id', 'device');
            ->orderBy('date_start DESC')
            ->getQuery()
            ->execute();

Phalcon\Mvc\Model\Exception: Unknown model or alias 'Model\Device\Device' (1), when preparing: SELECT [Model\Stat\Content\Show].* FROM [Model\Stat\Content\Show] JOIN [Model\Device\Device] AS [device] ON Model\Device\Device.id = Model\Stat\Content\Show.device_id WHERE content_id = :contentId: ORDER BY date_start DESC