MySql multiple times Where in Condition if possible

Question

i am Performing the MySQL query with Where In conditions.

Here is my Query.

The query should be:

SELECT * FROM users WHERE id IN (44,44,33,44,33,0);

Query showing my correct result, no problem at all, but what i want to do is Can we Divide all the id with the individual conditions ?

Or can a query has multiple Where In for a single column? like

SELECT * FROM users WHERE id IN (44) AND id IN (45) AND id IN (46);

like this.

Is that possible ? ?

My query code for performing the query, its in Laravel.

$films = Film::with('genre')->with('languages')->with('likes')->with('comments')->with('likedBy');

        if(Input::get('sort')){
            $sort = Input::get('sort');
            switch ($sort){
                case 'old_new':
                    $films = $films->orderBy('created_at', 'asc');
                    break;
                case 'new_old':
                    $films = $films->orderBy('created_at', 'desc');
                    break;
                case 'views':
//                    $films = DB::table('films')
//                        ->leftJoin('film_views', 'films.id', '=', 'film_views.film_id')
//                        ->select(DB::raw('films.*, count(film_views.film_id) as views'))
//                        ->groupBy('films.id')
//                        ->orderBy('views' , 'desc')
//                        ;

                    $films = $films
                        ->leftJoin('film_views', 'films.id', '=', 'film_views.film_id')
                        ->select(DB::raw('films.*, count(film_views.film_id) as views'))
                        //->whereBetween('created_at', [$this->first_day_of_search, $this->final_day_of_search])
                        ->groupBy('films.id')
                        ->orderBy('views' , 'desc')
                    ;

                    break;
                case 'likes':
                    $films = $films
                        ->leftJoin('film_likes', 'films.id', '=', 'film_likes.film_id')
                        ->select(DB::raw('films.*, count(film_likes.film_id) as likes'))
                        ->groupBy('films.id')
                        ->orderBy('likes' , 'desc')
                       ;
                    break;
            }


        }
        if(Input::get('filter')) {
            $jsonFilter = Input::get('filter');
            $filters = json_decode($jsonFilter);

            foreach ($filters as $filter => $value){
                switch ($filter){
                    case "genre":
                        if($value){
                            $films = $films->whereHas('genre', function ($query) use($value) {
                                $query->whereIn('genre_id', $value);
                            });
                        }
                        break;
                    case "cert":
                       if($value){
                           $films = $films->whereIn('cert', $value);
                       }
                        break;
                    case "country":
                        if($value){
                            $films = $films->whereIn('country', $value);
                        }
                        break;
                    case "lang":
                       if($value){
                           $films = $films->whereHas('languages', function ($query) use($value) {
                               $query->whereIn('language_id', $value);
                           });
                       }
                        break;
                }
            }


        }



        $films = $films->paginate(5);

        return parent::api_response($films->toArray(), true, ['return' => 'all films'], 200);

you can have a custom `order by`. You can create a trailing column that has a value based on id ranges (like segments) and use that column for the custom. — Drew, Jun 28 '16 at 04:38
@Drew , actually i have to put all the Ids in foreach and then making a query , dont want comma separated values for where In condition, — Punit Gajjar, Jun 28 '16 at 04:39
no, I referred to the `order by` as custom ... http://stackoverflow.com/q/9378613 — Drew, Jun 28 '16 at 04:40
In that case you have to use OR =>SELECT * FROM users WHERE ID IN(1) OR ID IN (2); — Mahesh Madushanka, Jun 28 '16 at 04:44
Why in the earth you want multiple `IN` clause for the same field??? Makes no sense, completely unnecessary (unless you passed the limit clause elements configured, which given your sample it is not the case). — Jorge Campos, Jun 28 '16 at 04:54
@JorgeCampos i know it does not make any sense but i am finding any alternate solution , because my all the conditions are in foreach loop — Punit Gajjar, Jun 28 '16 at 04:56
Show your `foreach` code and you will see in no time a much better version of what you think you should do as an answer. — Jorge Campos, Jun 28 '16 at 04:57
So just convert each case into an array and pass it to the filter like this: http://stackoverflow.com/a/22221454/460557 — Jorge Campos, Jun 28 '16 at 05:15
What about it? You have to convert each of your cases statements into arrays, so you would have an array of genre, one of cert, one of country and so on, then you would need just an if statement to see if those arrays aren't empty to add the each filter. — Jorge Campos, Jun 28 '16 at 05:20
At this point `foreach ($filters as $filter => $value)` you already have the arrays you just need to use it — Jorge Campos, Jun 28 '16 at 05:21
I'm going to sleep now. Hope some one show you how to do it. It is late at my end. Wish you good luck. — Jorge Campos, Jun 28 '16 at 05:22

Tim Biegeleisen · Accepted Answer · 2016-06-28T04:58:37.823

2

I will post an answer if for no other reason than amusement. I believe that a WHERE IN clause is internally converted into a series of equality onditions separated by OR. So your original query

SELECT * FROM users WHERE id IN (44,44,33,44,33,0)

would be internally converted to this

SELECT * FROM users WHERE id = 44 OR id = 44 OR id = 33 OR id = 44 OR
                          id = 33 OR id = 0

So having a single number for your IN clause would be equivalent to a single equality condition.

By the way, you have the same numbers appearing multiple times, which doesn't make any sense.

edited Jun 28 '16 at 04:58

answered Jun 28 '16 at 04:57

Tim Biegeleisen

502,043
27
286
360

Any other way except this. ? – Punit Gajjar Jun 28 '16 at 04:58

score 0 · Answer 2 · edited Mar 31 '17 at 09:42

0

Yes, it is possible to have multiple WHERE IN.

SELECT * FROM users WHERE id IN (44) OR id IN (45) OR id IN (46);

This is correct.

However I don't understand why you want to do this because this is low in performance compared to having one IN

edited Mar 31 '17 at 09:42

Punit Gajjar

4,937
7
35
70

answered Jun 28 '16 at 04:45

Malinga

505
3
14

2

This is about as ridiculous as it gets. – Drew Jun 28 '16 at 04:45
Sorry didnt get you – Malinga Jun 28 '16 at 04:50
I really should remove my downvote. The problem is the question, not this so much. But it is still ridiculous. – Drew Jun 28 '16 at 04:50
Let's say the `in` clause was 92 items. What might it look like? And why would put the db engine thru that? – Drew Jun 28 '16 at 04:52
@Drew: As i saw he asked "can a query has multiple Where In for a single column" I said yes, thats all – Malinga Jun 28 '16 at 04:53
Yeah, but you are supposed to be the `mysql` guru here. And you need to decipher question gibberish and steer it in the right direction. Why would anyone code like that, when it clogs the parser down? Why not just tack a hundred `and 1=1` onto the end for good effect. – Drew Jun 28 '16 at 04:54
@drew: Ya this is pointless. I know. But he might have some sets of ids where it comes from separate queries or something, that need to be added in separate IN s – Malinga Jun 28 '16 at 04:54
That would be even worse. – Jorge Campos Jun 28 '16 at 04:56
Downvote removed. No fault of yours the thing is so unclear. – Drew Jun 28 '16 at 04:59

MySql multiple times Where in Condition if possible

2 Answers2