How to write SQL Statements With Spaces In Column Names

Question

I am trying to write a dynamic query to get data from columns with spaces in their names.

The Product_Details table is the following.

S.No.	Product line	Product type	Product	Year
1	Camp Equipment	Cooking Gear	Cook Set	2015
2	Golf Equipment	Irons	Titanium	2014

I am using this code, which returns a syntax error or access violation error.

$query = \Drupal::database()->select('Product_Details', 'n');
$query->addField('n', 'S.No.');
$query->condition('n.Product type', 'Irons');
$results = $query->execute()->fetchAll();

While looking into the error log after the SQL Query has been executed, I found out this.

1: SELECT n.S.No. AS SNo FROM {Product_Details} n WHERE n.Producttype = :db_condition_placeholder_0; Array ( [:db_condition_placeholder_0] => Irons )

If you look into it, Product type is changed to Producttype and S.No. is changed to SNo.

How can I write a SQL query when column names has spaces?

EDIT: This has been handled in the issue #2986452. This is committed to branch 9.0.x

Answer 1

Ideally your column names shouldn't have spaces in them, but if you're stuck with that then you'll need to do a manual query, like this:

$q = \Drupal::database()
  ->query("select `S.No.` from `Product_Details` where `Product type` = 'Irons'")
  ->execute();