CODEWITHSUNDEEP
laravelrelational-databaseeloquent
Kolesar
Kolesar

Reputation: 1283

Laravel eloquent and relationship

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();

Upvotes: 0

Views: 196

Answers (1)

The Alpha
The Alpha

Reputation: 146191

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';
}

Upvotes: 1

Related Questions