Yii2: unable to get data from two tables with active record
I want to get data from two tables comments
, users
. comments
has to do hasOne
withusers
public function getUser()
{
return $this->hasOne(Users::className(), ['user_id' => 'user_id']);
}
I want to get comments.comment_id
, comments.comment_content
, comments.user_id
from the table comments
and uses.user_name
, users.user_display_name
of users
for the use of widgets gridview
.
I use
$res = Comments::find()
->select([
'comments.comment_id',
'comments.comment_content',
'comments.user_id',
'users.user_id',
'users.user_display_name',
'users.user_name',
])
->innerJoinWith('user')
->all();
this code gets the field comments
, but I cannot get users.user_name
, users.user_display_name
from the database.
How should I do it?
note: the user table is in the database users
, but when I create the model with Gii, the relationship method declares as getUser()
, I don't know why.
update 1:
comments
table:
DROP TABLE IF EXISTS `comments`;
CREATE TABLE `comments` (
`comment_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`comment_content` text NOT NULL,
`comment_approved` enum('no','yes') NOT NULL DEFAULT 'no',
`comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`comment_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
`sms_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`comment_id`),
KEY `fk_comment_sms_id` (`sms_id`),
KEY `fk_commetn_user_id` (`user_id`),
CONSTRAINT `fk_comment_sms_id` FOREIGN KEY (`sms_id`) REFERENCES `sms` (`sms_id`),
CONSTRAINT `fk_commetn_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
users
table:
CREATE TABLE `users` (
`user_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_name` varchar(60) NOT NULL DEFAULT '',
`user_pass` varchar(64) NOT NULL DEFAULT '',
`user_level` int(11) NOT NULL DEFAULT '1',
`user_email` varchar(100) NOT NULL DEFAULT '',
`user_display_name` varchar(250) NOT NULL DEFAULT '',
`user_phone_number` varchar(11) NOT NULL,
`user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`user_activation_key` varchar(60) NOT NULL,
`user_status` enum('active','deactive','delete') NOT NULL DEFAULT 'deactive',
PRIMARY KEY (`user_id`),
UNIQUE KEY `u_user_sign` (`user_name`) USING BTREE,
UNIQUE KEY `u_user_email` (`user_email`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;
source to share
Not sure how you are showing or checking if the query returned any data, but you should be reading relationships in Yii.
Working with Relational Data
Lazy and Eager Loading
With lazy loading, the data doesn't exist until you try to access it, so it might not show up in things like print_r
orvar_dump
source to share
I recommend renaming your tables to comment
and user
. Read here: Dilemma of table names: singular versus plural names , why you should use unique names.
The reason Gii generates getUser
and not getUsers
is because of the relationship hasOne
. It gets one -user-, not multiple -ser s -
Are you sure the definition of relationship is correct? Is user_id
PK in the users table and FK in the comments table? And are you sure there is correct data in the database?
source to share