Raw query count problem with where condition...

I am not able to add where condition in count raw query... this is my actual code and i want to trying add where condition in following query :

$rawQuery = DB::raw("count(table_2.fk_id) as countTotal"); $sessionHistory = table_1::select('table_1.id') ->leftJoin('table_2', 'table_1.id', '=', 'table_2.fk_id') ->selectRaw($rawQuery) ->groupBy('table_1.id') ->get();

In this query i want to add DB::raw("count(table_2.fk_id where table_2.status IS NULL) as countTotal"). but it's not working and return syntax error of query output...

jlrdw

8 years ago

Level 75

When and if I have to start messing with raw, I usually just use normal pdo with getPdo().

Not your example, but an example I gave a while back:

$postdata = array(
                'dogid' => $dogid,
                'dogpic' => $dogpic,
                'dogname' => $dogname,
                'sex' => $sex,
                'comments' => $comments,
                'adopted' => $adopted,
                'lastedit' => $lastedit
            );

            $sql = "UPDATE " . PREFIX . "dogs SET dogpic = :dogpic, dogname = :dogname, sex = :sex, comments = :comments, adopted = :adopted, lastedit = :lastedit WHERE dogid = :dogid";
              $stmt = DB::getPdo()->prepare($sql);
              $stmt->execute($postdata);

EDIT: Whoops that example was a nova framework example, but here is a laravel example

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;
    }

Haven't had to use lately (over two years), but in laravel it's DB::connection()->getPdo().

I just don't like all that raw stuff.

Please or to participate in this conversation.