Joining connected tables using with() function in eloquent is really useful and easy, but this will give you all fields from these tables which in many cases we do not need. So, how do we select only specific columns from tables when using with() function in eloquent?

 

To use with() command Laravel models need to be already connected (one to many, many to many...), if they are not please check that first.

Let's take a look to example where we want to query published posts with author (id, name, slug), author details (photo) and tags (id, name) but we want to get only specific fields from connected tables and to just get number of comments (but not comments data)

                    $post = Post::where('published','=', 1)->with([
                'author' => function($query){
                    $query->with(['authordetail'=>function($detail){
                        // selecting fields from authordetail table
                        $detail->select('authors_details.author_id','authors_details.photo'); 
                    }]);
                    // selecting fields from author table
                    $query->select('authors.id','authors.name','authors.slug'); 
                },
                'tags'=>function($tags){
                    // selecting fields from  tags table
                    $tags->select('tags.id','tags.tags'); 
                }
        ])
    ->orderBy('id','DESC')
    // selecting fields from post table
    ->select('post.slug','post.title','post.views','post.likes','post.dislikes','post.created_at','post.id') 
    // get only number of comments
    ->withCount(['comments'])  
    // take 20 records in pagination style
    ->paginate(20);