CODEWITHSUNDEEP
laravel
user9977616
user9977616

Reputation:

Eloquent with multiple where

I want to get the conferences where the status column in conferences table is equal "P" and also that have the end_date < now(). So I have this query:

 $publishedConferences = 
 $user->conferences()->
 where(['status', '=', 'P'], 
 ['end_date','<', now()])->paginate($pageLimit);

But like this is not working it appear:

SQLSTATE[42S22]: Column not found: 1054 Unknown column '0'
in 'where clause' (SQL: select count(*) as aggregate 
from `conferences` where `conferences`.`organizer_id` = 4 
and `conferences`.`organizer_id` is not null
and (`0` = status and `1` = = and `2` = P))

Upvotes: 0

Views: 3935

Answers (5)

Polaris
Polaris

Reputation: 1249

This is cleaner in my opinion. 

$whereData = [
 ['status', 'P'],
 ['end_date', '<', now()]
];

$publishedConferences = 
 $user->conferences()->
 where($whereData)->
paginate($pageLimit);

Upvotes: 0

Cory Fail
Cory Fail

Reputation: 1090

Referencing: https://laravel.com/docs/5.6/queries#where-clauses

You can easily combine multiple where clauses, just by adding multiple where()

        $users = DB::table('users')
                ->where('votes', '>', 100)
                ->where('name', 'John')
                ->get();

if you need an OR operator

         $users = DB::table('users')
                ->where('votes', '>', 100)
                ->orWhere('name', 'John')
                ->get();

You can also do it this way with whereColumn, just note that they are using the and operator:

      $users = DB::table('users')
         ->whereColumn([
                 ['first_name', '=', 'last_name'],
                 ['updated_at', '>', 'created_at']
         ])->get();

Upvotes: 3

patricus
patricus

Reputation: 62378

You were close, but your parameters to where() are a little off.

When passing an array of where clauses, it needs to be an array of arrays. You just need to wrap your parameters in another array:

$publishedConferences = $user->conferences()
    ->where([
        ['status', '=', 'P'],
        ['end_date','<', now()]
    ])
    ->paginate($pageLimit);

This is effectively the same as using a closure and adding the where clauses to the query individually.

Upvotes: 0

ali
ali

Reputation: 862

you can do the following:

  $publishedConferences = $user->conferences()->where('status', 'P')->where('end_date','<',Carbon::now())->paginate($pageLimit);

Upvotes: -1

J. Doe
J. Doe

Reputation: 1742

Try with closure:

$publishedConferences = 
   $user->conferences()->
   where(function ($query) {
     $query->where('status', '=', 'P');
     $query->where('end_date','<', now());
   })
   ->paginate($pageLimit);

Upvotes: 0

Related Questions