Laravel mySQL view return by specific ID-CodePudding

So I am quite new to Laravel, and I have a situation, where I am trying to gather data from a pivot table (contains 2 foreign keys only) in order to retrieve data from other tables.

Before everything, I'd like to note, that word "campaign" is the same as "box". Simply it differs in database and front.

I have multiple boxes, that contains specific gifts.

I have set the URL of the box to be something as such:

Basically, I would need to match my URL's id with campaign_foreignK

Thank you in advance.

CodePudding user response：

First of all, yout need to start to use Laravel Eloquent Models.

But doing by your way (the hardest):

You need to create a route in web or api, something like that:

Route::get('/box/{id}', [BoxController::class, 'view']);

Then you need to put this function on your controller:

function view($id){
 /**
* You can do it by 2 ways:
* 1 - Do a where in the result of DB query (the bad way)
*/

$list = DB::table('campaigns_gifts')
    ->join('gift_items', 'gift_items.id', '=', 'campaigns_gifts.gift_foreignK')
    ->select('gift_items.*')
    ->where($campaign_foreignK = '{id}')
    ->get();
$list = (array)collect($list)->where('abc', 123);

/**
* Or the second way (the best is to use the Eloquent, but using DB the following is the best)
* 1 - Get the relations:
* Is git_items id the key for gift_foreignK ? i'm supposing that is it! so....
*/

$giftsIds = array_values((array)DB::select("select * from campaigns_gifts where campaign_foreignK = $id"));
$giftsIdsString = implode($giftsIds, ',');
$list = (array)DB::select("select * from gift_items where id in ($giftsIdsString)");

return view('DBqueries.boxView', ['list' => $list]);
}