Convert your raw SQL statements to a Laravel Query Builder query

If you have some legacy SQL or some raw test SQL code that you want converted into a Laravel’s Query Builder statement, check out Orator.

Orator can convert raw SQL into a Laravel Query Builder statement.

For example, Orator will convert this SQL query…

SELECT * FROM users;

… into this Query Builder statement:

DB::select(`*`)
	->from(`users`)
	->get();

I decided to test Orator on a much more complex query generated by WooCommerce, a WordPress ecommerce plugin.

Here’s the query I plugged into Orator. It contains a subquery and an OR statement.

SELECT SQL_CALC_FOUND_ROWS posts.ID
FROM posts 
WHERE 1=1 
AND 
( 
	posts.ID NOT IN 
	( 
		SELECT object_id 
		FROM term_relationships 
		WHERE term_taxonomy_id IN (7) 
	) 
)
AND posts.post_type = 'product'
AND
(
	posts.post_status = 'publish'
	OR 
	posts.post_status = 'private'
)
GROUP BY posts.ID
ORDER BY posts.menu_order ASC, posts.post_title ASC
LIMIT 0, 10

Orator handled the subquery and OR statement perfectly:

DB::select(`posts.ID`)
	->addSelect(SQL_CALC_FOUND_ROWS)
	->from(`posts`)
	->where(1, `=`, 1)
	->where(DB::raw(`( posts.ID NOT IN ( SELECT object_id FROM term_relationships WHERE term_taxonomy_id IN (7) ) )` ))
	->where(`posts.post_type`, `=`, `product`)
	->where(DB::raw(`( posts.post_status = 'publish' OR posts.post_status = 'private' )` ))
	->groupBy(`posts.ID`)
	->orderBy(`posts.menu_order`, `ASC`)
	->orderBy(`posts.post_title`, `ASC`)
	->limit(10)
	->get();

The last test I performed contained 2 INNER JOINs. Here’s the query I started with:

SELECT t.*, tt.*, tr.object_id
FROM terms AS t 
	INNER JOIN term_taxonomy AS tt
		ON t.term_id = tt.term_id
	INNER JOIN term_relationships AS tr
		ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format')
AND tr.object_id IN (1)
ORDER BY t.name ASC

And Orator returned the following:

DB::select(`t.*`,`tt.*`,`tr.object_id`)
	->from(`terms as t`)
	->join(`term_taxonomy as tt`, function($join) {
		$join->on(`t.term_id`, `=`, `tt.term_id`);
	})
	->join(`term_relationships as tr`, function($join) {
		$join->on(`tr.term_taxonomy_id`, `=`, `tt.term_taxonomy_id`);
	})
	->whereIn(`tt.taxonomy`, `('category', 'post_tag', 'post_format')`)
	->whereIn(`tr.object_id`, `(1)`)
	->orderBy(`t.name`, `ASC`)
	->get();

Not only does Orator save you a ton of time converting test or raw SQL into Laravel specific queries, it’s also a great learning tool if you are more comfortable with SQL than you are with Laravel’s Query Builder.

Properly display line breaks from a <textarea> using Blade

If you have a <textarea name="content"> field in your form and you use {{ $post->content }} in your Blade template to display the $post->content field, you’ll notice that none of the line breaks are retained when the field is rendered on the page.

For example, text entered into a <textarea> like this:

The plants and flowers
I raised about my hut
I now surrender
To the will
Of the wind

Will be displayed on your page like this:

The plants and flowersI raised about my hutI now surrenderTo the willOf the wind

That does not do the Ryokan poem justice.

To fix our line break issue, replace {{ $post->content }} with {!! nl2br(e($post->content)) !!}

Let’s break that down working from the most inner function out to the wrapping Blade statements.

We first wrap $post->content with the escape function e(). The Laravel e() function runs PHP’s htmlspecialchars function with the double_encode option set to false. Laravel Documentation

Next we have nl2br() which inserts HTML line breaks (ie. <br> tags) before all newlines in a string. PHP Documentation

Lastly, we change our Blade statement from {{ ... }} to {!! ... !!} which disables the escaping of data between the curly braces. But we’ve already escaped the data using the e() function.

Important – If you choose to prevent escaping by using the {!! ... !!} statement, be sure to escape your data before rendering it on the page… or else.

Now your line breaks are retained.