I have the following database setup for my status posts. For each post, users can like the post, comment on the post or can even be tagged in the original post by the author.
I'm trying to setup my Resourceful controller 'Post' to bring back all the data via JSON object but I can't properly find the comment, likes or tags usernames. I'm using Sentry 2 for auth if that makes a difference.
Here's the database setup:
CREATE TABLE Users (
id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(30),
last_name VARCHAR(30),
many more...
);
CREATE TABLE Posts (
postID INT NOT NULL AUTO_INCREMENT,
caption VARCHAR(200),
description VARCHAR(200),
fromID INT(10) UNSIGNED NOT NULL,
toID INT(10) UNSIGNED NOT NULL,
icon VARCHAR(200),
link VARCHAR(200),
message TEXT,
storyType INT,
type ENUM ('LINK', 'PHOTO', 'STATUSUPDATE', 'VIDEO' ),
createdTime DATE,
PRIMARY KEY (postID),
FOREIGN KEY (fromID) REFERENCES users (id),
FOREIGN KEY (toID) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE Likes (
likeID INT NOT NULL AUTO_INCREMENT,
fromID INT(10) UNSIGNED NOT NULL,
postID INT NOT NULL,
createdDate DATE,
PRIMARY KEY (likeID),
FOREIGN KEY (fromID) REFERENCES users (id),
FOREIGN KEY (postID) REFERENCES Posts (postID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE Comments (
commentID INT NOT NULL AUTO_INCREMENT,
fromID INT(10) UNSIGNED NOT NULL,
postID INT NOT NULL,
comment TEXT,
createdDate DATE,
PRIMARY KEY (commentID),
FOREIGN KEY (fromID) REFERENCES users (id),
FOREIGN KEY (postID) REFERENCES Posts (postID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE Tags (
tagID INT NOT NULL AUTO_INCREMENT,
userID INT(10) UNSIGNED NOT NULL,
postID INT NOT NULL,
PRIMARY KEY (tagID),
FOREIGN KEY (userID) REFERENCES users (id),
FOREIGN KEY (postID) REFERENCES Posts (postID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
My Post Controller, I just have a simple page that spits out everything. I don't want to loop through anything in my view file, I just want to bring back the json full object.
class PostController extends BaseController {
public function show($id)
{
$post = Post::with(array('comments', 'from', 'tags', 'likes'))->find($id);
return View::make('samplepage')->with('data', $post);
}
}
My Post Model:
class Post extends Eloquent {
protected $table = 'Posts';
protected $primaryKey = 'postID';
public function comments()
{
return $this->hasMany('Comment','postID');
}
public function tags()
{
return $this->hasMany('Tag','postID');
}
public function likes()
{
return $this->hasMany('Like','postID');
}
public function from()
{
return $this->belongsTo('User', 'fromID')->select(array('id', 'first_name', 'last_name'));
}
public function users()
{
return $this->belongsTo('User', 'fromID');
}
}
Comment Model: class Comment extends Eloquent {
protected $table = 'Comments';
protected $primaryKey = 'commentID';
public function post() {
return $this->belongsTo('Post','fromID');
}
public function user() {
return $this->belongsTo('User', 'fromID')->select(array('id', 'first_name', 'last_name'));
}
}
Tag Model: class Tag extends Eloquent {
protected $table = 'Tags';
protected $primaryKey = 'tagID';
}
I even setup the following in my user model but it makes no difference. User Model:
public function posts() {
return $this->hasMany('Post','id');
}
public function comments() {
return $this->hasMany('Comment','id');
}
Everything works great with this setup and when I hit posts/2 with this the following code, I get the below object back. $post = Post::with(array('comments', 'from', 'tags', 'likes'))->find($id); return View::make('samplepage')->with('data', $post);
{
postID: "2",
toID: "8",
comments: [
{
commentID: "2",
comment: "second comment",
fromID: "1",
postID: "2",
createdDate: "2014-02-15"
}
],
from: {
id: "4",
first_name: Paul,
last_name: Davis
},
tags: [
{
tagID: "1",
userID: "2",
postID: "2"
},
{
tagID: "2",
userID: "3",
postID: "2"
}
],
likes: [
{
likeID: "1",
fromID: "2",
postID: "2",
createdDate: "2013-01-04"
},
{
likeID: "2",
fromID: "3",
postID: "2",
createdDate: "2013-02-05"
}
]
}
But what I want is the following, where for each tag, like and comment to concatenate the first and last name and get them back with the object.
{
postID: "2",
toID: "4",
comments: [
{
commentID: "2",
comment: "second comment",
fromID: "1",
from: {
"name": "Jason Terry",
"id": "721286625"
},
postID: "2",
createdDate: "2014-02-15"
}
],
from: {
id: "4",
first_name: Paul,
last_name: Davis
},
tags: [
{
tagID: "1",
userID: "2",
from: {
"name": "David Lee",
"id": "721286625"
},
postID: "2"
},
{
tagID: "2",
userID: "3",
from: {
"name": "Paul Pierce",
"id": "721286625"
},
postID: "2"
}
],
likes: [
{
likeID: "1",
fromID: "2",
from: {
"name": "David Lee",
"id": "721286625"
},
postID: "2",
createdDate: "2013-01-04"
},
{
likeID: "2",
fromID: "3",
from: {
"name": "Al Davis",
"id": "721286625"
},
postID: "2",
createdDate: "2013-02-05"
}
]
}
I have searched Stackoverflow, countless Laravel blogs, the official documentation for 2 weeks now and I can't seem to solve this. Any help is wonderfully appreciate.
Update: With Tony's answer below I added
$post = Post::with(array('comments.users', 'from', 'tags.users', 'likes.users'))->find($id);
Then I added
public function users()
{ return $this->belongsTo('User', 'fromID')->select(array('id', 'first_name', 'last_name'));
}
to the comments, tags, and likes model. And the object works great now.
But my debugger shows the following
select `id`, `first_name`, `last_name` from `users` where `users`.`id` in ('1')
select `id`, `first_name`, `last_name` from `users` where `users`.`id` in ('4')
select `id`, `first_name`, `last_name` from `users` where `users`.`id` in ('2', '3')
select `id`, `first_name`, `last_name` from `users` where `users`.`id` in ('2', '3')
In short, it runs 4 queries on my users table. Isn't this redundant? Shouldn't it be doing 1 query to the users table instead of 1 query for the original post user, 1 query for the comments users, 1 query for the tags users, and 1 query for the likes users?
