Reusing Database Queries

Writing Reusable Database Queries

You can wrap your query in the DatabaseQuery object. Then, it will be possible to reuse that query in Responders or GraphQL:

<?php namespace App\DatabaseQuery; use App\DatabaseEntity\BlogPost; use Distantmagic\Resonance\DatabaseConnectionPoolRepository; use Distantmagic\Resonance\DatabaseQuery; /** * @template-extends DatabaseQuery<null|BlogPost> */ final readonly class SelectBlogPostBySlug extends DatabaseQuery { const SQL = <<<'SQL' SELECT blog_posts.content, blog_posts.slug, blog_posts.title, FROM blog_posts WHERE blog_posts.slug = :slug LIMIT 1 SQL; public function __construct( DatabaseConnectionPoolRepository $databaseConnectionPoolRepository, private string $blogPostSlug, ) { parent::__construct($databaseConnectionPoolRepository); } /** * @return false|array{ * content: string, * slug: string, * title: string, * } */ public function execute(): false|array { $stmt = $this->getConnection()->prepare(self::SQL); $stmt->bindValue('slug', $this->blogPostSlug); $stmt->execute(); return $stmt->fetchAssociative(); } }

Using the query:

<?php use App\DatabaseQuery\SelectBlogPostBySlug; $selectBlogPostBySlug = new SelectBlogPostBySlug( $databaseConnectionPoolRepository, 'blog-post-slug', ); $blogPost = $selectBlogPostBySlug->execute();

Using Specific Connection Pool

You can choose a specific connection pool to execute the query by using $this->getConnection($connectionPoolName). For example:

<?php namespace App\DatabaseQuery; use Distantmagic\Resonance\DatabaseQuery; use Generator; final readonly class SelectBlogPosts extends DatabaseQuery { const SQL = <<<'SQL' SELECT blog_posts.title FROM blog_posts SQL; public function execute(): false|array { $stmt = $this->getConnection('readonly')->prepare(self::SQL) return $stmt->execute()->fetchAllAssociative(); } }
Edit on GitHub