Bitwise and condition in a Laravel relation (Eloquent ORM)

- 1 answer

Ad

I am trying to create a specialized relation on a user (Model) to the group model. In the schema I am working with, the groups have a type attribute that is a bitmask, where each bit defines a certain characteristic for a group.

For example, we might have a group:

name: New York
type: 33554436 (1<<25 | 1<<24)

With plain SQL, I can get the groups of interest in with the query:

select g.* from foobar_group g  where (type & 1<<25) != 0

I wish to define this relation in the user model for convenience and what have is:

<?php

class UserModel extends Illuminate\Database\Eloquent\Model
{

    public function visitedCities()
    {
        return $this->belongsToMany(
            GroupModel::class,
            'foobar_group_member',
            'user_id',
            'group_id')
          ->with([ 'type' => function ($belongsToMany) {
              $belongsToMany->where('type', '&', 
                    GroupModel::CITY_TYPE,
                    GroupModel::CITY_TYPE)
          }])
        ;
    }
}

In essence, I am trying to add the where condition from the SQL query above to the join statement (relation). Do you know how to do that? Do I need to extend the Eloquent\Relation class?

Ad

Answer

Ad

You can use whereRawto execute raw where clauses.

Eloquent doesn't support more fancy where clauses than like or = and the standard stuff. I suggest you take a look at the documentation

I your case you probably want to use the something like:

$belongsToMany->whereRaw('(type & 1<<25) != 0')

and add it to your select clause.

If you want to debug your SQL you can use:

DB::enableQueryLog();
//Execute Eloquent select
dd(DB::getQueryLog());

That way you can also see that if you put anything like bitmasks into the middlepart of the where clause it gets replaced with =

Ad
source: stackoverflow.com
Ad