ram ram - 1 year ago 54
PHP Question

how do i filter queries by records in other tables? - laravel

here is my current issue im trying to figure out

i have a laravel query for example below

$users = User::where('country', $country)

->where('age', '>=' , $lfmin)
->where('age', '<=' , $lfmax)
->get();


return $users;


and this works out all well and good. but I now have another sql table called datingblockedusers. each record in that table has the user id of the blockee and blocker. I also created a static function in datingblockeduser model that goes like this

public static function checkblock($id1, $id2)
{
//check user is blocked
$query = Datingblockeduser::where('uone', $id1)->where('utwo', $id2)->get();
if($query->count() > 0)
{
return true;
}
else
{
$query = Datingblockeduser::where('utwo', $id1)->where('uone', $id2)->get();
if($query->count() > 0)
{
return true;
}
else
{
return false;
}
}
}


How do i filter my main query such that the id of each user in that query is not in the datingblockeduser table under the fields uone or utwo (user one or user two)

EDIT: I wanted to implement a block list kind of related to a friends list. I created a new migration as such

Schema::create('blocked_user', function(Blueprint $table) {
$table->increments('id');
$table->integer('blocked_id')->unsigned()->index();
$table->integer('user_id')->unsigned()->index();
$table->foreign('blocked_id')->references('id')->on('users')->onDelete('cascade');
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});


and in the user model i did this

// blocked
public function blocked()
{
$blocked = $this->belongsToMany('User', 'blocked_user', 'user_id', 'blocked_id');
return $blocked;
}
public function block_user($blocked_id)
{
$this->blocked()->attach($blocked_id); // add friend
$blocked = User::find($blocked_id); // find your friend, and...
$blocked->blocked()->attach($this->id); // add yourself, too
}
public function remove_blocked($blocked_id)
{
$this->blocked()->detach($blocked_id); // remove friend
$blocked = User::find($blocked_id); // find your friend, and...
$blocked->blocked()->detach($this->id); // remove yourself, too
}


now can i now use it as part of the query above or any other query from now on to make sure i only return users who are not on the blocked list?

Answer Source

why don't you query all blocked user_id than change main query using whereNotIn()

// get blocked user id by authenticated user. (change where condition yourself)
$blockedId = Datingblockeduser::where('blocker_id', auth()->id())->pluck('id');

$users = User::where('country', $country)
            ->whereNotIn('id', $blockedId)
            ->where('age', '>=' , $lfmin)
            ->where('age', '<=' , $lfmax)
            ->get();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download