How the LIMIT should work

I have 2 AR:

Contract [CID, …, DateOf]

ContractLines [CLID, CID, ImageFile, SortOrder]

Of course one Contract can have many ContractLines (HAS_MANY relation)

How I can retrieve from DB last 10 Contracts with ONLY ONE ContractLine?

ONLY ONE means - one record from ContractLines with minimal SortOrder value for each Contract;

Model:

public function relations()

{

        return array('lines' => array(self::HAS_MANY, 'ContractLines', 'CID'));

}


Controller:

public function actionGet()

{

    $criteria = new CDbCriteria;

    $criteria->limit = 10;

    $criteria->order = 'DateOf DESC';

    $contracts = Contracts::model()->with("lines")->together()->findAll($criteria);

}

It returns 10 records, but not 10 contracts (because each contract has many contract lines).

What should I do?

Can anybody help?

If minimal SortOrder for each Contract Line is always 1, then you can just edit $criteria->order:

$criteria->order = 'SortOrder ASC, DateOf DESC';

This query also won’t work if you have less than 10 contracts.

True SQL query which will solve your problem will look like:

SELECT Contract.*, ContractLines.CLID, ContractLines.ImageFile FROM Contract

INNER JOIN ContractLines ON Contract.CID = ContractLines.CID

INNER JOIN

    (SELECT CID, MIN(SortOrder) AS MinSortOrder FROM ContractLines GROUP BY CID) AS t1

    ON (ContractLines.CID = t1.CID AND ContractLines.SortOrder = t1.MinSortOrder)

ORDER BY DateOf DESC

LIMIT 10

Here we add an additional join with a table of pairs (CID, MinSortOrder) to leave only contracts having MinSortOrder.

Now it’s a little problem to write this query using CDbCriteria

No problem with plain SQL, thanks

Here is my version:

SELECT *, 

(SELECT FileName FROM ContractLines AS l WHERE l.CID=c.CID ORDER BY SortOrder LIMIT 1) AS FileName 

FROM Contracts AS c 

WHERE c.Status=1 

ORDER BY c.DateOf DESC LIMIT 10

Question was about same things in ActiveRecord mode

if you will not perform search or order on the FileName, you can avoid to select in the main query, and simply write a getter method in the model for get the first contract.

Something like:

public function getFirstFileName()

{

   return ContractLines::model()->find(array('condition'=>'CID='.$this->CID, 'orderBy'=>'SortOrder'));

}

The find will authomatically add the limit 1.

Another option is to use a criteria for the main selection:

$criteria= new CDbCriteria;

$criteria->select="*, (SELECT FileName FROM ContractLines AS l WHERE l.CID=c.CID ORDER BY SortOrder LIMIT 1) AS FileName ";

$contracts = Contracts::model()->findAll($criteria);

This should work, you have just to add a public property ‘FileName’ in model for get the result