Asked  7 Months ago    Answers:  5   Viewed   23 times

I'm using the below code to pull some results from the database with Laravel 5.

BookingDates::where('email', Input::get('email'))->orWhere('name', 'like', Input::get('name'))->get()

However, the orWhereLike doesn't seem to be matching any results. What does that code produce in terms of MySQL statements?

I'm trying to achieve something like the following:

select * from booking_dates where email='my@email.com' or name like '%John%'

 Answers

23

If you want to see what is run in the database use dd(DB::getQueryLog()) to see what queries were run.

Try this

BookingDates::where('email', Input::get('email'))
    ->orWhere('name', 'like', '%' . Input::get('name') . '%')->get();
Wednesday, March 31, 2021
 
ariel
answered 7 Months ago
100

SOLVED

After weeks of playing with codes finally I came to the right results for myself (in my case it works this way for others maybe works with other suggested answers)

public function advancedsearch(Request $request) {
    $options = Option::all();
    $brands = Brand::all();
    $brandss = Input::has('brands') ? Input::get('brands') : [];
    $suboption = Input::has('suboptions') ? (int)Input::get('suboptions') : [];
    $min_price = Input::has('min_price') ? (int)Input::get('min_price') : null;
    $max_price = Input::has('max_price') ? (int)Input::get('max_price') : null;

    //codes
    if(count($request['suboptions'])){
      $products = DB::table('products')
      ->join('product_suboption', function ($join) {
        $suboption = Input::has('suboptions') ? Input::get('suboptions') : [];
            $join->on('products.id', '=', 'product_suboption.product_id')
                 ->where('product_suboption.suboption_id', '=', $suboption);
        })
      ->paginate(12);
    }

    elseif(count($request['brands'])){
      $products = DB::table('products')
      ->whereIn('products.brand_id', $brandss)
      ->paginate(12);
    }

    elseif(count($request['min_price']) && count($request['max_price'])){
      $products = DB::table('products')
      ->whereBetween('price', [$min_price, $max_price])
      ->paginate(12);
    }


    return view('front.advancesearch', compact('products', 'brands', 'options'));
    }

NOTE: most of my pricing issues solved with (int) as you see in my codes (int)Input::get('min_price') and (int)Input::get('max_price').

Special thanks to Ravindra Bhanderi for his count($request[''] suggestion.

Saturday, May 29, 2021
 
StampyCode
answered 5 Months ago
70

Answer to Question 1:
You can use advance where clause when using eloquent orm like in your case, use can try:

Foo::whereHas('fooTypes', function($query){
    $query->whereTypeId(5);  
})->get();  

Answer to Question 2:

Data Mapper or Query builder is faster than ORM Active Record. So when you are writing very large applications with significant number of concurrent requests then Data Mapper / Query Builder is the way to go.

On the other hand ORM provides a much cleaner syntax and better code readability for someone who just started with Laravel or is writing small or medium size app where the number of concurrent requests will not be significantly large.

Query Builder syntax is nearer to the plain SQL queries, which anyone who has worked with databases and sql finds easy to relate to.
So its a choice you have to make depending your comfort with the Query builder syntax as well as the size of app you are writing.

Laravel Eloquent vs Fluent query builder

Saturday, May 29, 2021
 
MannfromReno
answered 5 Months ago
25

You can achieve what you need by reverting the constraints on last_day and first_day - this way there is no need to use NOT clause.

Instead of doing

WHERE NOT(`last_day` <= '2001-06-01' OR `first_day` >= '2022-12-01');

you can do

WHERE `last_day` > '2001-06-01' AND `first_day` < '2022-12-01';

And with Eloquent builder the following should do the trick:

$query = $query->where('last_day', '>', $first_day)->where('first_day', '>', $last_day);
Saturday, May 29, 2021
 
avon_verma
answered 5 Months ago
19

It's works the same way as FS_amount, just add the new columns to your code that generates the dynamic columns:

  SELECT GROUP_CONCAT(DISTINCT CONCAT(
      'MAX(IF(month = ''',
      month,
      ''' and year(date) = ',
      year(date),
      ', FS_amount, NULL)) AS `',
      CONCAT('FA_',month),
      '_',
      year(date),
      '`, ', 
      'MAX(IF(month = ''',
      month,
      ''' and year(date) = ',
      year(date),
      ', AS_amount, NULL)) AS `',
      CONCAT('AS_',month),
      '_',
      year(date),
      '`'      
      )
    order by date
  ) INTO @sql
  FROM tmp_results;

You should have a look at the code the statements create (e.g. by temporary adding a select @sql;), although it should be pretty straight forward to add even more columns in case you'll need them.

Friday, August 20, 2021
 
Kirill Shlenskiy
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :