laravel 5.1 eloquent select with prefix joined table

Question

I have this query

$sales = Sales::join('customer', 'customer.id', '=', 'sales.customer_id')
->join('cashier', 'cashier.id', '=', 'sales.cashier_id')
->first();

how can i select each table with prefix so i can call like this :

• $order->customer_name
• $order->cashier_name

with my query above, i only get one name row because each table have same column name name, what i want is to give prefixed in each table i call like customer_, cashier_, sales_

Update

the result what i expect is like this

customer_name
customer_address
customer_phone
cashier_name
cashier_another_column
cashier_another_column2
sales_date
sales_another_column
sales_another_column2

Glaucous

Answer 1

the answer is you cannot do that. i usually only alias the same column name and get the rest with *. so your required to alias all same column name for solve the conflict

Sales::join('customer', 'customer.id', '=', 'sales.customer_id')
->join('cashier', 'cashier.id', '=', 'sales.cashier_id')
->select(['*', 'customer.name as customer_name', 'cashier.name as cashier_name'])
->get();

Donnell

thanks maybe this is the best approach for me right now beside describing all column name one by one – Glaucous

Answer 2

You have to use select and then you can give alias to your column names.

As example.

$sales = Sales::join('customer', 'customer.id', '=', 'sales.customer_id')
->join('cashier', 'cashier.id', '=', 'sales.cashier_id')
->select('customer.name as customer_name','cashier.name as cashier_name')
->first();

and so on you can select as many fields from database as you want and give them aliases.

Jurywoman

yes i know this, but i don't want to select one by one, because i have many column for that query, so like i state above, i want to use *. for example i want to select like this customer.* as customer_ so all my customer column is prefixes with customer_ – Glaucous

Then you can use link Accessors and mutators. so in your Customer model if you want name then codepublic function getNameAttribute($value) { return "customer_".$value; } – Jurywoman

the problem is when i run my query without alias then my similiar column name will replaced each other. i only get one name from cashier, doesn't get name from customer – Glaucous

@Glaucous Use raw queries and Join by Eloquent models and in models use mutators as i mentioned in above column then you will get desired result. Or you can use relations as mentioned here #29165910 – Jurywoman

Answer 3

Use get() method and add alias to column name. You can add toArray() after get() to get result in array.
$sales = Sales::join('customer', 'customer.id', '=', 'sales.customer_id')
    ->join('cashier', 'cashier.id', '=', 'sales.cashier_id')
    ->get(['customer.name as customer_name', 'cashier.name as cashier_name'])
    ->toArray();

Corticosterone

how to make an alias for all column ? i want to alias all column like this customer_ and i have 10 - 20 column per table, so i don't want to add alias one by one – Glaucous