Query foreign key

Hi i have two tables charges and customers(foreign) is it possible to make additional select to for table customers to get name ? as you can see inserted the join(commented) but cant find the way to make second select on the same query, i get error.

the query

 $charges = charge::query()
            ->search($this->search)

         ->select(\DB::raw('charges.*,SUM(taskscharges - payment) as balance'))
  
            // ->join('customers', 'charges.customer_id', '=', 'customers.id')
            ->groupBy('id')
            ->orderBy($this->sortBy, $this->sortDirection)
            ->paginate($this->perPage);

gitwithravish

5 years ago

Level 16

Why do you want two select methods ? You can get the job done within one only ! @chris1989

$calculatedCol = \DB::raw('charges.*,SUM(taskscharges - payment) as balance');
$charges = charge::query()
            ->search($this->search)
         	->select('charges.*', 'customers.name', $calculatedCol)
            ->join('customers', 'charges.customer_id', '=', 'customers.id')
            ->groupBy('id')
            ->orderBy($this->sortBy, $this->sortDirection)
            ->paginate($this->perPage);

Chris1989

5 years ago

Level 1

i tried something like this, after your first post

 $charges = charge::query()
            ->search($this->search)
         ->select(\DB::raw('charges.*,SUM(taskscharges - payment) as balance','customers.name as custname'))

         ->join('customers', 'charges.customer_id', '=', 'customers.id')
            ->groupBy('id')
            ->orderBy($this->sortBy, $this->sortDirection)
            ->paginate($this->perPage);

I tried to sort the custname but i get error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'custname' in 'order clause' (SQL: select charges.*,SUM(taskscharges - payment) as balance from `charges` inner join `customers` on `charges`.`customer_id` = `customers`.`id` where (exists (select * from `users` where `charges`.`user_id` = `users`.`id` and (`tasks` like %% or `name` like %%)) or exists (select * from `customers` where `charges`.`customer_id` = `customers`.`id` and `name` LIKE %%)) group by `id` order by `custname` asc

I want to name it to use for sorting

gitwithravish

5 years ago

Best Answer

Level 16

Ensure that the value of $this->sortBy is customers.name

Chris1989

5 years ago

Level 1

Yes, it was so simple.. Thank you very much

1 like

Please or to participate in this conversation.