SoFunction
Updated on 2025-04-04

laravel implements different queries based on different values ​​of the field

We often encounter this situation during development:

For example, an information table message, field type 1. Operation reminder 2. Platform notification, table message_read record user browsing status when the information is platform notification

Then when the information is a platform notification, it is targeted at all users. We want to display his unread messages in the message reminder based on whether he browses.

The statement is as follows (laravel)

public function index()
 {
// Listen to SQL statements//  DB::listen(function($query) {
//   $bindings = $query->bindings;
//   $sql = $query->sql;
//   foreach ($bindings as $replace){
//    $value = is_numeric($replace) ? $replace : "'".$replace."'";
//    $sql = preg_replace('/\?/', $value, $sql, 1);
//   }
//   dd($sql);
//  });
   $uid = 13; 
   return MessageModel::where(function($query) use($uid){
    $query->where(['type'=>2,'status'=>1,])->whereNotIn('id',function($query) use($uid){
     $query->select('mid')->from('message_read')->where([['','=',DB::raw('mid')],'uid'=>$uid]);
    });
   })->orwhere(function($query) use($uid){
    $query->where(['type'=>1,'status'=>1,'is_read'=>2,'uid'=>$uid]);
   })->get();
 }

Data table format

CREATE TABLE `message` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `uid` int(11) DEFAULT NULL COMMENT 'User ID that needs to be notified',
 `title` varchar(255) NOT NULL COMMENT 'title',
 `describe` varchar(255) DEFAULT NULL COMMENT 'Introduction',
 `type` tinyint(4) DEFAULT NULL COMMENT 'Notification Type 1.Behavior Notification 2.Platform Notification',
 `is_read` tinyint(4) DEFAULT NULL COMMENT 'Whether it has been read 1.read 2.unread',
 `status` tinyint(4) DEFAULT '1' COMMENT '1 exists 2 deletes',
 `created_at` int(11) DEFAULT NULL,
 `updated_at` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='Message Table';


CREATE TABLE `message_read` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `uid` int(11) DEFAULT NULL COMMENT 'User ID',
 `mid` int(11) DEFAULT NULL COMMENT 'Message id',
 `created_at` int(11) DEFAULT NULL,
 `updated_at` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='Platform message notification reading record table';

The above laravel implementation of different queries based on different fields is all the content I have shared with you. I hope you can give you a reference and I hope you can support me more.