mardi 2 avril 2019

Laravel query builder: DB::raw with IF statement gives wrong results

I have a query which works in mysql, but when doing it in laravel I get wrong results.

The query retrieves all books, but if a book has "visibility_school" (boolean), then the current user's school must match the book writer's school - so I used an IF statement for that.

This mysql query works: (see the WHERE IF line, where I hard coded a value 2 (schools.id = 2) to compare to for testing, normally it is dynamic)

SELECT book.*, user.name AS name, user.school_class AS schoolClass, schools.name as 
       schoolName, count(DISTINCT book.id) AS overallCount, 
       AVG(book_rating.mark) AS rating 
FROM book
LEFT JOIN user ON book.user_id = user.id
LEFT JOIN book_tag ON book.id = book_tag.book_id
LEFT JOIN book_rating ON book.id = book_rating.book_id
LEFT JOIN schools ON schools.id = user.school_id
WHERE IF(book.visibility_school = 1, IF(schools.id = 2, 1, 0), 1) = 1       
AND book.published = 1                      
AND book.deleted = 0                 
GROUP BY book.id

When doing this in Laravel query builder, my code looks like:

$books = DB::table('book')
   ->select(DB::raw("book.*, user.name AS name, user.school_class AS schoolClass, 
               schools.name as schoolName,count(DISTINCT book.id) AS overallCount, 
               AVG(book_rating.mark) AS rating"))
->leftJoin('user', 'book.user_id', '=', 'user.id') 
->leftJoin('book_tag', 'book.id', '=', 'book_tag.book_id')    
->leftJoin('book_rating', 'book.id', '=', 'book_rating.book_id')    
->leftJoin('schools', 'schools.id', '=', 'user.school_id')  
->where(DB::raw("IF(book.visibility_school = 1, IF(schools.id = '?', 1, 0), 1) = 1"),$currentUserSchoolId)
->where('book.published',Book::STATUS_PUBLISHED)
->where('book.deleted',0) 
->groupBy('book.id');

The mysql query correctly returns 5 published books, and if I set schools.id to 3 an extra book only visible to members of a certain school id is included.

The laravel code however returns no books, with two exceptions:

  1. If I set schools.id to 0, I get 1 result, the one book only visible to members of a certain school (which should be id 3).

  2. If I set schools.id to 1 - I get the 5 published books. If I set any other id's I get no results.

There is no difference if I put $currentUserSchoolId inside an array [$currentUserSchoolId]. Also the Book::STATUS_PUBLISHED property equals 1, so there's no difference to the mysql code version there.

So it seems that laravel evaluates the "where if" line to false even if visibility_school is not 1. Are there any laravel-specific syntax rules that I may have missed?

Aucun commentaire:

Enregistrer un commentaire