# AdapterInterface::query doesn't have consistent return interface?

**URL:** <https://discourse.laminas.dev/t/adapterinterface-query-doesnt-have-consistent-return-interface/2547>\
**Category:** Components & MVC\
**Tags:** laminas-db\
**Created:** [October 17, 2021, 9:48pm UTC](https://discourse.laminas.dev/t/adapterinterface-query-doesnt-have-consistent-return-interface/2547 "2021-10-17T21:48:17Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Brad\_MacGregor](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/brad_macgregor/32/1149_2.png) [@Brad\_MacGregor](https://discourse.laminas.dev/u/Brad_MacGregor)\
**Post date:** [October 17, 2021, 9:48pm UTC](https://discourse.laminas.dev/t/adapterinterface-query-doesnt-have-consistent-return-interface/2547/1 "2021-10-17T21:48:18Z")

</div>

with `$this->db` containing a `Laminas\Db\Adapter\AdapterInterface` configured as ‘pdo’

why does this work:

```auto
$this->db->query($sqlString, $this->db::QUERY_MODE_EXECUTE)->toArray();

```

but this not work:

```auto
$this->db->query($sqlString, $this->db::QUERY_MODE_PREPARE)->execute(['id' => $id])->toArray();

```

I know the answer on the surface is that `query()` can return either `Driver\StatementInterface` or `ResultSet\ResultSet`, but it doesn’t seem to make sense that the interface should change for consumers of `query()` based on arguments passed to it.

I suppose it wouldn’t be an issue for my use case if there was a way to nest queries using the Sql object:

```auto
new Sql($this->db)->select()

```

If I could nest queries, I could let the Sql object handle my variable binding to prevent injection, but instead, it appears I have to do something like this:

```auto
$sql = Sql($this->db)
$fp = function ($name) use ($db) {return $db->driver->formatParameterName($name);};
$select1 = $sql->select()->from()->where()->join()->columns();
$select2 = $sql->select()->from()->where()->join()->columns();
$select2->combine($select1);
$subQuery = $sql->buildSqlString($select2);
$query = "select * from ($subQuery) a group by a.col1 where a.col1 = {$fp('link_id')}"
return $this->db->query($sqlString, $this->db::QUERY_MODE_PREPARE)->execute(['link_id' => $guestLinkId]);
// which is now a Driver\StatementInterface with no toArray method.

```

Am I missing something here? Is there a better way? How much boiler-plate code should I need for a pretty common (for me) use case. I’ve seen a recommendation to use a hydrator, but I’m not sure I need that for just an associative array passed to JsonResponse. I feel like I’m re-inventing a wheel here.

---

<div class="post-metadata">

**Author:** ![froschdesign](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/froschdesign/32/38_2.png) [@froschdesign](https://discourse.laminas.dev/u/froschdesign)\
**Post date:** [October 20, 2021, 6:53am UTC](https://discourse.laminas.dev/t/adapterinterface-query-doesnt-have-consistent-return-interface/2547/2 "2021-10-20T06:53:47Z")

</div>

Hello and welcome to our forums! 😃

> [@Brad\_MacGregor](#):
>
> …but it doesn’t seem to make sense that the interface should change for consumers of query() based on arguments passed to it.

The `Laminas\Db\Adapter\Adapter::query()` method is defined as a _convenience function_ and not present in any interface of laminas-db like `Laminas\Db\Adapter\AdapterInterface`. Therefore, I would not give so much importance to this method.

> [@Brad\_MacGregor](#):
>
> I suppose it wouldn’t be an issue for my use case if there was a way to nest queries using the Sql object  
> …  
> How much boiler-plate code should I need for a pretty common (for me) use case.

We do not know your use case, so it would be good if you could show the desired result here.

---

<div class="post-metadata">

**Author:** ![Brad\_MacGregor](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/brad_macgregor/32/1149_2.png) [@Brad\_MacGregor](https://discourse.laminas.dev/u/Brad_MacGregor)\
**Post date:** [October 21, 2021, 6:51am UTC](https://discourse.laminas.dev/t/adapterinterface-query-doesnt-have-consistent-return-interface/2547/3 "2021-10-21T06:51:11Z")

</div>

That seems like a fair point about Adapter::query()

I suppose I’m just looking for more advice or recipes for running queries on the database. But at the end of the day, I guess it comes down to preference.

1. I need to be able to do multiple unions and sub-queries.
2. I would like to be able to use a query builder so that I can programmatically make changes to the query based on dynamic filters
3. I would like to be able to do all of that safely (ie variable binding to prevent injection).

I have found that the documentation is a bit confusing and lacks the necessary context to actually get where I need to be. For example, combine() isn’t clear. I once tried to combine a query to two others, and got an error saying you couldn’t combine more than two. It appears that you cannot use sub-queries at all, without resorting to writing SQL yourself again, which would sort of defeat the purpose, though I wonder if I could pass a query to the from() method. it doesn’t appear documented.

Anyways, that is sort of a lot, but I hope that clarifies a little bit more about what I’m after. Here is one of my methods. I don’t hate it, but I don’t love it either:

```auto
    public function get(ServerRequestInterface $request, $id=null): array
    {
        $guestLinkId = $request->getAttribute('link_id');
        $sql = new Sql($this->db);

        $guestBringing = $sql->select();
        $guestBringing->from('event_user')->where(['event_user.link_id' => $guestLinkId])
            ->columns([
                'supplied_by' => 'users.username',
                'available' => new \Laminas\Db\Sql\Expression('1'),
                'game' => 'game.name',
                'game_id' => 'game.id'
            ], false)
            ->join('event', 'event_user.event_id = event.id', [], $guestBringing::JOIN_INNER)
            ->join('event_user_bringing', "event.id = event_user_bringing.event_id", [], $guestBringing::JOIN_INNER)
            ->join('users', "users.id = event_user_bringing.user_id", [], $guestBringing::JOIN_INNER)
            ->join('game', "game.id = event_user_bringing.game_id", [], $guestBringing::JOIN_INNER);

        $hostBringing = $sql->select();
        $hostBringing->from('users')
            ->columns([
                'supplied_by' => 'users.username',
                'available' => new \Laminas\Db\Sql\Expression('1'),
                'game' => 'game.name',
                'game_id' => 'game.id'
            ], false)
            ->join('event_user', 'users.id = event_user.user_id', [], $hostBringing::JOIN_INNER)
            ->join('user_game', 'event_user.user_id = user_game.user_id', [], $hostBringing::JOIN_INNER)
            ->join('game', 'game.id = user_game.game_id', [], $hostBringing::JOIN_INNER)
            ->where(['event_user.is_host' => 1]);
        $userPreference = $sql->select();
        $userPreference->columns(
            [
                'supplied_by' => new \Laminas\Db\Sql\Expression('NULL'),
                'available' => new \Laminas\Db\Sql\Expression('0'),
                'game' => 'game.name',
                'game_id' => 'game.id'
            ], false)
            ->from('event_user')->where(['event_user.link_id' => $guestLinkId])
            ->join('users', 'users.id = event_user.user_id', [], $userPreference::JOIN_INNER)
            ->join('user_game_preference', 'user_game_preference.user_id = users.id', [], $userPreference::JOIN_INNER)
            ->join('game', 'user_game_preference.game_id = game.id', [], $userPreference::JOIN_INNER);

        $guestBringing = $sql->buildSqlString($guestBringing);
        $hostBringing = $sql->buildSqlString($hostBringing);
        $userPreference = $sql->buildSqlString($userPreference);

        $subQueryString = "$guestBringing UNION $hostBringing UNION $userPreference";

        $db = $this->db;
        $fp = function ($name) use ($db) {return $db->driver->formatParameterName($name);};

        $sqlString = "
SELECT b.game_id, b.game, b.supplied_by, ugp.rank, ugp.preference as weight, b.available
FROM (
    SELECT a.game_id,
        ANY_VALUE(`game`) as `game`,
        GROUP_CONCAT(distinct supplied_by) AS supplied_by,
        MAX(available) as available
    FROM ($subQueryString) a
    GROUP BY a.game_id) b
LEFT JOIN user_game_preference ugp ON ugp.game_id = b.game_id
LEFT JOIN event_user eu on ugp.user_id = eu.user_id
WHERE eu.link_id = {$fp('link_id')}
ORDER BY `rank` ASC";
        $results = $this->db->query($sqlString, $this->db::QUERY_MODE_PREPARE)->execute(['link_id' => $guestLinkId]);
        $responseArray = [];
        foreach($results as $result) {
            $responseArray[] = $result;
        }
        return $responseArray;
    }

```

---

<div class="post-metadata">

**Author:** ![froschdesign](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/froschdesign/32/38_2.png) [@froschdesign](https://discourse.laminas.dev/u/froschdesign)\
**Post date:** [October 21, 2021, 8:56am UTC](https://discourse.laminas.dev/t/adapterinterface-query-doesnt-have-consistent-return-interface/2547/4 "2021-10-21T08:56:19Z")

</div>

Unfortunately, I don’t see the advantage of using a query builder here – no matter which library you use.

There is only one dynamic parameter for the where clause. And if you only need a simple array, then laminas-db does not bring any advantages here.

Another option is to create a view in the database for this complex query then the usage is much simpler in PHP.

I wouldn’t get my hands dirty with it, because in this case a query builder always means extra work.