Laravel leftJoin 只加入右表的最后一条记录 [英] Laravel leftJoin only last record of right table

问题描述

我是 Laravel 的新手.

I am new to laravel.

我有两张桌子.1) 产品2) 价格

I have two tables. 1) products 2) prices

-----------------------------
- products                  -
-----------------------------
- id_product | int (p_key)  -
- name       | varchar      -
-----------------------------

-------------------------
- prices                -
-----------------------------
- id_price   | int (p_key)  -
- id_product | int          -
- price      | int          -
-----------------------------

products 表保存有关产品的数据，例如 id、name、...价格变化存储在 prices 表中，其中最后一条记录是应显示给用户的最新价格.

the products table holds data about products like id, name,... the price changes are stored in prices table where the last record is the newest price that should be displayed to users.

现在我想搜索 products 并从 prices 表中获取每个产品的最后价格.这是我的查询:

now I want to search through products and get the last price of each product from prices table. this is my query:

$result = DB::table('products')->leftJoin('prices', function($join) {
    $join->on('products.id_product', '=', 'prices.id_product');
})->whereRaw(MY_SEARCH_FILTERS);

上面的代码是错误的，因为如果一个产品在prices表中有4条记录，那么它在$result中会重复4次，但只有1条记录与应显示最后价格.

the above code is wrong because if a product has 4 records in prices table, then it will be repeated 4 times in $result, but only 1 record with the last price should be displayed.

推荐答案

这里我们有 2 个表 users 和 answers，其中 users 剩下table 和 answers 是包含用户答案的​​正确表格.

Here we have 2 tables users and answers where users is left table and answers is right table which has user answers.

我们想让用户加入答案，但加入应该是最新的记录或答案表.

We wanted to left join users with answers but the join should be with the latest record or answers table.

$query = Users::select('users.id', 'users.user_name','answers.created_at as last_activity_date')
->leftJoin('answers', function($query) 
{
   $query->on('users.id','=','answers.user_id')
   ->whereRaw('answers.id IN (select MAX(a2.id) from answers as a2 join users as u2 on u2.id = a2.user_id group by u2.id)');
})
->where('users.role_type_id', Users::STUDENT_ROLE_TYPE)->get();

这篇关于Laravel leftJoin 只加入右表的最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！