Laravel 4 Query Builder SQL Statement

Question

Good day, I have this SQL statement and I'm having a difficulty on how to convert this in Laravel Query.

I read the docs in http://laravel.com/docs/4.2/queries and I'm confused. Any help would do.

SELECT count(*) FROM `transactions` WHERE `borrower_id` = 2 
            AND `book_id` = 2 AND (
                (
                    `reservedDate` IS NOT NULL 
                    and `borrowedDate` IS NULL
                ) 
                OR (
                    `borrowedDate` IS NOT NULL 
                    AND `returnedDate` IS NULL
                )
            )

pagid · Accepted Answer · 2015-12-03 18:42:02Z

1

So I try to build this query by reading the documentations from Laravel. Still haven't tested it yet but hope it could give you the right direction.

Maybe next time, you should show your own query (it's ok if it's not correct). It's easier to get start from there.

$super_query = DB::table('transactions')->where('borrower_id' , '=' , 2)->where('book_id', '=' , 2)->where( function ( $query ) {
$query->where(function ($query1) {
    $query1->whereNotNull('reservedDate')->whereNull('borrowedDate')
)->orWhere(function ($query2) {
    $query2->whereNotNUll('borrowedDate')->whereNull('returnedDate')
})  
})->count();

edited Dec 3, 2015 at 18:42

pagid

14k14 gold badges84 silver badges107 bronze badges

answered Oct 2, 2014 at 3:35

Toby D

1,4291 gold badge19 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

yowza Over a year ago

Thank you! I'll show next my query, next time...I freaked out.

Toby D Over a year ago

Why downvote? Any idea to improve this answer? Thanks

Toby D Over a year ago

@yowza if you ok with the anser just mark it as correct answer. Thanks

Toby D Over a year ago

@yowza, my answer is the earliest one, and the one that you marked as correct looks exactly the same :)

yowza Over a year ago

oh, I'm sorry about that mate...I change the mark now (:, thank you!

jerald punx · Accepted Answer · 2014-10-02 03:41:14Z

1

    DB::table('transactions')
  ->Where('borrower_id', '=', 2)
  ->Where('book_id', '=', 2)
  ->where(function($query)
  {
    $query->where(function($query1) {
      $query1->whereNotNull('reservedDate')
             ->whereNull('borrowedDate');

    })
    ->orWhere(function($query2) {
      $query2->whereNotNull('borrowedDate')
             ->whereNull('returnedDate');
  });
})->count();

try this.. this will result into

select count(*) from `transactions` where `borrower_id` = 2 and `book_id` = 2 and ((`reservedDate` is not null and `borrowedDate` is null) or (`borrowedDate` is not null and `returnedDate` is null))

answered Oct 2, 2014 at 3:41

jerald punx

358 bronze badges

4 Comments

Jared Eitnier Over a year ago

Man, you beat me by 30 seconds!

jerald punx Over a year ago

@JaredEitnier I'm still new here... just signing up to help... i don't know this is a contest

Jared Eitnier Over a year ago

Oh no, it's not a contest per-se, just a friendly game of who can get the checkmark first :) Welcome.

jerald punx Over a year ago

@JaredEitnier Good game, well played... hahah

Jared Eitnier · Accepted Answer · 2014-10-02 03:46:20Z

1

Try this:

Eloquent

Transactions::where('borrower_id', '=', 2)
    ->where('book_id', '=', 2)
    ->where(function($query)
    {
        $query->whereNotNull('reservedDate')
                ->whereNull('borrowedDate');
    })
    ->orWhere(function($query)
    {
        $query->whereNotNull('borrowedDate')
                ->whereNull('returnedDate');
    })
    ->count();

answered Oct 2, 2014 at 3:46

Jared Eitnier

7,21213 gold badges72 silver badges126 bronze badges

Comments

Taryn · Accepted Answer · 2014-10-05 23:32:33Z

0

book::count()->where('borrower_id','=','2','book_id','=',2)

edited Oct 5, 2014 at 23:32

Taryn

249k57 gold badges374 silver badges409 bronze badges

answered Oct 2, 2014 at 3:30

Kevin Tabada

251 silver badge7 bronze badges

Collectives™ on Stack Overflow

Laravel 4 Query Builder SQL Statement

4 Answers 4

5 Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related