SQL Where Id in (Id1, Id2, ..., Idn)

SQL WHERE ID IN (id1, id2, ..., idn)

Option 1 is the only good solution.

Why?

• Option 2 does the same but you repeat the column name lots of times; additionally the SQL engine doesn't immediately know that you want to check if the value is one of the values in a fixed list. However, a good SQL engine could optimize it to have equal performance like with IN. There's still the readability issue though...

• Option 3 is simply horrible performance-wise. It sends a query every loop and hammers the database with small queries. It also prevents it from using any optimizations for "value is one of those in a given list"

Select $id from ids field containg $id1,$id2,$id3

Bad way to keep your ids but if you really can't change it, you could take advantage of LazyCollections and filter with php.

I'm sure there's a way to do it directly in MySQL (or whatever dbms you're using) but this is what I have.

$id = 3;
Model::cursor()
    ->filter(function ($model) use ($id) {
        return in_array($id, explode(',',  $model->linked_ids));
    })
    // then chain one of these methods
    ->first();    // returns the first match or null
    ->collect();  // returns an Illuminate\Support\Collection of the results after the filtering
    ->all();      // returns an array of Models after the filtering
    ->toArray();  // returns an array and transforms the models to arrays as well.
    ->toJson();   // returns a json string

Take notice that this will still do a SELECT * FROM table without any filtering (unless you chain some where methods before cursor() but it won't load any model into memory (which is usually the bottleneck for big queries in Laravel)

Select N items directly before a given ID

You are very close:

SELECT *
FROM things
WHERE id < 'cc'
ORDER BY id DESC
------------^
LIMIT 3;

You need to sort the items in descending order to get the "biggest" ones before 'cc'.

Also, for three items you want limit 3. I assume the "2" is a typo.

If you then want these in alphabetical order, use a subquery and order again:

SELECT t.*
FROM (SELECT t.*
      FROM things t
      WHERE id < 'cc'
      ORDER BY id DESC
      LIMIT 3
     ) t
ORDER BY id ASC;

select * where id= {multiply values)

SELECT * FROM Table WHERE id IN (79,86,42)

---

Related Topics