I need the query result where multiple tables are joined. But, for one of those tables, I need to call a function which uses a column value, does some operations and returns the final value. I need to export the query result in a CSV.
Below is a simplified example. How can I use getURL function in a query? Is there any other way to achieve this? (I am using Laravel 7.)
Tables
Activities
| id | title | description |
|---|---|---|
| 1 | running | ABC |
| 2 | dancing | XYZ |
Activity_multimedia
| id | activity_id | type | multimedia_location |
|---|---|---|---|
| 1 | 1 | Vid | abc1.mp4 |
| 2 | 1 | Img | abc2.jpeg |
| 3 | 2 | Img | abc3.jpeg |
| 4 | 2 | Img | abc4.jpeg |
| 5 | 2 | Vid | abc5.mp4 |
My code
public function query() {
return Activity::query()
->selectRaw(
'activities.id',
'activities.title',
'multimedia.multimedia_locations'
)
->leftJoin(
DB::raw('(SELECT activity_multimedia.activity_id, GROUP_CONCAT(activity_multimedia.type SEPARATOR \', \') as type, GROUP_CONCAT('. DB::raw($this->GetURL('activity_multimedia.type', 'activity_multimedia.multimedia_location')).' SEPARATOR \', \') as multimedia_locations, FROM activity_multimedia GROUP BY activity_multimedia.activity_id) as multimedia'), 'multimedia.activity_id', "=", "activities.id"
)
->groupBy('activities.id', 'multimedia.multimedia_locations')
->distinct();
}
public function GetURL($fileType = null, $fileName = null) {
$URL = 'https://xyz.blob.core.windows.net/';
$type = '';
if ($fileType === 'Img') {
$type = 'Image';
} elseif ($fileType === 'Vid') {
$type = 'Video';
}
return $URL.'/'.$type.$fileName;
}
