Having with parentheses in Laravel Query Builder

Question

I'm migrating my application to Laravel 4, but i'm stuck in a very important query, the Raw query is that:

'select m.* from vn_mensagem m'
            . ' INNER JOIN vn_mensagem_item i ON i.mensagem_id = m.mensagem_id '
            . 'INNER JOIN vn_mensagem_destino d ON d.mensagem_id = m.mensagem_id '
            . 'WHERE d.usuario_id = ' . $user->id . ' AND m.lida = 0 '
            . 'AND i.data > "' . $timestamp . '" '
            . 'group by d.mensagem_id '
            . 'HAVING (count(i.mensagem_item_id) > 1 OR m.usuario_id != ' . $user->id . ')'
            . 'ORDER BY i.data DESC'

I tried to reproduce that query with Laravel Query Builder, and i got this:

$mensagens = DB::table('mensagem')
                    ->select('mensagem.*')
                    ->join('mensagem_item', 'mensagem.mensagem_id', '=', 'mensagem_item.mensagem_id')
                    ->join('mensagem_destino', 'mensagem.mensagem_id', '=', 'mensagem_destino.mensagem_id')
                    ->where('mensagem_destino.usuario_id', $user->id)
                    ->where('lida', 0)
                    ->where('mensagem_item.data', '>', $timestamp)
                    ->groupBy('mensagem_destino.mensagem_id')
                    ->having(function($query) {
                        $query->where(DB::raw('count(mensagem_item.mensagem_item_id)'), '>', 1)
                        ->orWhere('mensagem.usuario_id', '!=', $user->id);
                    })                        
                    ->orderBy('mensagem_item.data', 'desc')->get();

But then i got the following error:

{"error":{"type":"ErrorException","message":"strtolower() expects parameter 1 to be string, object given","file":"\/var\/www\/laravel\/php\/vendor\/laravel\/framework\/src\/Illuminate\/Database\/Grammar.php","line":49}}

How can i make a HAVING condition between parenteses in Laravel Query Builder?

score 6 · Accepted Answer · answered Jan 29 '14 at 13:37

6

having() doesn't accept a callback, you could use havingRaw()

answered Jan 29 '14 at 13:37

cecilozaur

695
5
6

score 2 · Answer 2 · edited May 23 '17 at 10:28

2

Your problem is here:

DB::raw('count(mensagem_item.mensagem_item_id)')

This would return an integer, not a field name string which is what Laravel is expecting.

Look at Illuminate/Database/Grammar.php and you can see how the query is being constructed.

You may want to do a totally RAW query using DB::unprepared. See here: https://stackoverflow.com/a/17876877/146602

edited May 23 '17 at 10:28

Community

1
1

answered Jan 29 '14 at 12:36

phirschybar

8,357
12
50
66

what's the syntax of unprepared method? I can't find it in docummentation, i tried to $mensagens = DB::unprepared('select ...'); But i got the error " Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. " – wiLLiamcastrO Jan 29 '14 at 12:59
See the API docs about this: http://laravel.com/api/master/Illuminate/Database/MySqlConnection.html#method_unprepared – JackPoint Jan 29 '14 at 13:31

Having with parentheses in Laravel Query Builder

2 Answers2