5

I'm using MySQL and have a table of 9 million rows and would like to quickly check if a record (id) exists or not.

Based on some research it seems the fastest way is the following sql:

SELECT EXISTS(SELECT 1 FROM table1 WHERE id = 100)

Source: Best way to test if a row exists in a MySQL table

How can I write this using Laravel's query builder?

Community
  • 1
  • 1
Anthony Vipond
  • 1,879
  • 2
  • 19
  • 21

3 Answers3

7

Use selectOne method of the Connection class:

$resultObj = DB::selectOne('select exists(select 1 from your_table where id=some_id) as `exists`');

$resultObj->exists; //  0 / 1;
Jarek Tkaczyk
  • 78,987
  • 25
  • 159
  • 157
6

see here http://laravel.com/docs/4.2/queries

Scroll down to Exists Statements, you will get what you need

DB::table('users')
->whereExists(function($query)
{
    $query->select(DB::raw(1))
          ->from('table1')
          ->whereRaw("id = '100'");
})
->get();
Mochamad Gufron Efendi
  • 1,046
  • 12
  • 19
  • 2
    No, this example produces query: `select * from users where exists ( select 1 from orders where orders.user_id = users.id )` And the question was about different query: `SELECT EXISTS(SELECT 1 FROM table1 WHERE id = 100)` (Notice difference between `select ... where exists` and `select exists`) – AbstractVoid Jun 08 '16 at 09:30
4

This is an old question that was already answered, but I'll post my opinion - maybe it'll help someone down the road.

As mysql documentation suggests, EXISTS will still execute provided subquery. Using EXISTS is helpful when you need to have it as a part of a bigger query. But if you just want to check from your Laravel app if record exists, Eloquent provides simpler way to do this:

DB::table('table_name')->where('field_name', 'value')->exists();

this will execute query like

select count(*) as aggregate from `table_name` where `field_name` = 'value' limit 1
// this is kinda the same as your subquery for EXISTS

and will evaluate the result and return a true/false depending if record exists.

For me this way is also cleaner then the accepted answer, because it's not using raw queries.

Update

In laravel 5 the same statement will now execute

select exists(select * from `table_name` where `field_name` = 'value')

Which is exactly, what was asked for.

Community
  • 1
  • 1
AbstractVoid
  • 3,583
  • 2
  • 39
  • 39
  • 1
    No, this query is not the same. Your query that is produced by the exists() method, it scans the whole table for matching records and THEN counts them (which number is a single record with one INT field) and THEN limits the number of this single record by 1 (which is useless). By contrast, the SELECT EXIST() query stops at the first record it finds so it is much faster. – Csongor Halmai Jan 10 '18 at 01:19
  • 1
    @CsongorHalmai I don't know if they really used `count(*)` for `->exists()` in laravel 4 (that would be really poor), but in laravel 5 a `select exists(..)` query is executed. – Paul Spiegel Mar 19 '18 at 13:30