r9host's avatar

How do I do this db query with eloquent?

Hi friends,

I have a case where I need to have a special "WHERE" clause that include a some criteria to be ORed then the result be ANDed with other criteria. I'm not able to achieve this by chaining where() and orWhere() methods. The query should look like this:

SELECT bank_transactions.*, journal_entries.bank_transaction_id, journal_entries.description FROM bank_transactions, journal_entries WHERE bank_account_id=x AND journal_entries.bank_transaction_id = bank_transactions.id AND ( bank_transactions.description like '%something%' OR bank_transactions.payee_name like '%something%' OR journal_entries.description like '%something%' );

I'm not interested in joining two tables above as each record in bank_transactions table is related to two records in journal_entries tables (hasMany).

How can I do this in eloquent way?

Thanks in advance.

0 likes
9 replies
r9host's avatar

@Ben Taylor I just tested what is in the doc for logical grouping and I have the following TypeError. Below is my code snippet:

DB::table('bank_transactions') ->where('bank_account_id', 1) ->where(function(Builder $query) {
$query->where('description', 'like', '%something%') ->orWhere('payee_name', 'like', '%something%'); });

It throws the below exception:

TypeError  {closure}(): Argument #1 ($query) must be of type Builder, Illuminate\Database\Query\Builder given

I've also tested "\Builder" instead but the same.

I'm on Laravel 11.x Please help.

tykus's avatar
tykus
11 months ago
Best Answer
Level 104

@r9host you need to typehint the correct Builder class; which you can alias like this

use Illuminate\Database\Query\Builder;

DB::table('bank_transactions') 
  ->where('bank_account_id', 1) 
  ->where(function(Builder $query) {
    $query->where('description', 'like', '%something%')
      ->orWhere('payee_name', 'like', '%something%');
});

Or, you can just omit the typehint, and it'll still work for you

DB::table('bank_transactions') 
  ->where('bank_account_id', 1) 
  ->where(function($query) {
    $query->where('description', 'like', '%something%')
      ->orWhere('payee_name', 'like', '%something%');
});

There is also a very useful whereAny method which might be easier to use here (it will take care of logical grouping automatically:

use Illuminate\Database\Query\Builder;

DB::table('bank_transactions') 
  ->where('bank_account_id', 1) 
  ->whereAny(['description', 'payee_name'], 'like', '%something%')
  ->get();
1 like
r9host's avatar

@tykus Next problem is that I have "undefined variable" error inside the closure as the closure function does not have access to variables defined outside of it. Does this closure accepts any optional data argument that I can use to pass a variable to it?

tykus's avatar

@r9host pass the variable into the Closure scope:

// ...
->where(function($query)  use ($theVariable) {
    // $theVariable is available
});
1 like
r9host's avatar

@jlrdw Sorry! I had written the SQL in readable format. I'm not sure why my code was posted unformatted.

akramghaleb's avatar

Here’s how to translate your raw SQL into a proper Eloquent query with Query Builder syntax:

DB::table('bank_transactions')
    ->join('journal_entries', 'journal_entries.bank_transaction_id', '=', 'bank_transactions.id')
    ->where('bank_account_id', '=', $x)
    ->where(function($query) {
        $query->where('bank_transactions.description', 'like', '%something%')
              ->orWhere('bank_transactions.payee_name', 'like', '%something%')
              ->orWhere('journal_entries.description', 'like', '%something%');
    })
    ->select('bank_transactions.*', 'journal_entries.bank_transaction_id', 'journal_entries.description')
    ->get();
1 like

Please or to participate in this conversation.