Doctrine with SQL View

[chathura86]

Hi,

here is the Docrrin class i have generated for the following SQL View

CREATE VIEW `STREAMLINE`.`ViewPurchaseOrderProgress` AS
(SELECT
	P.StockPurchaseOrderId, P.Item, I.Name, P.Ordered, IFNULL(0, G.Received) AS Received,
	(P.Ordered - IFNULL(0, G.Received) + IFNULL(0, R.Returned)) AS Pending
FROM
	ViewStockPurchaseOrderSummary P
LEFT JOIN
	StockItem I
ON (I.StockItemId = P.Item)
LEFT JOIN
	ViewStockGrnSummary G
ON (G.PurchaseOrder = P.StockPurchaseOrderId AND G.Item = P.Item)
LEFT JOIN
	ViewStockDamageReturnSummary R
ON (R.PurchaseOrder = P.StockPurchaseOrderId AND R.Item = P.Item))

abstract class BaseViewPurchaseOrderProgress extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->setTableName('ViewPurchaseOrderProgress');
        $this->hasColumn('StockPurchaseOrderId', 'integer', 4, array(
             'type' => 'integer',
             'length' => 4,
             'fixed' => false,
             'unsigned' => false,
             'primary' => false,
             'default' => '0',
             'notnull' => true,
             'autoincrement' => false,
             ));
        $this->hasColumn('Item', 'integer', 4, array(
             'type' => 'integer',
             'length' => 4,
             'fixed' => false,
             'unsigned' => false,
             'primary' => false,
             'notnull' => true,
             'autoincrement' => false,
             ));
        $this->hasColumn('Name', 'string', 128, array(
             'type' => 'string',
             'length' => 128,
             'fixed' => false,
             'unsigned' => false,
             'primary' => false,
             'notnull' => false,
             'autoincrement' => false,
             ));
        $this->hasColumn('Ordered', 'decimal', 32, array(
             'type' => 'decimal',
             'length' => 32,
             'fixed' => false,
             'unsigned' => false,
             'primary' => false,
             'notnull' => false,
             'autoincrement' => false,
             ));
        $this->hasColumn('Received', 'decimal', 32, array(
             'type' => 'decimal',
             'length' => 32,
             'fixed' => false,
             'unsigned' => false,
             'primary' => false,
             'notnull' => false,
             'autoincrement' => false,
             ));
        $this->hasColumn('Pending', 'decimal', 34, array(
             'type' => 'decimal',
             'length' => 34,
             'fixed' => false,
             'unsigned' => false,
             'primary' => false,
             'notnull' => false,
             'autoincrement' => false,
             ));
    }

    public function setUp()
    {
        parent::setUp();
        
    }
}

I used the folllowing code to get the data

$poItems = Doctrine_Query::create()
						->select('Item, Name, Ordered, Received, Pending')
						->from('ViewPurchaseOrderProgress')
						->where('StockPurchaseOrderId = ?', 1)
						->fetchArray();

but this triggers the following error

Column not found: 1054 Unknown column 'v.id' in 'field list'

and it is true that this table does not have any column with the name id

please help me to solve this as im new to doctrine

Regards
Chathura Bamunusinghe

[dlite922]

Take a look at the "Doctrine_Recor d" class, I'm sure that has "id" in it.

I don't know what Doctrine is, but if it's a framework then I think some frameworks require all their tables to have a field called id.



Dan