Bitwise operators not working properly for PostgreSQL

[Arzaroth]

• Laravel Version: 8.79.0
• PHP Version: 7.4.27
• Database Driver & Version: MariaDB 10.6.5, PostgreSQL 13.5

Description:

MariaDB supports a bitwise AND operator as follows:

SELECT 'a' WHERE (3 & 1);

PostgreSQL also does, but requires a bool cast like so:

SELECT 'a' WHERE (3 & 1):::bool;

The expression itself produces an integer, so I understand the necessity to cast in raw SQL.
However, given this operator is in the list of available ones, alongside other bitwise operators (e.g. |, ^, &~, etc...) I fail to see how it is supposed to be used.

Steps To Reproduce:

Let's pretend you have a Model named Order, with a status column. Since the status could be a combination of many, we'll use a bitmask in order to "combine" multiple status in one column.

Then, we should be able to query the table like this :

$query = Order::query();
// doing some stuffs
$query->where('status', '&', 1)->get();

This yields a PDOException.

What we currently have to do :

$query = Order::query();
// doing some stuffs
$query->where($query->raw('(status & 1)'), '!=', 0)->get();

This could cause some problems, since the column name is no longer escaped.

[driesvints]

This seems like a feature request? I don't think Laravel is capable of handling these right now.

[Arzaroth]

I would beg to differ.
Laraval boasts compatibility with the following databases :

• MySQL
• PostgreSQL
• SQLite
• SQL Server

Thus, I would expect a level of abstraction so my code would run using any databases previously mentionned (unless raw is involved of course).
JSON expressions are a perfect example of this.

Binary operators already are in the list of availables operators as shown here :

framework/src/Illuminate/Database/Query/Builder.php

Line 197 in d11d431

public $operators = [

They are, in my understanding, solely for WHERE and HAVING purposes.

From what I've gathered, $query->where('field', '&', $value) works on MySQL/MariaDB & SQLite and doesn't on PostgreSQL & SQL Server.
An approach that works everywhere is '(field & $value) != 0'.

By no means I'm trying to implying that such a change would be easy to implement.

I've snooped around and I have a basic idea of what should be done for this to work.
I'll try to submit a PR soonish.

[martin-pfeiffer-dibooq]

@Arzaroth

Concerning:
"An approach that works everywhere is '(field & $value) != 0'."

This is right now not working with Postgresql. Please look at this -> #40757 (comment)

[Arzaroth]

Thank you for the heads up.

I apologize about this and I'll look into it.