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

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?



5.4k
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


5.4k
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



5.4k

Finally got things resolved.

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



2.0k

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?



3.6k

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