Query SQL returns data that was not to be returned

Question

Well, I’m putting together a ticket sales system for football matches as a project of my course I’m working with the Laravel + Eloquent ORM, but I’m having an inconsistency.

My code is:

$matches = Match::select('ut_matches.id', 'ut_matches.id_championship', 'ut_matches.id_stadium','ut_matches.id_club_main','ut_matches.id_club_visitor','ut_matches.id_match_round', 'ut_matches.date_match', 'ut_matches.schedule_match', 'ut_lots.id AS id_lot','ut_lots.tickets_sell', 'ut_lots.price_full', 'ut_lots.id_stadium_sector','ut_stadiums_sectors.name AS sector_name', 'ut_stadiums.name', 'ut_stadiums.id_club', 'ut_stadiums.id_state', 'ut_stadiums.id_city')
 ->join('ut_lots', 'ut_lots.id_match', 'ut_matches.id')
 ->join('ut_stadiums_sectors', 'ut_lots.id_stadium_sector', 'ut_stadiums_sectors.id')
 ->join('ut_stadiums', 'ut_stadiums_sectors.id_stadium', 'ut_stadiums.id')
 ->where('ut_lots.id_lot_status', 2)
 ->where('ut_matches.date_match', '>', date('Y-m-d', strtotime(now())))
 ->where('ut_matches.id_club_main', Auth::user()->id_club)
 ->orWhere('ut_matches.id_club_visitor', Auth::user()->id_club)
 ->orderBy('ut_matches.date_match', 'DESC')
 ->groupBy('ut_matches.id')
 ->limit(6)
 ->get();

All this code begets me:

 select `ut_matches`.`id`, `ut_matches`.`id_championship`, `ut_matches`.`id_stadium`, `ut_matches`.`id_club_main`, `ut_matches`.`id_club_visitor`, `ut_matches`.`id_match_round`, `ut_matches`.`date_match`, `ut_matches`.`schedule_match`, `ut_lots`.`id` as `id_lot`, `ut_lots`.`tickets_sell`, `ut_lots`.`price_full`, `ut_lots`.`id_stadium_sector`, `ut_stadiums_sectors`.`name` as `sector_name`, `ut_stadiums`.`name`, `ut_stadiums`.`id_club`, `ut_stadiums`.`id_state`, `ut_stadiums`.`id_city` from `ut_matches` inner join `ut_lots` on `ut_lots`.`id_match` = `ut_matches`.`id` inner join `ut_stadiums_sectors` on `ut_lots`.`id_stadium_sector` = `ut_stadiums_sectors`.`id` inner join `ut_stadiums` on `ut_stadiums_sectors`.`id_stadium` = `ut_stadiums`.`id` where `ut_matches`.`date_match` > '2019-07-24' and `ut_lots`.`id_lot_status` = 2 and `ut_matches`.`id_club_main` = 1 or `ut_matches`.`id_club_visitor` = 1 and `ut_matches`.`deleted_at` is null group by `ut_matches`.`id` order by `ut_matches`.`date_match` desc limit 6

Only the problem is that it generates an inconsistency on account of two Where, however I need to have them.

 ->where('ut_matches.id_club_main', Auth::user()->id_club)
 ->orWhere('ut_matches.id_club_visitor', Auth::user()->id_club)

That is, I need to verify which is the team of the registered user, but also need to check if the date of the game did not exceed the current date:

 ->where('ut_matches.date_match', '>', date('Y-m-d', strtotime(now())))

If I remove my two wheres to fetch the user club it works the Where of the date, but I need to be 3 clauses. How can I select to not generate this inconsistent?

Answer 1

The inconsistency in the result is due to the fact that the dates are being compared as strings. Then the date 2019-07-24 is minor that 2019-06-30 following this model of comparison.

To compare dates in type DATE in Laravel there are several possibilities.

Using the Query Builder function itself, whereDate:

->whereDate('ut_matches.date_match', '>', date('Y-m-d'))

Concatenate the team with the date:

->where('ut_matches.date_match', '>', date('Y-m-d') . ' 00:00:00')

Use the functions DATE() mysql:

->whereRaw("DATE(ut_matches.date_match) > '" . date('Y-m-d') . "'")

To use the function DATE() or other Mysql functions, it is necessary to insert them as strings and for that there are "raw Expressions"

---

To know the large amount of functions for the clause where, see in itself documentation of Laravel.