Edit: Updated to hasOne in both classes
I have two tables, Store and Address. One Store has one address and one address is associated with only one store.
In my Store model, I have a hasOne relationship.
public function store_address(): HasOne
{
return $this->hasOne(Address::class, 'id', 'address_id');
}
And in Address model I have a hasOne:
public function store(): HasOne
{
return $this->hasOne(Store::class, 'id', 'store_id');
}
Now I want to join these two tables using Eloquent with() with select * from store but want specific columns from the address table.
Store::where('user_id', $user_id)
->select(\DB::raw('*')
->with(['store_address' => function($query) {
return $query->select(['distance_to_user as distance']);
}])
->orderBy('distance');
However it's not returning the correct distance from the address table. How can I do that?
This is the error message I get:
Column not found: 1054 Unknown column 'distance' in 'order clause' (SQL: select *, from `store` where `store`.`user_id` = 12 and `store`.`deleted_at` is null order by `distance` asc)
I assume the structure of your tables
Addresses:
id | store_id| body | distance_to_user
----------------------------------------
1 | 1 |China | 100
and for Stores:
id | name
--------------
1 | Store_01
You should have Models like:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Address extends Model
{
//
public function store()
{
return $this->belongsTo('App\Store');
}
}
and
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Store extends Model
{
//
public function address()
{
return $this->hasOne('App\Address');
}
}
now in your controller:
public function index()
{
//return Store::with(['address' => function($q){
// $q->select('distance_to_user as distance','store_id');
// }])->get();
return Store::leftJoin('addresses', 'addresses.store_id', '=', 'stores.id')
->select('stores.id','stores.name','distance_to_user as distance')
// OR use ->select('stores.*','addresses.id as address_id','distance_to_user as distance')
->orderBy('distance','Desc')
->get();
}
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments