将指定一对多表SQL查询语句转换为Laravel Query Builder表达式

Hey there! Let's break down how to translate your query logic for the a16s and a16s_like tables into Laravel's Query Builder syntax. First, a quick recap of your schema to make sure we're on the same page:

• a16s: Main table with id (primary key) and pic (4 records total)
• a16s_like: Child table linked via p_id (foreign key to a16s.id), with fields id, u_id (user identifier), and approve (flag for approved likes) (11 records total)

Since you mentioned the original SQL was incomplete, I'll cover a few common one-to-many query scenarios you're likely working with—all while preserving the core logic, conditions, and aggregation you'd have in raw SQL.

Scenario 1: Get all a16s with total number of likes #

If your raw SQL looked like this:

SELECT a16s.*, COUNT(a16s_like.id) AS total_likes
FROM a16s
LEFT JOIN a16s_like ON a16s.id = a16s_like.p_id
GROUP BY a16s.id

Here's the equivalent Laravel Query Builder code:

$results = DB::table('a16s')
    ->leftJoin('a16s_like', 'a16s.id', '=', 'a16s_like.p_id')
    ->select('a16s.*', DB::raw('COUNT(a16s_like.id) AS total_likes'))
    ->groupBy('a16s.id')
    ->get();

Scenario 2: Get a16s with approved likes count + check if a specific user liked it #

If your query included conditional aggregation (like counting only approved likes) and a user-specific check, your raw SQL might be:

SELECT a16s.*,
       COUNT(CASE WHEN a16s_like.approve = 1 THEN 1 END) AS approved_likes,
       MAX(CASE WHEN a16s_like.u_id = ? THEN 1 ELSE 0 END) AS user_liked
FROM a16s
LEFT JOIN a16s_like ON a16s.id = a16s_like.p_id
GROUP BY a16s.id

The Query Builder version keeps all that logic intact, plus uses parameter binding for safety:

$userId = auth()->id(); // Replace with your target user ID

$results = DB::table('a16s')
    ->leftJoin('a16s_like', 'a16s.id', '=', 'a16s_like.p_id')
    ->select(
        'a16s.*',
        DB::raw('COUNT(CASE WHEN a16s_like.approve = 1 THEN 1 END) AS approved_likes'),
        DB::raw('MAX(CASE WHEN a16s_like.u_id = ? THEN 1 ELSE 0 END) AS user_liked')
    )
    ->setBindings([$userId])
    ->groupBy('a16s.id')
    ->get();

Scenario 3: Filter a16s that have at least 3 likes #

If you were using a HAVING clause to filter aggregated results in SQL:

SELECT a16s.*, COUNT(a16s_like.id) AS total_likes
FROM a16s
LEFT JOIN a16s_like ON a16s.id = a16s_like.p_id
GROUP BY a16s.id
HAVING total_likes >= 3

Here's how to write that in Query Builder:

$results = DB::table('a16s')
    ->leftJoin('a16s_like', 'a16s.id', '=', 'a16s_like.p_id')
    ->select('a16s.*', DB::raw('COUNT(a16s_like.id) AS total_likes'))
    ->groupBy('a16s.id')
    ->having('total_likes', '>=', 3)
    ->get();

Bonus: Using Eloquent Relationships (Recommended) #

If you're using Laravel's Eloquent ORM, defining the relationship first makes things cleaner. In your A16s model:

public function likes()
{
    return $this->hasMany(A16sLike::class, 'p_id');
}

Then you can use withCount to get aggregated data without writing raw SQL:

// Get all a16s with total likes and approved likes count
$results = A16s::withCount([
    'likes',
    'likes as approved_likes' => function ($query) {
        $query->where('approve', 1);
    }
])->get();

Content of the question comes from stack exchange, asked by robspin