Running "exists" queries in Laravel query builder

Question

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?

Hereld

Answer 1

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;

Hargett

Answer 2

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();

Saphena

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) – Prevost

Answer 3

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.

Prevost

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. – Deliladelilah

@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. – Smug