Need Help For Complex Has_Many Relation Criteria

Hi

How can I return the newest Post of the specific Author in Yii Blog Demo ??

in the User model we have




	/**

	 * @return array relational rules.

	 */

	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'posts' => array(self::HAS_MANY, 'Post', 'author_id'),

		);

	}



I dont want Hard coded m I just need a simple way because I need to access newest record many times

thanks a lot

I want to do something like this but I don’t know how do this ?




/**

	 * @return array relational rules.

	 */

	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'posts' => array(self::HAS_MANY, 'Post', 'author_id'),

			'newestPost' => array(self::HAS_One, 'Post', 'author_id',array('order'=>'Post.create_time DESC',)),

		);

	}



Usage :




<?php

$model = User::model()->findByPk(1);

echo $model->newestPost->title; // Must echo title of Newst post of User 1

?>



if it is not possible tell me best way

Help me Please

Hello

I have 2 Table

Tbl_Post

Tbl_Log

Database is Mysql , Storage Engine is InnoDB and Tbl_Log.Post_ID is a Foreign Key

When the status of post changed , a new record add to Tbl_Log that keep new and old status and log times

How can do this ?

[color="#FF0000"]Show all Post that it’s newest status is 1 ??[/color]

and

[color="#FF0000"]and easy access to recent status? for example $post->status;[/color]

Thanks

if I understood what u want right this the answer

assuming $postId is the Id of the Post u want to change its status

$log = new Log ;

$log->Status = ‘whatever’;

$log->Post_Id = Post::model()->findbyPk( $postId );

$log->logTime = date('Y-m-d H:i:s);

$log->save(True);

Diaa , No no , I dinr want add new record to Log

Here is an example

Tbl_Post




 ID = 1

 Title = 'Post #1'

 Content = 'Anything'


 ID = 2

 Title = 'Post #2'

 Content = 'Anything'




 ID = 3

 Title = 'Post #3'

 Content = 'Anything'




Tbl_Log


 

 ID = 1

 Status = 0

 Log_Time = 2013-01-01

 Post_ID = 1


 ID = 2

 Status = 1

 Log_Time = 2013-01-02

 Post_ID = 1


 ID = 3

 Status = 2

 Log_Time = 2013-01-03

 Post_ID = 1


 ID = 4

 Status = 0

 Log_Time = 2013-01-04

 Post_ID = 2


 ID = 5

 Status = 5

 Log_Time = 2013-01-06

 Post_ID = 2


 ID = 6

 Status = 4

 Log_Time = 2013-01-10

 Post_ID = 2




I want to [color="#FF0000"]Show all Post that it’s newest status is 1 ??[/color]

Now if you see log and sort it DESC by Log_Time




Status of Post#1 = 2 (last log in 2013-01-03) from log#3

Status of Post#2 = 4 (last log in 2013-01-10) from log#6

Status of Post#3 = null or empty



So I want do this

1- when use echo $post->status for post#1 yii echo 2

2- Find All Post By Status

Emergency help needed , plz

Please reply :(

I need your help , Please :unsure: ::)

Hello

Could you explain your needs in another way? I tried but I could not understand what you’re trying to achieve…

Thanks for your reply and sorry for my bad English skill

As you can see For One Post I have Many Logs

but I need usually last log and some times all logs so I define 2 relation for this , Has_One and Has_Many




<?php

/**

 * @return array relational rules.

 */

public function relations()

{

	return array(

		..............

		'postLogs' => array(self::HAS_MANY, 'PostLog', 'post_id'),

		'neweststatus'=> array(self::HAS_ONE, 'PostLog', 'post_id',

			'order'=>'neweststatus.regtime DESC',

		),

		..............

	);

}

?>



Now If I want access to last Status of Post I can do this :




<?php

$post->neweststatus->status;//Status ID

$post->neweststatus->log_time;//When Status changed to this status

?>



Now my problem is ::

Only posts that $post->neweststatus->status = 1 and $post->neweststatus->status=2 can be display for end user and other post must hide for end users so I want to create a Named Scope like this




<?

public function userVisible()//Only Visible Post for end-users

	{

		$criteria = new CDbCriteria;

		$criteria->with = array('neweststatus');

		$criteria->addColumnCondition(

			array(

				'neweststatus.status' => 1,

				'neweststatus.status' => 2,

			),

			'OR'

		);

		$this->getDbCriteria()->mergeWith($criteria);

		return $this;

}

?>



but is dont works properly becacuse in this example

the newest status of Post#1 is 2 but in Tbl_Log post#1 has 0 and 1 as old status and when I use




<?php

$posts=Post::model()->userVisible()->findAll();

?>



$posts contain Post#1 because of old staus , I try to GROUP BY Post_ID in my $criteria but I cannot find solution

Hello

Can you tell me which CDbCriteria can generate this query ?? or something like this

I dont know How must define CDbCriteria ?





SELECT * 

FROM (

SELECT * 

FROM `tbl_log` 

ORDER BY logtime DESC 

) `t1` 

GROUP BY `stuff_id`




[color="#006400"]/* Merged 2 threads (“HAS_MANY” and “CDbCriteria”) into this one. Please don’t scatter the same issue accross different threads. */[/color]

Who can help me ?!!?

I know Experts know how to Convert




SELECT * 

FROM (

SELECT * 

FROM `tbl_log` 

ORDER BY logtime DESC 

) `t1` 

GROUP BY `stuff_id`



To a CDbCriteria for using userVisible as a named scope

Yii admins , Developers and Experts I need Your HELP , just a quick reply

So, you say ‘neweststatus’ relation works fine for lazy loading:


$post->neweststatus->status;

but it doesn’t work as expected when you use it for filtering … am I right?

I think it’s the limitation of a HAS_ONE relation that uses ‘order’ to get the first record. Probably we have do ‘select from select …’ in a very low-level fashion … as you are trying to do.

Would you please try this?




<?

public function userVisible()//Only Visible Post for end-users

{

	$criteria = new CDbCriteria;

	$criteria->select = array(

		'*',

		'(SELECT status from (

			SELECT * from `tbl_log`

			WHERE `tbl_log`.`Post_ID` = `t`.`id`

			ORDER BY `tbl_log`.`Log_Time` DESC

			LIMIT 1)

		) as status',

		);

	$criteria->addColumnCondition(

		array(

			'status' => 1,

			'status' => 2,

		),

		'OR'

	);

	$this->getDbCriteria()->mergeWith($criteria);

	return $this;

}

?>



Not tested. Sorry if it doesn’t work.

As I suggested in the previous post, I have confirmed that A HAS_ONE relation using ORDER has a limitation. You can use it for the lazy loading scenario. It works fine for it. But you can not use it for ‘with’, because ‘ORDER’ clause is not applied to the related table only but to the whole virtual table that is the result of joining.

OK , thanks for you reply

I find better solution before your reply that support ‘with’

Thank you softark for you HELP , thanks :lol:

That’s fine. :)

Could you share your solution with us?

Sorry , it is a little complex and I cannot explain :mellow: