laravel query

so i have this table fsos with these columns

		- id
		- created_by
		- name
		.....

another table named fso_items with these columns

		- id	
		- fso_id
		- service_id
		- serviceable_id	
		- serviceable_type

and another table called com_transfers with these columns

		- id
		- fso_item_id
		- from_type
		- from_id
		- to_type
		- to_id

so i am creating an api call for the fso sumary .

what i have is the fso id and i need to get the the "assets" of that fso the assets are the serviceable that are on fso_items table (do not return the asset if already returned but return the other service done on this asset ) and then on the com_transfers table the serviceable item can be in the "from" column or the "to" column i need to count the number of the opposit side of the serviceable so if the serviceable item is on the commodity transfer table from_type from_id i need to count the to_type to_id but do not count if the same item was used more then one time (note that all relationships on the model level are already done and i did that with php but i need it in laravel query)

iamsagarvaghela

2 years ago

Level 1

Hey, @maska Can we solve this in below way!

// Assuming $fsoId is the ID of the FSO you're interested in
$fsoId = SPECIFIC_ID;

$fsoSummary = Fso::findOrFail($fsoId);

$assets = FsoItem::where('fso_id', $fsoId)
    ->where(function ($query) {
        $query->whereNotExists(function ($subQuery) {
            $subQuery->select('fso_item_id')
                ->from('com_transfers')
                ->whereColumn('com_transfers.fso_item_id', 'fso_items.id')
                ->whereColumn('com_transfers.from_type', 'fso_items.serviceable_type')
                ->whereColumn('com_transfers.from_id', 'fso_items.serviceable_id');
        });
    })
    ->with(['serviceable'])
    ->get();

$transfersCount = ComTransfer::whereIn('fso_item_id', $assets->pluck('id'))
    ->where(function ($query) {
        $query->orWhere(function ($subQuery) {
            $subQuery->whereColumn('com_transfers.from_type', 'fso_items.serviceable_type')
                ->whereColumn('com_transfers.from_id', 'fso_items.serviceable_id');
        })
        ->orWhere(function ($subQuery) {
            $subQuery->whereColumn('com_transfers.to_type', 'fso_items.serviceable_type')
                ->whereColumn('com_transfers.to_id', 'fso_items.serviceable_id');
        });
    })
    ->distinct()
    ->count('id');

// Combine the results into the final summary
$fsoSummary->assets = $assets;
$fsoSummary->transfersCount = $transfersCount;

return response()->json($fsoSummary);

Please let me know if it works. 👍🏻

Please or to participate in this conversation.