Laravel eloquent and relationship

Question

I have a code:

$response = $this->posts
    ->where('author_id', '=', 1)
    ->with(array('postComments' => function($query) {
        $query->where('comment_type', '=', 1);
    }))
    ->orderBy('created_at', 'DESC')
    ->limit($itemno)
    ->get();

And when I logged this query with:

$queries = \DB::getQueryLog();
$last_query = end($queries);
\Log::info($last_query);

In log file I see follow:

"select * from `post_comments` where `post_comments`.`post_id` in (?, ?, ?, ?) and `comment_type` <> ?"

Why is the question mark for comment_type in the query?

Update #1:

I replaced current code with following and I get what I want. But I'm not sure it is OK. Maybe exists many better, nicer solution.

$response = $this->posts
    ->where('author_id', '=', 1)
    ->join('post_comments', 'post_comments.post_id', '=', 'posts.id')
    ->where('comment_type', '=', 1)
    ->orderBy('created_at', 'DESC')
    ->limit($itemno)
    ->get();

I added '=' 1, but in log file I see = ?. I do not know why? :( — Kolesar, Jun 02 '14 at 22:58
Where did you put this function and how are you calling the function ? — The Alpha, Jun 03 '14 at 04:49
Sorry, I don't understand you. If you ask from which class or something like that, answer is: from Controller — Kolesar, Jun 03 '14 at 05:02

score 1 · Accepted Answer · edited May 23 '17 at 11:57

Behind the scene the PDO is being used and it's the way that PDO does as a prepared query, for example check this:

$title = 'Laravel%';
$author = 'John%';
$sql = "SELECT * FROM books WHERE title like ? AND author like ? ";
$q = $conn->prepare($sql);
$q->execute(array($title,$author));

In the run time during the execution of the query by execute() the ? marks will be replaced with value passed execute(array(...)). Laravel/Eloquent uses PDO and it's normal behavior in PDO (PHP Data Objects). There is another way that used in PDO, which is named parameter/placeholder like :totle is used instead of ?. Read more about it in the given link, it's another topic. Also check this answer.

Update: On the run time the ? marks will be replaced with value you supplied, so ? will be replaced with 1. Also this query is the relational query, the second part after the first query has done loading the ids from the posts table. To see all the query logs, try this instead:

$queries = \DB::getQueryLog();
dd($queries);

You may check the last two queries to debug the queries for the following call:

$response = $this->posts
->where('author_id', '=', 1)
->with(array('postComments' => function($query) {
    $query->where('comment_type', '=', 1);
}))
->orderBy('created_at', 'DESC')
->limit($itemno)
->get();

Update after clarification:

You may use something like this if you have setup relation in your Posts model:

// $this->posts->with(...) is similar to Posts::with(...)
// if you are calling it directly without repository class
$this->posts->with(array('comments' =. function($q) {
    $q->where('comment_type', 1);
}))
->orderBy('created_at', 'DESC')->limit($itemno)->get();

To make it working you need to declare the relationship in your Posts (Try to use singular name Post if possible) model:

public function comments()
{
    return $this->hasmany('Comment');
}

Your Comment model should be like this:

class Comment extends Eloquent {
    protected $table = 'post_comments';
}

Yea, but I get all records from database without comment_type restriction. As you can see. I added = 1, but I get all comment types from database. post_id is OK, but comment_type as it is ignored. I'm confused. — Kolesar, Jun 02 '14 at 23:08
Can you instead ask what do you want to get from database and what you are getting? Show models and relationship for that. — The Alpha, Jun 02 '14 at 23:10
If I replaced [[ with(array('postComments' => function($query) { ]] with [[ whereHas('usersMessagesMapped', function($query) { ]] I get results as I want. but without columns from comments table :) — Kolesar, Jun 03 '14 at 04:27

Laravel eloquent and relationship

1 Answers1

Update after clarification: