fetchAll Database query returns an error

Question

I'm building a REST Resource in drupal 8 php that returns the result of a query to the database. However using the fetchAssoc() only returns one result. So I figured that fetchAll() returns all results, but it throws an error when I do so. This is the code

$result = $database
      ->select('node__field_recipe_category', 'recipe_cat')
      ->fields('recipe_cat', ['entity_id','bundle'])
      ->condition('entity_id', [3,8], 'BETWEEN')
      ->execute()
      ->fetchAll();
     // Configure caching settings.
    $build = [
      '#cache' => [
        'max-age' => 0,
      ],
    ];
    
    // Return results.
    return (new ResourceResponse($result, 200))->addCacheableDependency($build);

This is the error

The website encountered an unexpected error. Please try again later.</br></br><em class="placeholder">Symfony\Component\Serializer\Exception\NotNormalizableValueException</em>: Could not normalize object of type &quot;stdClass&quot;, no supporting normalizer found. in <em class="placeholder">Symfony\Component\Serializer\Serializer-&gt;normalize()</em> (line <em class="placeholder">164</em> of <em class="placeholder">vendor/symfony/serializer/Serializer.php</em>). <pre class="backtrace">Symfony\Component\Serializer\Serializer-&gt;normalize(Array, &#039;json&#039;, Array) (Line: 116)
Symfony\Component\Serializer\Serializer-&gt;serialize(Array, &#039;json&#039;, Array) (Line: 176)
Drupal\rest\EventSubscriber\ResourceResponseSubscriber-&gt;Drupal\rest\EventSubscriber\{closure}() (Line: 573)
Drupal\Core\Render\Renderer-&gt;executeInRenderContext(Object, Object) (Line: 177)
Drupal\rest\EventSubscriber\ResourceResponseSubscriber-&gt;renderResponseBody(Object, Object, Object, &#039;json&#039;) (Line: 76)
Drupal\rest\EventSubscriber\ResourceResponseSubscriber-&gt;onResponse(Object, &#039;kernel.response&#039;, Object)
call_user_func(Array, Object, &#039;kernel.response&#039;, Object) (Line: 111)
Drupal\Component\EventDispatcher\ContainerAwareEventDispatcher-&gt;dispatch(&#039;kernel.response&#039;, Object) (Line: 191)
Symfony\Component\HttpKernel\HttpKernel-&gt;filterResponse(Object, Object, 1) (Line: 173)
Symfony\Component\HttpKernel\HttpKernel-&gt;handleRaw(Object, 1) (Line: 68)
Symfony\Component\HttpKernel\HttpKernel-&gt;handle(Object, 1, 1) (Line: 57)
Drupal\Core\StackMiddleware\Session-&gt;handle(Object, 1, 1) (Line: 47)
Drupal\Core\StackMiddleware\KernelPreHandle-&gt;handle(Object, 1, 1) (Line: 106)
Drupal\page_cache\StackMiddleware\PageCache-&gt;pass(Object, 1, 1) (Line: 85)
Drupal\page_cache\StackMiddleware\PageCache-&gt;handle(Object, 1, 1) (Line: 47)
Drupal\Core\StackMiddleware\ReverseProxyMiddleware-&gt;handle(Object, 1, 1) (Line: 52)
Drupal\Core\StackMiddleware\NegotiationMiddleware-&gt;handle(Object, 1, 1) (Line: 23)
Stack\StackedHttpKernel-&gt;handle(Object, 1, 1) (Line: 708)
Drupal\Core\DrupalKernel-&gt;handle(Object) (Line: 19)
</pre>

Any ideas?

Answer 1

fetchAssoc worked for you because of the result is an associative array.

fetchAll did not work because the result is an array of stdClass objects and JSON normalizer did not know how to return the data.

I think you will need fetchAllKeyed(0,0), but better play around a bit.

reference: https://www.drupal.org/docs/8/api/database-api/result-sets

Answer 2

$result->fetchAll() Retrieves all records into an indexed array of stdClass objects. To have it return all results as an associative array pass in a fetch mode constant:

$result->fetchAll(\PDO::FETCH_ASSOC);

PDO::FETCH_ASSOC: Returns an array indexed by column name as returned in your result set.

References: https://www.drupal.org/docs/8/api/database-api/result-sets https://www.php.net/manual/en/pdostatement.fetch.php