Hi.
I'm trying to migrate from MySQL to pgsql.
Code:
$this->select()
->setIntegrityCheck(false)
->from('Feed', array('FeedId', 'FeedName'))
->joinLeft(array(
'LogDate' => new Zend_Db_Expr('(' .
$this->select()
->setIntegrityCheck(false)
->from('Log', array('FeedId AS LogDateFeedId', 'MAX("Log"."Dateline") AS
MaxDateline'))
->group('FeedId')
.
')')), 'Feed.FeedId = LogDateFeedId')
->joinLeft('Log', 'LogDateFeedId = Log.FeedId AND LogDate.MaxDateline =
Log.Dateline', array('Dateline', 'Added', 'Removed'))
->join('Category', 'Category.CategoryId = Feed.CategoryId',
'CategoryName')
->joinLeft('Content', 'Content.FeedId = Feed.FeedId',
'COUNT("Content"."ContentId") AS ContentCount')
->group('Feed.FeedId, Feed.FeedName, LogDate.LogDateFeedId,
LogDate.MaxDateline, Log.Dateline, Log.Added, Log.Removed,
Category.CategoryName');
gives
SELECT "Feed"."FeedId", "Feed"."FeedName", "LogDate".*, "Log"."Dateline",
"Log"."Added", "Log"."Removed", "Category"."CategoryName",
COUNT("Content"."ContentId") AS "ContentCount" FROM "Feed" LEFT JOIN (SELECT
"Log"."FeedId" AS "LogDateFeedId", MAX("Log"."Dateline") AS "MaxDateline"
FROM "Log" GROUP BY "FeedId") AS "LogDate" ON Feed.FeedId = LogDateFeedId
LEFT JOIN "Log" ON LogDateFeedId = Log.FeedId AND LogDate.MaxDateline =
Log.Dateline INNER JOIN "Category" ON Category.CategoryId = Feed.CategoryId
LEFT JOIN "Content" ON Content.FeedId = Feed.FeedId GROUP BY "Feed"."FeedId,
Feed"."FeedName, LogDate"."LogDateFeedId, LogDate"."MaxDateline,
Log"."Dateline, Log"."Added, Log"."Removed, Category"."CategoryName"
identifiers under MAX, COUNT... are not quoted (added manually in example
above), as well as well as join conditions. Which leads to :
exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42P01]:
Undefined table: 7 ERROR: missing FROM-clause entry for table "feed"
LINE 2: ...Id" FROM "Log" GROUP BY "FeedId") AS "LogDate" ON Feed.FeedI...
^' in ...
This use to work with MySQL.
Am I doing something wrong in my code?
Regards,
Saša Stamenković
