How to limit records from related tables?

posted 11 years ago

Hi everyone! I'm new to laravel and I need some help with this problem.

I have 2 tables posts and post_channels

posts table's columns are:
id
content
status

post_channels table's columns are:
id
post_id
channel_id
is_main

The relationship is:
posts has many post_channels
(note: posts may have multiple post_channels records but only one of that record has an is_main value of 1)

I have this query using the post model

$posts = Post::where('status', '=', 1)  
->whereHas('postChannels', function($q){  
    $q->where('is_main', '=', 1);  
})  
->take(2)
->get();

This query works but the problem is it will return all the post_channels record of a post. The "$q->where('is_main', '=', 1)" line only checks if that post has a record in the post_channels table that has an is_main value of 1. I only want the query to return the related post_channels record that has an is_main value of 1.

Right now if all the post has 3 records in the post_channels table and i run this line of code

foreach($posts as $post){
    echo 'the id of this post is -> '.$post->id.'<br/>';
    echo 'the total retrieved post_channels of this post is -> '.count($post->postChannels).'<br/><br/>';

    // "postChannels" is the hasMany function in the post model
}

/* result is
    the id of this post is 1
    the total retrieved post_channels of this post is -> 3

    the id of this post is 2
    the total retrieved post_channels of this post is -> 3
*/

the total retrieved post_channels should only be 1, and the record should have the is_main value of 1.

I hope you guys could help me with this. Thanks! :)

Last updated 3 years ago.

hudsonpereira

replied 11 years ago

Hello sir,

Use, before get(), take() and skip() methods. Like so:

http://laravel.io/bin/L4V

Last updated 3 years ago.

sherwinncnadev

replied 11 years ago

@hudsonpereira
Thanks for the reply, your example helped me get the records that I needed from the table. I tweaked your query to:

$posts = Post::with(
    array(
        'postChannels' => function($query){
            $q->where('is_main', '=', 1);
        }
    )
)
->where('status', '=', 1)
->take(2)
->get();

I think my problem was not clear above so I edited it and wanted to know if this is the right approach? Thanks again!

Last updated 3 years ago.

gpluess

replied 11 years ago

Hm, try this:

$posts = Post::with(
    array(
        'postChannels' => function($query){
            $q->where('is_main', '=', 1);
        }
    )
)
->where('status', '=', 1)
->postChannels()
->take(2)
->get();

Last updated 3 years ago.

sherwinncnadev

replied 11 years ago

@gpluess Thanks man! I'll try that to see what difference it'll make because the tweaked query that I posted above gives me the records that I need.

Last updated 3 years ago.

Moderators