Geek Answers Handbook

Convert SQL query to query-query style

I am trying to understand how I can convert a SQL query to the style of the query builder in laravel.

My SQL query:

$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");

I currently need to convert to a query builder:

  $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 read that I cannot use whereIn in the connection. Now I get the error:

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

I really don't know how I can convert my SQL to a query builder. Hopefully when I see a good working conversion of my query, I can figure out how to do it next time.

+5
source share
2 answers

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 . wouter_blog_posts = wouter_blog_posts . id And id =" (1) ")") ()>)) ('published_at', 'desc') -> skips (0) -> take (10) -> paginated ($ this-> property ('postsPerPage'));

0
source

The following Query Builder code will give you the exact SQL query you have in 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 must be canceled because you cannot have the subquery as the first parameter of the where method, and you can still bind the value of the condition. So this is 1 <= (subquery) , which is equivalent to (subquery) >= 1 . The request created by the above code will look like this:

 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 in the SQL environment to make sure that they work as requested. Then I implement them step by step using 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 Query Builder will generate, allowing me to compare this with my original query so that make sure he is the same.

0
source

More articles:


All Articles