convert SQL query to query builder style

Question

Im trying days to understand how I can convert a SQL query to a query builder style in laravel.

My SQL query is:

$tagid = Db::select("SELECT `id` FROM `wouter_blog_tags` WHERE `slug` = '".$this->param('slug')."'");

$blog = Db::select("SELECT * 
            FROM `wouter_blog_posts` 
            WHERE `published` IS NOT NULL 
            AND `published` = '1'
            AND `published_at` IS NOT NULL 
            AND `published_at` < NOW()
            AND (

            SELECT count( * ) 
            FROM `wouter_blog_tags` 
            INNER JOIN `wouter_blog_posts_tags` ON `wouter_blog_tags`.`id` = `wouter_blog_posts_tags`.`tags_id` 
            WHERE `wouter_blog_posts_tags`.`post_id` = `wouter_blog_posts`.`id` 
            AND `id` 
            IN (
             '".$tagid[0]->id."'
            )) >=1
            ORDER BY `published_at` DESC 
            LIMIT 10 
            OFFSET 0");

Where I now end up to convert to the query builder is:

   $test = Db::table('wouter_blog_posts')
->where('published', '=', 1)
->where('published', '=', 'IS NOT NULL')
->where('published_at', '=', 'IS NOT NULL')
->where('published_at', '<', 'NOW()')
  ->select(Db::raw('count(*) wouter_blog_tags'))
->join('wouter_blog_posts_tags', function($join)
{ 
$join->on('wouter_blog_tags.id', '=', 'wouter_blog_posts_tags.tags_id')
->on('wouter_blog_posts_tags.post_id', '=', 'wouter_blog_posts.id')
->whereIn('id', $tagid[0]->id);
})
->get();

I have read that I can't use whereIn in a join. The error i now get:

Call to undefined method Illuminate\Database\Query\JoinClause::whereIn()

I realy dont know how I can convert my SQL to query builder. I hope when I see a good working conversion of my query I can understand how I have to do it next time.

Answer 1

This work for me:

DB::table('wouter_blog_posts') ->whereNotNull('published') ->where('published', 1) ->whereNotNull('published_at') ->whereRaw('published_at < NOW()') ->whereRaw("(SELECT count(*) FROM wouter_blog_tags INNER JOIN wouter_blog_posts_tags ON wouter_blog_tags.id = wouter_blog_posts_tags.tags_id WHERE wouter_blog_posts_tags.post_id = wouter_blog_posts.id AND id IN ( '".$tagid."' )) >=1") ->orderBy('published_at', 'desc') ->skip(0) ->take(10) ->paginate($this->property('postsPerPage'));