John Davidson

Message:

I have a question on mysql changing to Laravel

Here is my code on 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

here is my Laravel code

        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();

but the result showing like this

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

---

My Answer:
>rightJoin('mapdata', 'mapdata.m_id', '=', 'raceseriesmap.m_id')
>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'))
>where('raceseriesmap.m_id', 4)
>get();