CODEWITHSUNDEEP
phplaravellaravel-4
adear11
adear11

Reputation: 945

Building query with Query Builder or Eloquent in Laravel 4

I have a query that has multiple subqueries with parameters that I'm trying to build in a Laravel model.

I'm quite new to Laravel 4, so I'd really like some help with what is the "best" way to do this.

The Query that I need to reproduce is:

Select userId from facultyAvailability 
where 
  :startDate between startDate and endDate
  and :endDate between startDate and endDate
  and userId NOT IN(
    Select 
      userId
    from
      schedulerTrialSchedule
      inner join `review`
        on eventId=lectureId
    where
      trialId = :trialId
      and (
        startDate between :startDate and :endDate
        or endDate between :startDate and :endDate
      )
  )
  AND userId IN (
    SELECT userId 
    from
      faculty2FacultyCategory
    where
      categoryId in(:categoryIdList)
  )

I'm really not sure what methods to chain together to build this. Any help would be greatly appreciated.

Upvotes: 1

Views: 1370

Answers (1)

adear11
adear11

Reputation: 945

Well, after some trial and error, I think I arrived at the proper solution.

I am making use of Eloquent's Query Scope functionality. On the model in question, I've defined a scope as:

public function scopeAvailable($query, $trialId, UnscheduledEvent $event)
{
    $query->whereRaw('? BETWEEN startDate AND endDate', array($event->startDate))
        ->whereRaw('? BETWEEN startDate AND endDate', array($event->endDate))
        ->whereIn(
            'userId', function ($query) use ($trialId, $event) {
                $query->select('userId')
                    ->from('schedulerTrialSchedule')
                    ->join('review', 'eventId', '=', 'lectureId')
                    ->where('trialId','=',$trialId)
                    ->where(
                        function ($query) use ($event) {
                            $query->whereBetween('startDate', array($event->startDate, $event->endDate))
                                ->orWhereBetween('endDate', array($event->startDate, $event->endDate));
                        }
                    );
            }
        ,'and',true);
    return $query;
}

This scope yields a query that looks like:

select * 
from `facultyAvailability` 
where 
  ? BETWEEN startDate AND endDate 
  and ? BETWEEN startDate AND endDate 
  and `userId` not in (
    select `userId` 
    from `schedulerTrialSchedule` 
      inner join `review` on `eventId` = `lectureId` 
    where `trialId` = ? 
      and (`startDate` between ? and ? or `endDate` between ? and ?))

This is exactly what I needed it to be. I'm posting this here just for reference in case anyone else needs to know how to solve this problem.

I have to say that I'm quite impressed with Eloquent/Fluent's ability to cope do this without me having to resort to using a bunch of raw SQL. The only reason I had to use raw SQL at all is because whereBetween doesn't seem to be able to handle using a value instead of a column for the first argument of the between with columns as the second and third arguements i.e '2013-08-09' BETWEEN column1 AND column2

Upvotes: 2

Related Questions