Eloquent Multitable query

- 1 answer

Ad

I trying to create a search function in php with Slim and Eloquent 5.1, but i have a problem to get the results from the database. This are my tables

Listing Table
  id |  title | description | price |  size  |etc...
-----|--------|-------------|-------|--------|-------
  1  | list1  | some text   | 500   |   50   |
  2  | list2  | some text   | 700   |   80   |
  3  | list3  | some text   | 350   |  120   |

Listings Option Table
  id |  id_listing | opt_name    | opt_value
-----|-------------|-------------|----------
  1  |      1      | rooms       |    3
  2  |      1      | baths       |    4
  3  |      2      | rooms       |    8
  4  |      3      | baths       |    1
  5  |      3      | rooms       |    6

Listings Images Table
  id |  id_listing | file_name   
-----|-------------|-------------
  1  |      1      | file_1.png       
  2  |      1      | file_2.png       
  1  |      2      | file_3.png      
  2  |      3      | file_4.png     

Now i found a way to get all the results with all they options with

listado = $app->listing->with(['options'],['imgs'])->get();

My classes

//Listing Class

class Listing extends Eloquent{
  protected $table = 'listings';


  public function options(){
    return $this->hasMany('Casas\Listing\ListingOption', 'id_listing', 'id');
  }

  public function imgs(){
    return $this->hasMany('Casas\Listing\ListingImg', 'id_listing');
  }

}

// ListingOption class

class ListingOption extends Eloquent{
  protected $table = 'listings_options';

  public function listings()
  {
    return $this->belongsTo('Casas\Listing\Listing', 'id_listing');
  }

}

// ListingImg Class
class ListingImg extends Eloquent{
  protected $table = 'listings_imgs';

  public function listings()
  {
    return $this->belongsTo('Casas\Listing\Listing', 'id_listing');
  }

}

but i cannot find a way to filter the query the way i want to. I tried many thing.

Example: I want to get all the listings that have price less than 600 and have more than 2 rooms.

So that be listing id 1 and 3

If anyone can help create this query i'll appreciate that.

Ad

Answer

Ad

What you want want to use is whereHas.

When accessing the records for a model, you may wish to limit your results based on the existence of a relationship

$result = $app->listing->with('options', 'imgs')
    ->whereHas('options', function ($query) {
        $query->where('opt_value', '>', 2)
            ->where('opt_name', 'room');
    })
    ->where('price', '<', 600)->get();

So what we are doing here is, eager loading options and imgs tables and fetching only the listings that have at least one option where opt_value is bigger than two and opt_name is room.

You can find more in info about querying relationships in here

Ad
source: stackoverflow.com
Ad