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:
-
If I set
schools.idto0, I get 1 result, the one book only visible to members of a certain school (which should be id 3). -
If I set
schools.idto1- 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