Question mark operator in query

Question

In my laravel 5 app, I'm using PostgreSQL's jsonb data type and it has ? operator.

But I can't get it work in my model, because laravel uses question marks as bindings.

Specifically, in whereRaw() method:

$query->whereRaw("jsonb_column ? 'a_key'")

How can I use question mark in my queries?

Answer 1

you can consider using the function call instead of operator.

First you should find out which function ? operator uses via following query on your PostgresSQL database:

SELECT oprname, oprcode FROM pg_operator WHERE oprname = '?'

on my development database it's jsonb_exists function, then you can update your query as:

$query->whereRaw("jsonb_exists(jsonb_column, 'a_key')")

I hope it helps, happy coding.

Answer 2

Basically you have 2 options:

1. Getting your hand dirty by extending the current way Laravel's Query Builder implement whereRaw, otherwise stated doing it the hard way.
2. Send feature request to the Laravel team (ie asking them to extend all Query Builder component dealing with more PostgresQL specifics) and cross your fingers they will address it if you are patient enough.

---

Here are my takes for the [1.] option:

The namespace "Illuminate\Database\Query" is good for you:

You need particularly to delve into the following Laravel 5.0 source codes of interest:

• Builder.php
• Grammar.php
• PostgresGrammar.php

---

Code fragments of interest:

whereRaw in Builder.php (excerpt):

/**
 * Add a raw where clause to the query.
 *
 * @param  string  $sql
 * @param  array   $bindings
 * @param  string  $boolean
 * @return $this
 */
public function whereRaw($sql, array $bindings = array(), $boolean = 'and')
{
    $type = 'raw';
    $this->wheres[] = compact('type', 'sql', 'boolean');
    $this->addBinding($bindings, 'where');
    return $this;
}

compileWheres in Grammar.php (excerpt):

/**
 * Compile the "where" portions of the query.
 *
 * @param  \Illuminate\Database\Query\Builder  $query
 * @return string
 */
protected function compileWheres(Builder $query)
{
    $sql = array();
    if (is_null($query->wheres)) return '';
    // Each type of where clauses has its own compiler function which is responsible
    // for actually creating the where clauses SQL. This helps keep the code nice
    // and maintainable since each clause has a very small method that it uses.
    foreach ($query->wheres as $where)
    {
        $method = "where{$where['type']}";
        $sql[] = $where['boolean'].' '.$this->$method($query, $where);
    }
    // If we actually have some where clauses, we will strip off the first boolean
    // operator, which is added by the query builders for convenience so we can
    // avoid checking for the first clauses in each of the compilers methods.
    if (count($sql) > 0)
    {
        $sql = implode(' ', $sql);
        return 'where '.$this->removeLeadingBoolean($sql);
    }
    return '';
}

$operators array in PostgresGrammar.php (excerpt):

/**
 * All of the available clause operators.
 *
 * @var array
 */
protected $operators = array(
    '=', '<', '>', '<=', '>=', '<>', '!=',
    'like', 'not like', 'between', 'ilike',
    '&', '|', '#', '<<', '>>',
);

notice that ? is not a valid operator ;-)

Specialized PostgreSQL protected methods in PostgresGrammar.php (excerpt):

/**
 * Compile the additional where clauses for updates with joins.
 *
 * @param  \Illuminate\Database\Query\Builder  $query
 * @return string
 */
protected function compileUpdateWheres(Builder $query)
{
    $baseWhere = $this->compileWheres($query);
    if ( ! isset($query->joins)) return $baseWhere;
    // Once we compile the join constraints, we will either use them as the where
    // clause or append them to the existing base where clauses. If we need to
    // strip the leading boolean we will do so when using as the only where.
    $joinWhere = $this->compileUpdateJoinWheres($query);
    if (trim($baseWhere) == '')
    {
        return 'where '.$this->removeLeadingBoolean($joinWhere);
    }
    return $baseWhere.' '.$joinWhere;
}
/**
 * Compile the "join" clauses for an update.
 *
 * @param  \Illuminate\Database\Query\Builder  $query
 * @return string
 */
protected function compileUpdateJoinWheres(Builder $query)
{
    $joinWheres = array();
    // Here we will just loop through all of the join constraints and compile them
    // all out then implode them. This should give us "where" like syntax after
    // everything has been built and then we will join it to the real wheres.
    foreach ($query->joins as $join)
    {
        foreach ($join->clauses as $clause)
        {
            $joinWheres[] = $this->compileJoinConstraint($clause);
        }
    }
    return implode(' ', $joinWheres);
}

consider these as a sort of specialization of the compileWheres mentioned above, the remaining cases apart from the two (only 2!!!) specific ones are compiled using the parent class method (Illuminate\Database\Query\Grammars\Grammar).

---

Other recommended relevant resources

You may find valuable posts in this blog (SOFTonSOFA).

I particularly recommend:

• Laravel Query Builder global scope – how to use custom Connection and Query Builder in Laravel 4, not in right version but shows how to extend the Query Builder.
• Laravel 5 Eloquent Global Scope how-to, in Laravel 5.0 but orthogonal to your question yet instructive (right version, plumbing details).

Last but not the least, the Laravel documentation is the best place to grab more about its Architecture Foundations (Service Providers, Service Container, Facades and so on). Mastering it is handy to devise a robust extension of the framework.

---

Hopefully my input gives you enough hints for the possible extension point of the Laravel Query Builder offered and may it serve as a good starting point for you to write the PostgreSQL extension addressing the ?operator issue in whereRaw.

Please give back/share when done.