CODEWITHSUNDEEP
laravel
niksrb
niksrb

Reputation: 459

How to write SQL subquery using Laravel Eloquent?

select properties.address, properties.unit_type, rents.date, rents.amount, lease_requests.security_deposit 
    from properties 
    left join rents on rents.property_id = properties.id 
    left join lease_requests on lease_requests.property_id = properties.id
    where (rents.status = 'paid' and rents.date between '2019-01-07' and '2019-02-07') 
    or (lease_requests.security_deposit_status = 'paid' and lease_requests.move_in between '2019-01-07' and '2019-02-07')

How can I rewrite this sql query in Laravel, but not by using raw sql, but with combining ->join, ->where, ->orWhere? I know that I should use function within one of these, but I am not sure within which one would that be. Does all of those functions accept function as second parameter?

EDIT

Here is the code which I have written in order to accomplish this, but it's not retrieving exactly the same data as the sql code I have written up there, because I am not sure how to implement query with brackets using Laravel:

Property::join('rents', 'properties.id', '=', 'rents.property_id')
            ->leftJoin('lease_requests', 'properties.id', '=', 'lease_requests.property_id')
            ->where('properties.address', $property['address'])
            ->whereBetween('move_in', [$from,$to])
            ->whereBetween('date', [$from,$to])
            ->where('rents.status', 'paid')
            ->orWhere('security_deposit_status', 'paid')
            ->get(['properties.address', 'properties.unit_type', 'rents.date', 'rents.amount', 'lease_requests.move_in', 'lease_requests.security_deposit'])->toArray();

Upvotes: 0

Views: 40

Answers (1)

Julien METRAL
Julien METRAL

Reputation: 1974

$status = 'paid'; $dateStart = '2019-01-07'; $dateEnd = '2019-02-07';
DB::table('properties')
->selectRaw('properties.address, properties.unit_type, rents.date, rents.amount, lease_requests.security_deposit')
->leftJoin('rents', 'rents.property_id', '=', 'properties.id')
->leftJoin('lease_requests', 'lease_requests.property_id', '=', 'properties.id')
->where(function ($query) use ($status, $dateStart, $dateEnd)  {
    $query->where('rents.status', $status)->whereBetween('rents.date', [$dateStart, $dateEnd]);
})
->orWhere(function ($query) use ($status, $dateStart, $dateEnd)  {
    $query->where('lease_requests.security_deposit_status', $status)->whereBetween('lease_requests.move_in', [$dateStart, $dateEnd]);
})->get();

you must provide a function to get the parenthesis ;)

EDIT with your code :

Property::leftJoin('rents', 'properties.id', '=', 'rents.property_id')
->leftJoin('lease_requests', 'properties.id', '=', 'lease_requests.property_id')
->where(function ($query) use ($status, $dateStart, $dateEnd)  {
    $query
          ->where('rents.status', $status)
          ->whereBetween('rents.date', [$dateStart, $dateEnd]);
})
->orWhere(function ($query) use ($status, $dateStart, $dateEnd)  {
    $query
          ->where('lease_requests.security_deposit_status', $status)
          ->whereBetween('lease_requests.move_in', [$dateStart, $dateEnd]);
})
->get([
    'properties.address', 'properties.unit_type',
    'rents.date', 'rents.amount', 'lease_requests.move_in', 
    'lease_requests.security_deposit'
])->toArray();

Upvotes: 1

Related Questions