Help Raw Query

If it works you can use getPdo.

how do you mean?

@jlrdw

If it works you can use getPdo.

Please Share some code!

Can you try this:

$results = DB::table('advisor_user')
    ->join('bookings', function ($join) {
        $join->on('advisor_user.user_id', '=', 'bookings.user_id')
             ->where('bookings.status', '=', 1)
             ->where('bookings.progress', '=', 2);
    })
    ->join('locations', 'bookings.location_id', '=', 'locations.id')
    ->join('courses', 'locations.course_id', '=', 'courses.id')
    ->join('users', 'bookings.user_id', '=', 'users.id')
    ->where('advisor_id', 3)
    ->select(
        'users.first_name', 
        'users.last_name', 
        'users.email', 
        'courses.title', 
        'locations.venue_name', 
        'locations.start_date', 
        'locations.end_date', 
        'bookings.status', 
        'bookings.progress'
    )->get();

@ARCANEDEV TOP MAN!!!!!

You're welcome ( ^_^)b

@ARCANEDEV quick question how can i filter the results with where if user has search for like status = 2 and course title = level 2 NVQ

You do the same as the bookings join.

You pass a closure for users and courses, after the joins you add your where statements.

For more details, check the Laravel documentation about the Query Builder.

And don't forget to mark your thread as solved

what if the user has not passed any data will this still find the results?

You can do a check inside the closure:

if (empty($value)) {
    $join->on('join_one.some_id', '=', 'join_two.some_id');
}
else {
    $join->on('join_one.some_id', '=', 'join_two.some_id')
        ->where('join_two.value', '=', $value);
}

OR (i didn't test that) like this:

$join->on('join_one.some_id', '=', 'join_two.some_id');

if ( ! empty($value)) {
    $join->where('join_two.value', '=', $value);
}

Hi @ARCANEDEV thanks for your help so far when i run this code i get FatalErrorException in ReportsController.php line 53: parse error

$title          = $request->get('title');
    $user           = $request->get('user');
    $status         = $request->get('status');
    $progess        = $request->get('progress');
    $start          = $request->get('start');
    $end            = $request->get('end');

    $results = \DB::table('advisor_user')
->join('bookings', function ($join) {
    $join->on('advisor_user.user_id', '=', 'bookings.user_id')
         ->where('bookings.status', '=', 1)
         ->where('bookings.progress', '=', 2);
})
->join('locations', 'bookings.location_id', '=', 'locations.id')
->join('courses', 'locations.course_id', '=', 'courses.id')
->join('users', 'bookings.user_id', '=', 'users.id')


->where('advisor_id', 3)

if($title){
    ->where('bookings.status', 1)
}

->select(
    'users.first_name', 
    'users.last_name', 
    'users.email', 
    'courses.title', 
    'locations.venue_name', 
    'locations.start_date', 
    'locations.end_date', 
    'bookings.status', 
    'bookings.progress'
)->get();

Not rewriting op one but example I did a while back

public function getChecks($offset = "", $rowsperpage = "", $checksearch = "")
    {
        $checksearch = $checksearch . "%";
        $pagingQuery = " LIMIT $offset, $rowsperpage";
        $sql = "SELECT OD.checkid, OD.transdate, OD.transdescribe, OD.widthdraw, OD.deposit, OD.isclr,";
        $sql = $sql . " (SELECT (Sum(IFNULL(deposit, 0)) - Sum(IFNULL(widthdraw, 0))) FROM checks";
        $sql = $sql . " WHERE checkid<=OD.checkid) AS RunningSum";
        $sql = $sql . " FROM checks AS OD" . $pagingQuery;
        $sth = \Illuminate\Support\Facades\DB::connection()->getPdo()->prepare($sql);
        $sth->execute();
        $results = $sth->fetchAll(\PDO::FETCH_ASSOC);
        return $results;
    }

@jlrdw Dude!