How to come up with SELECT queries?

Question

I have a query like this:

SELECT DISTINCT field_name FROM table_name 

I tried:

\Drupal::database()->select('table_name', 't');
$query->fields('t', ['field_name']);
$result = $query->execute();

But since Connection::select expects base table this did not work.

How am I supposed to execute it? I used entityQuery previously. I could not find a way to filter out the unique values of only one field using entityQuery.

Note: I went through this example which is basically using Connection::select. But Connection::select requires to provide the base table. In entityQuery (https://api.drupal.org/api/drupal/core!lib!Drupal.php/function/Drupal%3A%3AentityQuery/8.2.x) I did not have to provide the base table data.

Answer 1

The Problem is, that you need to fetch the results from your execution.

Your code should look like this:

$query = \Drupal::database()->select('table_name', 't');
$query->fields('t', ['field_name']);
$query->distinct();
$result = $query->execute()->fetchAll();

That does the same as the query you stated above. Alternatively you can fetch your results with a complete custom query:

$result = Drupal::database()->query('SELECT DISTINCT field_name FROM table_name')->fetchAll();

Notice here that you dont have to execute() your query in this case. Instead you can directly fetch your results. You will still need the execute() for insert/update or similiar queries.