CODEWITHSUNDEEP
phpmysqllaraveleloquent
Paul Wright
Paul Wright

Reputation: 455

Laravel Eloquent, return rows where both conditions are True

I have a table of Weeks that is joined to a property table, Weeks table looking like this:-

PropID, WeekDate,    Available

1     , 2015-07-04,      Yes

1     , 2015-07-11,      Yes

1     , 2015-07-18,      No

2     , 2015-07-04,      Yes

2     , 2015-07-11,      No

2     , 2015-07-18,      No

I want to select properties where both the weeks of the 4th and 11th are available. In the example above, I want to return two rows with PropID 1 as both are available and no rows from PropID 2 as only one of the weeks are available.

I've tried various ways, but either get nothing or always return the 1st, 2nd and 4th rows.

I think this is close, but it's still missing something as it is looking for dates that are <= AND >=

 $query = Property::whereHas('Week', function($q) use ($arrive)
            {
                $q->where(function($sub)
                {
                    $sub->where('WeekDate', '>=', '2015-07-04');
                    $sub->where('WeekDate', '<=', '2015-07-11');

                });
                $q->where('Available', '=', 'Yes');
            })
                ->get();

Not sure this helps, but the Property table is simply

PropID, PropName

    1     , Property 1

    2     , Property 2

Just found that this SQL works.

SELECT PropID FROM tblweeks WHERE WeekDate IN ('2015-07-04', '2015-07-11') AND Available = 'yes' GROUP BY PropID HAVING COUNT(*) = 2

Upvotes: 2

Views: 2074

Answers (5)

Ariful Haque
Ariful Haque

Reputation: 3750

This will give your result as Property 1 only:

$weeks = Property::whereHas('Week', function ($q) {
    $q->where(function ($sub) {
        $sub->whereIn('WeekDate', array('2015-07-04', '2015-07-11'));
        $sub->where('Available', '=', 'y');
    });
    $q->groupBy('property_id');
    $q->having('count(*)', '=', '2');
})->get();

Upvotes: 1

whoacowboy
whoacowboy

Reputation: 7447

I believe you do not need the second nested query.

$query = Property::whereHas('Week', function($q) use ($arrive)
{
  $q->where('WeekDate', '>=', '2015-07-04');
  $q->where('WeekDate', '<=', '2015-07-11');
  $q->where('Available', '=', 'Yes');
})
->get();

Updated

Have you looked into whereBetween.

$query = Property::whereHas('Week', function($q) use ($arrive)
{
  $q->whereBetween('WeekDate', '2015-07-04', '2015-07-11');
  $q->where('Available', '=', 'Yes');
})
->get();

Upvotes: 0

lukasgeiter
lukasgeiter

Reputation: 152870

You actually need two whereHas for this:

 $query = Property::whereHas('Week', function($q) use ($arrive)
                  {
                      $q->where('WeekDate', '>=', '2015-07-04');
                      $q->where('Available', '=', 'Yes');
                  })
                  ->whereHas('Week', function($q) use ($arrive)
                  {
                      $q->where('WeekDate', '<=', '2015-07-11');
                      $q->where('Available', '=', 'Yes');
                  })
                  ->get();

Upvotes: 0

Bhavin Solanki
Bhavin Solanki

Reputation: 4818

There are some changes, the query might be using group by query :

Please check this solution

$query = Property::whereHas('Week', function($q) use ($arrive)
{
  $q->where('WeekDate', '=', '2015-07-04');
  $q->orWhere('WeekDate', '=', '2015-07-11');
  $q->where('Available', '=', 'Yes');
  $q->group('WeekDate');
})
->get();

This should work and will return desire output

Upvotes: 0

Bhavin Solanki
Bhavin Solanki

Reputation: 4818

There are some changes i have did in your query :

Please check this solution

$query = Property::whereHas('Week', function($q) use ($arrive)
{
  $q->where('WeekDate', '=', '2015-07-04');
  $q->orWhere('WeekDate', '=', '2015-07-11');
  $q->where('Available', '=', 'Yes');
})
->get();

This should work and will return desire output

Upvotes: 0

Related Questions