Writing better query that has whereRaw

I wrote a query like this. I think whereRaw part could have been written far better and eventually be more performant but I couldn't succeed.

DB::table('messages')
            ->select('*')
            ->where('message_type', '=', $message_type)
            ->whereIn('seller_id', $seller_id->pluck('seller_id'))
            ->whereNull('answer_id')
            ->whereRaw('market_place_id IN ( SELECT MAX(market_place_id) FROM messages GROUP BY product_id )', [], 'AND')
            ->orderBy('market_place_id', 'DESC')
            ->paginate(10);

jlrdw

8 years ago

Level 75

For simpler things I use querybuilder and or eloquent, but as a query gets more complex I prefer using regular sql with getPdo(). And I do not like that raw thing.

Here just quick example of usage from 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;
    }

But that's just me. But remember eloquent is basically a shortcut where it transforms to normal sql at runtime anyway.

Please or to participate in this conversation.