Ravel8 Which SQL query is better?

Fine-tune without permission.Also, start with :query() to make it compatible with IDE and easy to match indentation.

//A: Two consecutive SQL issues at WHERE IN (1, 2, 3, ...)
$article_ids=Favorite::query()
    ->where('user_id', $user_id)
    ->pluck('article_id');
$articles=Article::query()
    ->find($article_ids);

// B: Use JOIN
$articles=Article::query()
    ->leftJoin('favorites', 'favorites.article_id', '=', 'articles.id')
    ->where('favorites.user_id', $user_id)
    ->select('articles.*')
    ->get();

// C: Correlation subqueries using WHERE EXISTS (SELECT...)
$articles=Article::query()
    ->whereHas('favorites', function(Builder$query)use($user_id){
        $query->where('user_id'=>$user_id);
    })
    ->get();

// D: Non-relevant subqueries using WHERE IN (SELECT...)
// // https://github.com/mpyw/eloquent-has-by-non-dependent-subquery
$articles=Article::query()
    ->hasByNonDependentSubquery('favorites', function(Builder$query)use($user_id){
        $query->where('user_id'=>$user_id);
    })
    ->get();

A

It's the simplest code, but when you try to add pageation, you think a little more.Also, pageation is difficult with information only found in the articles table.Therefore, only a limited number of cases are available.

B

This is the best performance code, but it uses a lot of landmines.Please note the following:It's a good idea for non-skilled people to use it.

• Only applicable.HasManyThere is a risk of duplicate records of the same ID appearing for the relationship.There is a limitation that says, "Only one user can like the same article," and there is no problem if you narrow it down to a single user_id.Also, HasOneBelongsTo is perfectly fine.
• ->select('articles.*')Don't forget to give it.Without it, the favorites fields will be mixed into creating a disastrous bug.
Start with
• Article::query().Favorite::query() results in an instance of Favorite.

C

Probably the most orthodox method.It can be used in many situations and is a safe method, but query optimizer is not smart MySQL, and the performance is blatantly degraded.There is no problem with PostgreSQL.

D

This code uses my own library to solve problems with C in MySQL.It's negative from an orthodox point of view, but it's both easy to use and high performance.

• mpyw/eloquent-has-by-non-dependent-subquery: Convert has() and whereHas() constraints to non-dependent subqueries.