Yii joining two table using relations in model

Question

Hi I have these two tables that I want to join using relations in Yii, The problem is Im having a hard time figuring out how Yii relation works.

picturepost
    id
    title
    link_stat_id

linkstat
    id
    link
    post_count

I also have a working SQL query. This is the query I want my relation to result when I search when I want to get picturepost

SELECT picturepost.id, picturepost.title,linkstat.post_count   
FROM picturepost
RIGHT JOIN linkstat
ON picturepost.link_stat_id=linkstat.link;

I want something like this when I search for a post.

$post = PicturePost::model() -> findByPk($id);
echo $post->linkCount;

Here's my table for extra info:

CREATE TABLE IF NOT EXISTS `picturepost` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `title` text COLLATE utf8_unicode_ci DEFAULT NULL,
     `link_stat_id` char(64) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM;
CREATE TABLE IF NOT EXISTS `linkstat` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `link` char(64) COLLATE utf8_unicode_ci NOT NULL,
  `post_count` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `post_count` (`post_count`),
  KEY `link_stat_id` (`link`)
) ENGINE=InnoDB;

Thanks in advance I hope I explained it clearly.

score 0 · Answer 1 · answered Nov 30 '13 at 19:02

There are a few tutorial regarding this, and I won't repeat them, but urge you to check them out.

The easiest starting point will be to create your foreign key constraints in the database, then use the Gii tool to generate the code for the model, in this case for the table picturepost.

This should result in a class Picturepost with a method relations(),

class Picturepost extends  {

public function relations()
{
   return array(
     'picturepost_linkstats' => array(self::HAS_MANY, 
                                 'linkstat', 'link_stat_id'),
   );
}

This links the 2 tables using the *link_stat_id* field as the foreign key (to the primary key of the linked table).

When you are querying the table picturepost, you can automatically pull in the linkstat records.

// Get the picturepost entry
$picturepost = PicturePost::model()->findByPk(1);

// picturepost_linkstats is the relationship name
$linkstats_records =  $picturepost->picturepost_linkstats;

Still confused on how connect it using foreign key. **link_stat_id** should be primary key? — tiltdown, Nov 30 '13 at 20:21
picturepost.link_stat_id is a FOREIGN KEY, which maps tolinkstat.id, which is a PRIMARY KEY. What exactly are you confused about? I provided the code to create the linkages, and query the parent and child records. Is there anything more you were expecting? — crafter, Nov 30 '13 at 20:26
picturepost.link_stat_id = linkstat.link while linkstat.id is an auto_increment column is my table structure wrong? — tiltdown, Nov 30 '13 at 20:34

score 0 · Answer 2 · answered Dec 01 '13 at 00:43

public function relations()
{
   return array(
     'linkstat' => array(self::HAS_ONE, 'Linkstat', array('link_stat_id'=>'link')),
   );
}

Yii joining two table using relations in model

2 Answers2