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?
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;
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();
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)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.
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.
count(*) for ->exists() in laravel 4 (that would be really poor), but in laravel 5 a select exists(..) query is executed.