如何解决thinkphp left join 遇到右边要提前筛选的情况
比如下面的sql
$map_message['tab_sdk_message.game_id']=$user['game_id'];
$map_message['tab_sdk_message.reach_money']=array('lt',$sum_pay_amount);
$sql=M('sdk_message','tab_')->field('tab_sdk_message.id,tab_sdk_message.content,tab_sdk_message.game_id,tab_sdk_message.creat_time,tab_sdk_message.title,tab_message_read.user_id')->join('LEFT JOIN tab_message_read on tab_sdk_message.id=tab_message_read.article_id')->where($map_message)->order('id desc')->select();
但这个时候我们需要把tab_message_read这张先通过条件筛选一遍。那怎么解决呢。
其实我们可以先通过fetchSql打印出原生sql,然后通过正则把"LEFT JOIN tab_message_read"替换,再用query()方法执行。
完整示例:
$map_message['tab_sdk_message.game_id']=$user['game_id'];
$map_message['tab_sdk_message.reach_money']=array('lt',$sum_pay_amount);
$sql=M('sdk_message','tab_')->field('tab_sdk_message.id,tab_sdk_message.content,tab_sdk_message.game_id,tab_sdk_message.creat_time,tab_sdk_message.title,tab_message_read.user_id')->join('LEFT JOIN tab_message_read on tab_sdk_message.id=tab_message_read.article_id')->where($map_message)->order('id desc')->fetchSql(true)->select();
$sql1="left join (select * from tab_message_read where user_id=".$user['user_id'].") as tab_message_read";
$str=preg_replace("/LEFT JOIN tab_message_read/",$sql1,$sql);
$data=M()->query($str);