I have 3 tables : users
, travels
and favorites
.
I'm trying to write a request to retrieve data from db with the following informations : the travel, which user proposed it and how many times the travel has been marked as favorite.
This is my code :
$travels = DB::table('travels')
->join('users', 'users.user_id', '=', 'travels.user_id')
->leftJoin('favorites', 'favorites.travel_id', '=', 'travels.travel_id')
->select('travels.*', 'users.user_firstname', 'users.user_date_of_birth', 'favorites.travel_id', DB::raw("count(favorites.travel_id) as count_favorite"))
->groupBy('favorites.travel_id', 'travels.travel_id')
->get();
It's almost working, except that travel_id
value is now NULL when the travel is not marked as favorite (not present in the favorites table). I'm doing a leftJoin because I want ALL the travels (even those who are not marked as favorite).
What am I missing ?
Thanks a lot