conditional based in one query

i have a table like below,

courses

course_id, course_name, course_code

Now every course has tests and it has table structure like below,

test_id, test_name, course_id, test_schedule_date, description, syllabus,test_type

Also every courses has packages

packages

package_id, course_id, package_name

Now we should map every test to particular package and it has below structure,

test_package_mapping

id, package_id, test_id

Now i am trying to fetch course with total tests and mapped tests

difference between total tests and mapped tests is in total tests i want to fetch all tests present in tests table which matches course_id in mapped tests i should join test_package_mapping to tests table and fetch the tests only if it is exists in test_package_mapping table right now i have a query like below,

$prelimsarr = array('Integrated Prelims', 'Online Prelims');
$prelimsimplode = "'" .implode("','", $prelimsarr  ) . "'";

$coursedetail = DB::table('courses')->join('packages','courses.course_id','=','packages.course_id')
          ->join('test_package_maping','packages.package_id','=','test_package_maping.package_id')
          ->join('tests','test_package_maping.test_id','=','tests.test_id')
          ->where('tests.test_active',1)
          ->where('courses.course_id',$courseid)
          ->selectRaw('count(case when tests.test_type IN ('.$prelimsimplode.') then 1 end) as testscount')
          ->first();

Now i want to fetch non mapped tests too and tried like below,

$prelimsarr = array('Integrated Prelims', 'Online Prelims');
$prelimsimplode = "'" .implode("','", $prelimsarr  ) . "'";

$coursedetail = DB::table('courses')
          ->join('tests','courses.course_id','=','tests.course_id')
          ->where('tests.test_active',1)
          ->where('courses.course_id',$courseid)
          ->selectRaw('count(case when tests.test_type IN ('.$prelimsimplode.') then 1 end) as testscount')
          ->first();

Instead of writing in two queries can i combine to one query ? any solution? Thank you

Deekshith

4 years ago

Level 5

i have queries like below now,

//first query joining with test_package_maping
   	   $coursedetail = Courses::join('packages','courses.course_id','=','packages.course_id')
          ->join('test_package_maping','packages.package_id','=','test_package_maping.package_id')
          ->join('tests','test_package_maping.test_id','=','tests.test_id')
          ->where('tests.test_active',1)
          ->where('courses.course_id',$courseid)
          ->select(
                DB::raw('count(case when tests.test_type IN ('.$prelimsimplode.') then 1 end) as prelimscount'),
                DB::raw('count(case when tests.test_type IN ('.$mainsimplode.') then 1 end) as mainscount'),
                'courses.course_name','courses.course_code'
            )
          ->withCount('ordersdata')
          ->first();

//secondquery without joining with test_package_maping
$testscount = DB::table('courses')
          ->join('tests', 'courses.course_id', '=', 'tests.course')
          ->where('tests.test_active',1)
          ->select('courses.course_name', DB::raw("count(tests.course) as testcount"))
          ->where('courses.course_id',$courseid)
          ->first();

I can get result in two queries instead is it possible to combine second query with first one?

Please or to participate in this conversation.