How optimise Model Qeury with large "where in (?, ?, ? .....)" statement

posted 11 years ago

I have projects that each belongs to a user When using Project::with('user') to get all my projects, the DB query will build large " in statement " as below :

select * from `user` where `user`.`user_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?,  .....

If I use

Project::join('user', 'user_id')

I can't use user model like

$project->user->user_name  // only I can use is like $project->user_name

I had browsed some topics in Database/Eloquents, not sure if there is same topic before. Thanks for help :)))

Last updated 3 years ago.

corbosman

replied 11 years ago

If you want to use relations, the first method is the way to go. I dont know how large the query becomes, but it doesnt have to be a problem. Use something like Clockwork to check the timings on the query. I have some pretty large queries like this in one of my projects, and it's very fast.

SELECT * FROM "attributes" WHERE "attributes"."container_id" in (... about 300 ids .. )

This took 3ms.

Last updated 3 years ago.

jasteralan

replied 11 years ago

Thanks!!

But I'm still curious about if it is possible (in mysql) to build a very very long query (maybe with 1000 or more ids? ).

Last updated 3 years ago.

Moderators