Error when use hasMany. I get non related objects

Question

User relationship:

public function events() {
     return $this->hasMany('Events', 'user_id');
}

Event relationship:

public function user() {
     return $this->belongsTo('User');
}

I want to get all events for the current month except today's events, so I use:

$pets= Auth::user()->events()
       ->where(function($query) use($myYear, $myMonth, $myDay) {
           $query->whereYear('start_date', '=', $myYear);
           $query->whereMonth('start_date', '=', $myMonth);
           $query->whereDay('start_date', '!=', $myDay);
       })->orWhere(function($query) use($myYear, $myMonth, $myDay)     {
           $query->whereYear('end_date', '=', $myYear);
           $query->whereMonth('end_date', '=', $myMonth);
           $query->whereDay('end_date', '!=', $myDay);
       })->get();

But this retrieves me all the events of all users. I need to add ->where("user_id", Auth::user()->id) before -get() and I don't know why.

Can someone help me solve this question?

Could you possibly show the relevant parts of the schema too? — Jite, Dec 08 '16 at 15:31
Hi Jite, what do you mean by schema? The relationships of the database? One user has N events, one events belong to 1 user. Its an 1:N relationship. The event has the user id as a field — MartaGom, Dec 09 '16 at 10:02
Do you have a `migration` for the database? The place where you create the database fields and the foreignkey connections between the two tables. — Jite, Dec 09 '16 at 10:20

Niklesh Raut · Answer 1 · 2016-12-09T11:09:55.067

You are not keeping reference of $query to pass in closure, Try like this.

   $query = Auth::user()->events();
   $query->where(function($query) use($myYear, $myMonth, $myDay) {
       $query->whereYear('start_date', '=', $myYear);
       $query->whereMonth('start_date', '=', $myMonth);
       $query->whereDay('start_date', '!=', $myDay);
   });
   $query->orWhere(function($query) use($myYear, $myMonth, $myDay) {
       $query->whereYear('end_date', '=', $myYear);
       $query->whereMonth('end_date', '=', $myMonth);
       $query->whereDay('end_date', '!=', $myDay);
   })
   $pets = $query->get();

score 1 · Answer 2 · edited May 23 '17 at 12:30

How to solve: in mySQL (which I assume you're using if it's Lavavel) simply turn on the "general log". Here's a pretty decent StackOverflow on how to do that: How to enable MySQL Query Log?

This will log all the calls made to MySQL and you'll be able to see the construct.

This next bit is a guess: you'll probably find the query ends up as:

SELECT events FROM events
    WHERE
      userID = :userID 
      AND (not start date)
      OR (not end date)

because ANDs are evaluated with higher priority (http://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html) this is the same as

SELECT events FROM events
    WHERE
      (
         userID = :userID 
         AND
         (not start date)
      )
      OR
      (not end date)

And therefore will include anyone in the second query, not just the user.

But what you need is

SELECT events FROM events
    WHERE
      userID = :userID 
      AND
      (
          (not start date)
              OR
          (not end date)
      )

How you've "fixed" it, but by adding the additional "AND" at the end, you get

SELECT events FROM events
    WHERE
      userID = :userID 
      AND (not start date)
      OR (not end date)
      AND userID = :userID

Which is the same as

SELECT events FROM events
    WHERE
      (
         userID = :userID 
         AND
         (not start date)
      )
      OR
      (
         (not end date)
         AND
         userID = :userID 
      )

which does what you want, in a roundabout way...

score 1 · Accepted Answer · answered Dec 12 '16 at 05:53

The issue is with your or statement. Your query currently looks like this:

where relationship_condition AND start_date_condition OR end_date_condition

In the logical order of operations, the ANDs are performed before the ORs, so this is equivalent to:

where (relationship_condition AND start_date_condition) OR end_date_condition

This means that any records that match your end_date_condition will be returned, whether or not they match the relationship_condition. In order to correct this, you need to properly group your OR condition, so it looks like this:

where relationship_condition AND (start_date_condition OR end_date_condition)

So, your code should look something like:

$pets= Auth::user()->events()
    ->where(function ($query) use ($myYear, $myMonth, $myDay) {
        return $query
            ->where(function ($query) use ($myYear, $myMonth, $myDay) {
                return $query
                    ->whereYear('start_date', '=', $myYear)
                    ->whereMonth('start_date', '=', $myMonth)
                    ->whereDay('start_date', '!=', $myDay);
            })
            ->orWhere(function ($query) use($myYear, $myMonth, $myDay) {
                return $query
                    ->whereYear('end_date', '=', $myYear)
                    ->whereMonth('end_date', '=', $myMonth)
                    ->whereDay('end_date', '!=', $myDay);
            });
    })
    ->get();

Thank you for your Help, sir. I understand and solved my problem :) — MartaGom, Dec 12 '16 at 09:04

Error when use hasMany. I get non related objects

3 Answers3