Sql Query Max Value

Yii 1.1 General Discussion
1

In one of my CgridViews I need to put a column which shows the latest week (number and date) on which the person was active.

Now , I tried to find Yii’s tools for sql queries , but there weren’t the MAX option there(or did I miss them?) , thus I used a pure SQL:


public function latestWeek() //this function is for Person

	{			

		$datalogin=//connection to DB is working well

		$sql ="SELECT w.number,MAX(w.start_date)

		FROM tbl_person_week t, tbl_week w

		WHERE t.person_id=$this->id AND t.week_id=w.id"; 

		

	$query = mysqli_query($datalogin, $sql);

	return $query;

	}

Problem with this is that the query is fine (I checked it on the server) but I get the error that an object is being sent back , instead of a string , but I found no working solution for this conversion.

Any idea how can I do it with SQL or Yii’s tools?

Thanks,

Mark.

2

Posed again by mistake

3

$sql ="SELECT w.number,MAX(w.start_date) as max_date

4

Didn’t make a difference (I still should return $query , shouldn’t I?)

5

they may be no MAX function in Yii querybuilder, but you can still run any custom query with plain DAO interfaces without need to setup your own database link…

just use:




$command = Yii::app()->db->createCommand( 'SELECT w.number,MAX(w.start_date) FROM tbl_person_week t, tbl_week w WHERE t.person_id=:person_id AND t.week_id=w.id' );


$result = $command->query( array( ':person_id'=>$this->id ) );

…or any other query* function from CDbCommand like queryRow, queryAll, queryColumn, etc (http://www.yiiframework.com/doc/api/1.1/CDbCommand#query-detail)

6 

they may be no MAX function in Yii querybuilder, but you can still run any custom query with plain DAO interfaces without need to setup your own database link...

Indeed. Thanks.

Any ideas about string conversion?

7

what do you mean by that?

8

This is my problem:

9

Well… query returns resultset object, so called datareader (if you do not use queryScalar, queyRow, etc), so you have to iterate the results:

http://www.yiiframework.com/doc/guide/1.1/en/database.dao#fetching-query-results




$dataReader = $command->query();

foreach( $dataReader as $row ) {

   echo $row['column'];

   ...

}

or




// retrieving all rows at once in a single array

$rows = $dataReader->readAll();


echo $rows[0]['column'];
10

I solved it in the next way:


public function latestWeek() //this function is for Person

	{			

		$datalogin=mysqli_connect("192.168.1.137", "volapp", "t2ru3s1Lv","vol_app");

		$sql ="SELECT w.start_date

		FROM tbl_person_week t, tbl_week w

		WHERE t.person_id=$this->id AND t.week_id=w.id

		ORDER BY w.start_date DESC";

		

	$query = mysqli_query($datalogin, $sql);

	$numrows = mysqli_fetch_array($query);

	$n=$numrows[0];

	return $n;

	}

Ordering by DESC and taking the first value fom the array.

HOWEVER, I got another question now:

In addition to the start_date , I want to show another Week’s attribute - number , in the same column.

But if I do


SELECT w.start_date, w.number

, only the first attribute is being showed. . .

How can I show both in one column?

11

OTMH something like:


SELECT CONCAT(w.start_date, w.number) as w.both

maybe :)