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.
['https://Stackoverflow.com/questions/32537116', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3618513/']
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'));
> 
> 
>
The following Query Builder code will give you the exact SQL query you have within your `DB::select`:

```
DB::table('wouter_blog_posts')
    ->whereNotNull('published')
    ->where('published', 1)
    ->whereNotNull('published_at')
    ->whereRaw('`published_at` < NOW()')
    ->where(DB::raw('1'), '<=', function ($query) use ($tagid) {
        $query->from('wouter_blog_tags')
            ->select('count(*)')
            ->join('wouter_blog_posts_tags', 'wouter_blog_tags.id', '=', 'wouter_blog_posts_tags.tags_id')
            ->whereRaw('`wouter_blog_posts_tags`.`post_id` = `wouter_blog_posts`.`id`')
            ->whereIn('id', [$tagid[0]->id]);
    })
    ->orderBy('published_at', 'desc')
    ->skip(0)
    ->take(10)
    ->get();

```

The subquery condition had to be reversed because you can't have a subquery as the first parameter of the `where` method and still be able to bind the condition value. So it's `1 <= (subquery)` which is equivalent to `(subquery) >= 1`. The query generated by the above code will look like this:

```sql
SELECT * 
FROM `wouter_blog_posts` 
WHERE `published` IS NOT NULL 
       AND `published` = 1 
       AND `published_at` IS NOT NULL 
       AND `published_at` < Now() 
       AND 1 <= (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 ( ? )) 
ORDER  BY `published_at` DESC 
LIMIT  10 offset 0 

```

---

My process when creating more complex queries is to first create them and try them out in a SQL environment to make sure they work as indended. Then I implement them step by step with the Query Builder, but instead of using `get()` at the end of the query, I use `toSql()` which will give me a string representation of the query that will be generated by the Query Builder, allowing me to compare that to my original query to make sure it's the same.