mysql在count和rightjoin上更改为laravel

Question

我有一个关于mysql更改为Laravel 的问题

这是我在mysql 上的代码

SELECT raceseriesmap.rs_id,raceseriesmap.m_id,raceseriesmap.rsm_mo , raceseriesmap.rsm_extra, mapdata.m_name, mapdata.m_photoname,  
(SELECT count(carinraceseries.crs_id)  from carinraceseries WHERE carinraceseries.rs_id = raceseriesmap.rs_id)  as c_count from raceseriesmap 
right join mapdata on mapdata.m_id = raceseriesmap.m_id
where raceseriesmap.m_id =4

这是我的Laravel代码

DB::table('raceseriesmap')
->select('raceseriesmap.rs_id', 'raceseriesmap.m_id', 'raceseriesmap.rsm_mo', 'raceseriesmap.rsm_extra', 'mapdata.m_name', 'mapdata.m_photoname', 'carinraceseries.crs_id as c_count')
->rightJoin('mapdata','mapdata.m_id','=','raceseriesmap.m_id')
->where('raceseriesmap.m_id','=',4)
->get();

但是结果显示像这个

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'carinraceseries.crs_id' in 'field list'
select
`raceseriesmap`.`rs_id`,
`raceseriesmap`.`m_id`,
`raceseriesmap`.`rsm_mo`,
`raceseriesmap`.`rsm_extra`,
`mapdata`.`m_name`,
`mapdata`.`m_photoname`,
`carinraceseries`.`crs_id` as `c_count`
from
`raceseriesmap`
right join `mapdata` on `mapdata`.`m_id` = `raceseriesmap`.`m_id`
where
`raceseriesmap`.`m_id` = 4

Answer 1

SELECT语句中缺少一个子查询，也就是使用DB::raw():的子选择

DB::table('raceseriesmap')
->select(
'raceseriesmap.rs_id',
'raceseriesmap.m_id',
'raceseriesmap.rsm_mo',
'raceseriesmap.rsm_extra',
'mapdata.m_name',
'mapdata.m_photoname',
DB::raw("(SELECT count(carinraceseries.crs_id)  from carinraceseries WHERE carinraceseries.rs_id = raceseriesmap.rs_id)  as c_count")
)
->rightJoin('mapdata','mapdata.m_id','=','raceseriesmap.m_id')
->where('raceseriesmap.m_id','=',4)
->get();