CODEWITHSUNDEEP
mysqlsqllaraveleloquent
Eldair
Eldair

Reputation: 15

Eloquent query results limited by relation criteria

I have two tables, affiliates and meetings and I want to build Eloquent query which will list all affiliates without those who have appointed meeting at specified date and time. So, the result should contain all affiliates who don't have any meeting and also those with meetings but not on specified date and time.

Users: id, name, city...

Meetings: id, client, date, time, user_id...

Can it be done without joins or raw sql (whereRaw or DB::raw) and how?

Test data users:

array:3 [▼
  0 => array:5 [▼
    "id" => 2
    "name" => "John"
    "city" => "New York"
    "created_at" => "2015-02-19 00:26:43"
    "updated_at" => "2015-02-19 00:34:03"
  ]
  1 => array:5 [▼
    "id" => 3
    "name" => "Elisabeth"
    "city" => "Kansas City"
    "created_at" => "2015-02-19 00:26:43"
    "updated_at" => "2015-02-19 00:26:43"
  ]
  2 => array:5 [▼
    "id" => 4
    "name" => "Teodora"
    "city" => "New York"
    "created_at" => "-0001-11-30 00:00:00"
    "updated_at" => "-0001-11-30 00:00:00"
  ]
]

Test data meetings:

array:3 [▼
  0 => array:8 [▼
    "id" => 1
    "client" => "George P."
    "date" => "2015-05-15"
    "time" => "14:00:00"
    "approved" => 1
    "user_id" => 2
    "created_at" => "2015-02-19 00:26:43"
    "updated_at" => "2015-02-19 00:26:43"
  ]
  1 => array:8 [▼
    "id" => 2
    "client" => "Jack White"
    "date" => "2015-05-15"
    "time" => "12:00:00"
    "approved" => 1
    "user_id" => 2
    "created_at" => "2015-02-19 00:26:43"
    "updated_at" => "2015-02-19 00:26:43"
  ]
  2 => array:8 [▼
    "id" => 3
    "client" => "Philip B."
    "date" => "2015-05-16"
    "time" => "16:00:00"
    "approved" => 1
    "user_id" => 3
    "created_at" => "2015-02-19 00:26:43"
    "updated_at" => "2015-02-19 00:26:43"
  ]
]

Expected result all users who don't have meeting on 2015-05-15 at 12:00

array:2 [▼
  0 => array:5 [▼
    "id" => 3
    "name" => "Elisabeth"
    "city" => "Kansas City"
    "created_at" => "2015-02-19 00:26:43"
    "updated_at" => "2015-02-19 00:26:43"
  ]
  1 => array:5 [▼
    "id" => 4
    "name" => "Teodora"
    "city" => "New York"
    "created_at" => "-0001-11-30 00:00:00"
    "updated_at" => "-0001-11-30 00:00:00"
  ]
]

Any help appreciated.

Upvotes: 0

Views: 116

Answers (1)

lukasgeiter
lukasgeiter

Reputation: 152910

Assuming your user model has a relation meetings you can do this:

$users = User::whereDoesntHave('meetings', function($q){
    $q->where('time', '12:00:00');
})->get();

Upvotes: 1

Related Questions