Select query with prefix on all column elements

user_8466673 0
Viewed 68

Is it possible to create a database query in which I add a prefix to every single column of a table. Because the tables unfortunately have "too many" columns I would like to avoid assigning an alias to every single column.

Goal: select table1.*, table2 Will output: table1_id, table_1_name, table2_id, table_2_name, ...

Is this possible?

That is my approach:

$data = DB::connection('mysql_wp')->table('wp_posts as p')
  ->selectRaw('p.*, m.*')
  ->join('wpao_metas as m', 'm.post_id', '=', 'p.ID')
  ->get();
        
dd($data);

But i didnt work. I got columnnames without prefixe.

laravel mysql
1 Answers

You could use a helper function to alias the corresponding columns.

public function getColumnAliases(string $table, string $prefix = null): array
{

    $prefix = $prefix ?? $table . "_";

    return array_map(function ($column) use ($table, $prefix) {

        return $table . "." . $column . " AS " . $prefix . $column;

    }, \Illuminate\Support\Facades\DB::getSchemaBuilder()->getColumnListing($table));
}

Then in your select(...) query:

Instead of ❌:

->selectRaw('p.*, m.*')

Use this ✅:

->select(
    array_merge(
        $this->getColumnAliases("wp_posts"),
        $this->getColumnAliases("wpao_metas")
    )
)

Notes:

You may pass a custom prefix as the second argument of the getColumnAliases(...) method.

user_73765900
Related