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

Questions Components & MVC
1

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

why does this work:

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

but this not work:

$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:

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:

$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.

2

Hello and welcome to our forums! :smiley:

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.

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

3

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:

    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;
    }
4

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.