Laravel Eloquent - Query with multiples parameters

Question

I'm new to the Laravel framework and I could need a little help from you.

Could someone help me to convert this request into Eloquent ?

SELECT * FROM `non_working_days` WHERE YEAR(date) = "2021" AND (country = "Paris" OR country = "Madrid")

For the moment I have managed to find a solution but I am running as many queries as the number of parameters.

foreach ($calendar as $code) {
  array_push(
    $data,
    Model::query()
      ->whereYear('date', '=', $year)
      ->where('country', $code)
      ->get()
  );
}

So it comes down to doing :

SELECT * FROM `non_working_days` WHERE YEAR(date) = "2021" AND country = "Paris"
SELECT * FROM `non_working_days` WHERE YEAR(date) = "2021" AND country = "Madrid"

So I don't think it's very efficient knowing that potentially I would have many more parameters.

Thanks

Koala Yeung · Accepted Answer · 2021-02-09 10:34:19Z

3

You can use the whereIn method:

$countries = ['Paris', 'Madrid']; // or use $calendar instead if it's an array
$data = Model::query()
    ->whereYear('date', '=', $year)
    ->whereIn('country', $countries)
    ->get();

This should give you a query like this:

SELECT * FROM `non_working_days` WHERE YEAR(`date`) = "2021" AND `country` IN ("Paris", "Madrid");

edited Feb 9, 2021 at 10:34

answered Feb 9, 2021 at 10:14

Koala Yeung

8,0434 gold badges35 silver badges58 bronze badges

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

Comments

GhanuBha · Accepted Answer · 2021-02-09 10:21:24Z

1

You can do it, by

Model::query()
       ->whereYear('date', '=', $year) 
       ->where( function( $whereQry ) use( $country_1, $country_2 ) {
            $whereQry->orWhere( 'country', $country_1 );
            $whereQry->orWhere( 'country', $country_2 );
       })
       ->get();

its convert into exactly what you need,

SELECT * FROM `non_working_days` WHERE YEAR(date) = "2021" AND (country = "Paris" OR country = "Madrid")

answered Feb 9, 2021 at 10:21

GhanuBha

18 bronze badges

2 Comments

isckraa Over a year ago

Your proposal seems correct to me only if I only have two parameters. As soon as I have several parameters the code will be very complicated. I think it is better to use OR key IN ('key1', 'key2'). Thank you for your help.

GhanuBha Over a year ago

whereIn is the best if you want to match multi-value to a single column. But this one will help you where you actually need the OR operation in your SQL query. So this is for your future use.

Mohamed Ahmed · Accepted Answer · 2021-02-09 10:15:44Z

0

you can do this by using where in and you dont need to make loop for this,like this :-

Model::query()
  ->whereYear('date', '=', $year)
  ->whereIn('country', $calendar)
  ->get()

answered Feb 9, 2021 at 10:15

Mohamed Ahmed

8301 gold badge5 silver badges11 bronze badges

1 Comment

Mohamed Ahmed Over a year ago

but $countries array should be like this :- ['Paris', 'Madrid']

Collectives™ on Stack Overflow

Laravel Eloquent - Query with multiples parameters

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related