3

I have manually built a large query using the various eloquent functions (i.e. $this->newQuery()->join....), but I can't get it to run.

When I call

echo $query->toSql();

It shows me the query. I can copy and paste it into my command line mysql client, and it runs fine, and returns several rows. But when I call

echo $query->count();

or

echo $query->get()->count();

It's showing 0.

I eventually enabled my mysql general log to see what was happening. I see that as laravel runs, it executes several queries - each has a prepare line in the log, followed by an execute line. But this one doesn't.

It appears that laravel is preparing the statement, but never executing it. Why not?

After much testing, I have identified the line that causes the problem:

$query->having('book_author_author_id', 'NOT IN', DB::raw('('.implode(',',$author_ids).')'));

It appears that queries which contain a 'having' clause are not executed by laravel, instead it pretends to execute them and returns an empty collection. What is going on?

Benubird
  • 18,551
  • 27
  • 90
  • 141

2 Answers2

2

The problems turns out to be that laravel is not correctly resolving DB::raw statements in a having clause. Since it also does not allow arrays to be passed through, this has the practical effect of making it impossible to write a query with having x in (1,2,3). Instead, the solution is to do several separate having clauses. So in this case, like this:

foreach($author_ids as $id) {
    $query->having('book_author_author_id', '!=', $id);
}

Yet another irritating example of Eloquent's restricted functionality.

Benubird
  • 18,551
  • 27
  • 90
  • 141
0

Try this: 

$query->havingRaw(
  "{book_author_author_id} IN ('". implode("','", $author_ids) . "')"
);
sasha13
  • 11
  • 1
  • 3
  • While this code may answer the question, providing additional context regarding _why_ and/or _how_ this code answers the question improves its long-term value. – Justin Howard Mar 03 '16 at 17:43