I am new to laravel.
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 -
-----------------------------
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.
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);
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.
You need to add two things in here,
1) orderBy descending on prices table.
2) first clause in the DB::table function (It will fetch only 1 record, that will be the latest price).
The solution :
$result = DB::table('products')
->leftJoin('prices',function($join)
{
$join->on('products.id_product', '=', 'prices.id_product')
})->whereRaw(MY_SEARCH_FILTERS)
->orderBy('prices.id_price','desc')
->first();
You can also use (Laravel 5.1) :
$result = DB::table('products')
->leftJoin('products.id','=','prices.id_product')
->whereRaw(MY_SEARCH_FILTERS)
->orderBy('prices.id_price','desc')
->first();
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();