Yii Framework Forum: Need Help For Complex Has_Many Relation Criteria - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Need Help For Complex Has_Many Relation Criteria Rate Topic: ***** 1 Votes

#1 User is offline   p30tec 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 78
  • Joined: 03-March 13

Posted 23 May 2013 - 08:54 AM

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
0

#2 User is offline   p30tec 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 78
  • Joined: 03-March 13

Posted 23 May 2013 - 09:19 AM

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
0

#3 User is offline   p30tec 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 78
  • Joined: 03-March 13

Posted 26 May 2013 - 04:21 AM

Help me Please
0

#4 User is offline   p30tec 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 78
  • Joined: 03-March 13

Posted 26 May 2013 - 06:42 AM

Hello
I have 2 Table

Tbl_Post

Quote

ID
Title
Content


Tbl_Log

Quote

ID
Status
Log_Time
Post_ID


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 ?
Show all Post that it's newest status is 1 ??
and
and easy access to recent status? for example $post->status;

Thanks
0

#5 User is offline   diaa 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 07-April 13

Posted 26 May 2013 - 07:06 AM

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);
0

#6 User is offline   p30tec 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 78
  • Joined: 03-March 13

Posted 26 May 2013 - 07:48 AM

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 Show all Post that it's newest status is 1 ??
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
0

#7 User is offline   p30tec 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 78
  • Joined: 03-March 13

Posted 26 May 2013 - 08:54 AM

Emergency help needed , plz
0

#8 User is offline   p30tec 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 78
  • Joined: 03-March 13

Posted 26 May 2013 - 10:53 AM

Please reply :(
0

#9 User is offline   p30tec 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 78
  • Joined: 03-March 13

Posted 26 May 2013 - 12:15 PM

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

#10 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,166
  • Joined: 05-January 12
  • Location:Morocco

Posted 26 May 2013 - 12:23 PM

Hello

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

#11 User is offline   p30tec 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 78
  • Joined: 03-March 13

Posted 26 May 2013 - 12:51 PM

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

Quote

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


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
0

#12 User is offline   p30tec 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 78
  • Joined: 03-March 13

Posted 26 May 2013 - 01:30 PM

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`


0

#13 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,062
  • Joined: 16-February 11
  • Location:Japan

Posted 26 May 2013 - 06:11 PM

/* Merged 2 threads ("HAS_MANY" and "CDbCriteria") into this one. Please don't scatter the same issue accross different threads. */
0

#14 User is offline   p30tec 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 78
  • Joined: 03-March 13

Posted 27 May 2013 - 12:17 AM

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
0

#15 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,062
  • Joined: 16-February 11
  • Location:Japan

Posted 27 May 2013 - 05:27 AM

View Postp30tec, on 26 May 2013 - 12:51 PM, said:

<?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',
		),
		..............
	);
}
?>

<?
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;
}
?>



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.
0

#16 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,062
  • Joined: 16-February 11
  • Location:Japan

Posted 27 May 2013 - 05:52 AM

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.
1

#17 User is offline   p30tec 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 78
  • Joined: 03-March 13

Posted 27 May 2013 - 07:33 AM

OK , thanks for you reply
I find better solution before your reply that support 'with'

Thank you softark for you HELP , thanks :lol:
0

#18 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,062
  • Joined: 16-February 11
  • Location:Japan

Posted 27 May 2013 - 08:52 AM

View Postp30tec, on 27 May 2013 - 07:33 AM, said:

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?
0

#19 User is offline   p30tec 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 78
  • Joined: 03-March 13

Posted 28 May 2013 - 01:51 AM

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

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users