yii2 : can't get data from two table with active record

Question

I want get data from two table comments,users.
comments have hasOne relation with users

public function getUser()
    {
        return $this->hasOne(Users::className(), ['user_id' => 'user_id']);
    }

i want get comments.comment_id,comments.comment_content,comments.user_id from comments table and uses.user_name , users.user_display_name from users table to use in gridview widget.

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 get comments field but i can't get users.user_name,users.user_display_name from database.

How should I do it?

note: user table in database is users but when i create model with Gii,relation method declare 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;

$res is model object(i think) and i use `foreach($res as $comment)`, then get value with field name like : `$comment->comment_content`. widget use it automatically. — Death Programmer, Nov 05 '14 at 15:17

score 4 · Accepted Answer · answered Nov 05 '14 at 15:46

4

Not sure how you're displaying or testing if the query returned any data but you should read up on relations 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 may not appear in things like print_r or var_dump

answered Nov 05 '14 at 15:46

Alex

2,102
1
13
16

1

i use xdebug and `var_dump` to see result and `var_dump` has limit diplay result(i fix it). – Death Programmer Nov 05 '14 at 15:56

score 2 · Answer 2 · edited May 23 '17 at 12:16

2

I recommend renaming your tables to comment and user. Read here: Table Naming Dilemma: Singular vs. Plural Names why you should use singular names.

The reason Gii generates getUser and not getUsers is because of the hasOne relationship. It's getting one -user- and not multiple -users-

Are you sure the relationship definition is correct? Is user_id the PK in the users table and the FK in the comments table? And are you sure there is correct data in the DB?

edited May 23 '17 at 12:16

Community

1
1

answered Nov 05 '14 at 14:55

Jap Mul

17,398
5
55
66

1

update tables query, i use navicat to enter data in db and i'm sure there is correct data in DB – Death Programmer Nov 05 '14 at 15:03
1

And if you do a `print_r($comment)` in your foreach, do you see the user_name and user_user_display_name? – Jap Mul Nov 05 '14 at 15:21
1

i use @Alex help in above, and it's work. use `$comment->user->user_name`. – Death Programmer Nov 05 '14 at 15:31

yii2 : can't get data from two table with active record

2 Answers2