Laravel Builder Scope与Union和多对多关系

Question

I have a notifications table (and model)
notifications table columns are thus:

id
title
body
is_public
...

I also have a users table (and model)
users table columns:

id
username
...

I also have a pivot notification_user table
columns:

user_id
notification_id

many-to-many relationship is set on both Notification and User models thus:

Notification.php

public function users()
{
    return $this->belongsToMany('App\Api\V1\Models\User');
}

User.php

public function notifications() 
{
    return $this->belongsToMany('App\Api\V1\Models\Notification');      
}

Now inside Notification.php I want to set a scope. In the scope I need to get public notifications and the current user's private notifications in a single SQL query. from my table structure, public notifications are where is_public == 1. Private notifications are associated on the pivot table.

to achieve this, inside my Notification.php, I also have this setup:

public function scopePublicAndPrivate(Builder $query)
{
    return $this->public($query)->union($this->private($query));
}
public function scopePublic(Builder $query)
{
    return $query->where('is_public', 1);
}
public function scopePrivate(Builder $query)
{
    $user = JWTAuth::parseToken()->authenticate(); //using JWT to get a user.
    return $user->notifications();
}

Now when I try Notification::publicAndPrivate()->get() inside a controller, I get:

Illuminate\Database\QueryException with message 'SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: (select * from `notifications` where `is_public` = 1) union (select * from `notifications` inner join `notification_user` on `notifications`.`id` = `notification_user`.`notification_id` where `notification_user`.`user_id` = 1))

Please I'll appreciate any help with getting this to work or a better solution.

Answer 1

I believe you should change:

return $user->notifications();

to something else, for example:

return $query->where('user_id', $user->id);

or maybe

return $query->whereHas('users', function($q) use ($user) {
   $q->where('id', $user->id);
});   

This is because in one query you are not using any join and in second you do and you are getting different number of columns for union parts.